Nasty const bug in ASP .NET 5

Recently, Microsoft released some much-anticipated software including Visual Studio 2015 and .NET 4.6. This has not been without hiccups though: the guys at Stack Exchange identified a serious flaw in the new .NET’s RyuJIT compiler, which was promptly fixed by Microsoft.

And if, like me, you happen to be playing around with the prerelease technologies, you’re bound to run into other odd behaviour. Specifically, I ran into an issue where debugging information would stop working in an ASP .NET 5 Web Application. I posted a question on Stack Overflow about it before realising it was caused by the presence of a const.

To reproduce the issue, let’s create a new Web Application. In the template selector, we’ll use one of the ASP .NET 5 Preview Templates:

aspnet5constbug-newproject

Locate the Index() method in HomeController, and add some code involving a const:

        public IActionResult Index()
        {
            const int x = 1;
            ViewData["x"] = x;

            return View();
        }

Put a breakpoint somewhere. Run the application, and you’ll notice two things:

  1. If you hover over the constant, you won’t get any tooltip showing its value.
  2. If you try to get that information from the Immediate Window or watches, you’ll get the following error:
error CS0648: '' is a type not supported by the language

aspnet5constbug-noconstinfo

In the above screenshot you can’t see that my cursor is actually on x and I’m not getting any intellisense, but you can see the message in the Immediate Window.

If we instead go to the About page, though, debugging tooltips work fine:

aspnet5constbug-infoavailableonothermethod

In fact, if you add some code in the Index() method (e.g. before the const is declared), you’ll notice that you can’t see the value of any variables or constants in the whole method. Other methods, however, are unaffected.

Let us now remove the const keyword and make x a variable instead:

aspnet5constbug-variableworks

There you go, it was the const keyword that messed up debugging information for the whole method. Removing it made everything work again.

I have no idea what’s causing this bug, but it’s clearly in ASP .NET 5. Console applications do not have this problem, nor do ASP .NET web applications prior to version 5.

Update 2015.09.30: There seems to be an open issue about this, posted just a few days ago.

Retrieving Table Metadata from SQL Server Catalog Views

Introduction

All database systems that I’ve worked with have some sort of system tables that provide information about the tables, columns, indexes, constraints, etc in a database. SQL Server is no exception; in fact there are different ways of querying its System Catalog. We’re going to look at one of these: Catalog Views, which can be queried easily.

Being able to retrieve this metadata can be very useful in various situations, such as developing tools to work with and visualize data (like SQL Server Management Studio), or automating rote tasks (such as creating Entity classes for each table).

List of Table Names

Querying catalog views is as easy as querying any table. The easiest thing you can do is get a list of table names. Use this query:

select *
from sys.tables;

Here’s the result:

systables

So if all you want is the name of the table, just refine the query to select only that:

select name
from sys.tables;

Retrieving data using ADO .NET

It is easy to run simple queries using the good old ADO .NET technology. Below is a sample you could use to retrieve the table names from a C# application.

        static void Main(string[] args)
        {
            const string connStr = @"server=.\SQLEXPRESS;database=BookStore;Trusted_Connection=True;";

            using (var conn = new SqlConnection(connStr))
            {
                conn.Open();

                using (var command = new SqlCommand())
                {
                    command.Connection = conn;

                    command.CommandText = "select name from sys.tables;";

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string name = reader["name"].ToString();
                            Console.WriteLine(name);
                        }
                    }
                }
            }

            Console.ReadLine();
        }

This code can be similarly adapted to fetch additional data from the queries we will see next.

More Advanced Queries

Retrieving other table metadata such as indexes, foreign keys, etc is not as straightforward as retrieving table names. However, you can get queries for just about any metadata you need from the Querying the SQL Server System Catalog FAQ.

Here’s the query to get column data types:

SELECT c.name AS column_name
    ,c.column_id
    ,SCHEMA_NAME(t.schema_id) AS type_schema
    ,t.name AS type_name
    ,t.is_user_defined
    ,t.is_assembly_type
    ,c.max_length
    ,c.precision
    ,c.scale
FROM sys.columns AS c 
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE c.object_id = OBJECT_ID('<schema_name.table_name>')
ORDER BY c.column_id;

Here’s the result:

syscolumns

This is how you get the indexes for a table (which may include primary keys):

SELECT i.name AS index_name
    ,i.type_desc
    ,is_unique
    ,ds.type_desc AS filegroup_or_partition_scheme
    ,ds.name AS filegroup_or_partition_scheme_name
    ,ignore_dup_key
    ,is_primary_key
    ,is_unique_constraint
    ,fill_factor
    ,is_padded
    ,is_disabled
    ,allow_row_locks
    ,allow_page_locks
FROM sys.indexes AS i
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id
WHERE is_hypothetical = 0 AND i.index_id <> 0 
AND i.object_id = OBJECT_ID('<schema_name.table_name>');

Here’s an example result:

sysindexes

And finally, here’s how you get info on the foreign keys:

SELECT 
    f.name AS foreign_key_name
   ,OBJECT_NAME(f.parent_object_id) AS table_name
   ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
   ,OBJECT_NAME (f.referenced_object_id) AS referenced_object
   ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name
   ,is_disabled
   ,delete_referential_action_desc
   ,update_referential_action_desc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc 
   ON f.object_id = fc.constraint_object_id 
WHERE f.parent_object_id = OBJECT_ID('<schema_name.table_name>');

Here’s the result of that:

sysforeign_keys

So even though these queries aren’t trivial to cook up, you can find just about anything you need from the Querying the SQL Server System Catalog FAQ, and just adapt it from there.

One Query to Rule Them

If you’re going to do something like code generation, you probably want to build one query that retrieves all the above metadata in one go. You can do that by combining the above queries. Fortunately, I’ve done that for you. Here you go:

SELECT
	-- columns / data types
	c.name AS column_name
    ,c.column_id
    ,SCHEMA_NAME(t.schema_id) AS type_schema
    ,t.name AS type_name
    ,c.max_length
    ,c.precision
    ,c.scale
	-- primary key / indexes
	,i.name AS index_name
    ,is_identity
	,i.is_primary_key
	-- foreign key
    ,f.name AS foreign_key_name
   ,OBJECT_NAME (f.referenced_object_id) AS referenced_object
   ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name
FROM sys.columns AS c 
INNER JOIN sys.types AS t
	ON c.user_type_id=t.user_type_id
LEFT OUTER JOIN sys.index_columns AS ic
	ON ic.object_id = c.object_id
	AND c.column_id = ic.column_id
LEFT OUTER JOIN sys.indexes AS i
	ON i.object_id = ic.object_id
	AND i.index_id = ic.index_id
LEFT OUTER JOIN sys.foreign_key_columns AS fc
	ON fc.parent_object_id = c.object_id
	AND COL_NAME(fc.parent_object_id, fc.parent_column_id) = c.name
LEFT OUTER JOIN sys.foreign_keys AS f
	ON f.parent_object_id = c.object_id
	AND fc.constraint_object_id = f.object_id
WHERE c.object_id = OBJECT_ID('dbo.Book')
ORDER BY c.column_id;

Here’s what you’ll get:

sysquerycombined

That includes column names, column types (along with stuff like decimal precision), indexes, primary keys, auto-increment (that’s is_identity), and foreign key info (constraint name, referenced table, and referenced column).

I’ve only tested this on a very simple scenario, so I’m pretty sure there are improvements to be made. While this can be considered a starting point, feedback is more than welcome.