Getting at your database metadata is a pain in the ass. In my casual development of NDeavor, I’m also writing some providers for various database platforms, currently focused around MySql because it has good .NET support and it’s not Sql Server, which I actually work with every day (in the professional sense).  It became clear pretty quickly that without a fully-featured API like SMO that using the functionality in .GetSchema() off of an ADO.NET-compliant vendor’s connection class is the best bet all around.

Being that I want a solution I can use for all the providers I end up doing for NDeavor, and be as lightweight as possible, T4 templates was the best choice. Oh, and I want it LINQ-enabled as well. It will basically do the following:
- Call .GetSchema() on a DbConnection instance to get available metadata (as well as specific collections you tell it to).
- Ouput a C# POCO class with properties mapping to each column of the metadata’s DataTable that GetSchema() returns
- Output a DataContext-like class allowing for LINQ goodness.

You might think:

“Really? You’re getting the schema of a DataTable returned from DbConnection.GetSchema() so it can used to create the schema for a C# class which will be used to query the schema of a relational database?”

and you’d be right. So?

The idea of DBConnection.GetSchema(), in Microsoft terms, was to allow querying of vendor-specific metadata about the underlying data source, so as a lowest-common-denominator it returns instances of the ubiquitous DataTable and DataSet. Passing around loosely typed blobs of data won’t cut it, so we’ll be mapping these to strongly types classes, similar to that of an ORM framework. That sounds tedious and like a job for some lightweight code generation, in this case it will be some T4 template magic in Visual Studio 2008. These classes will just be thin wrappers over DataRows, they won’t have the associations/relationship semantics you’re used to seeing with Linq to Sql or Entity Framework, they’re just glue to support building up a larger more api-friendly object model. Any of the joins you do from the resulting object collections will be in-memory, there’s nothing dynamically querying the underlying schema. It’s all smoke and mirrors.

5 Steps to LINQ-ifying to your Db Schema

Download LinqToSchema

1. Add LinqToSchema.tt to your Visual Studio 2008 project, and remove the ‘Custom Tool’ setting – this is not the template itself, just for inclusion inside the ‘host’ template.
2. Create a new T4 template by adding a text file to your project and renaming the extension to .tt
3. Write the template to look similar to this:

   <#@ template language="C#v3.5" hostspecific="True" debug="true" #>
   <#@ output extension="cs" #>
   <#@ include file="LinqToSchema.tt" #>
   <# 
       LinqToSchemaSettings settings = new LinqToSchemaSettings
       {
           ConnectionStringName = "Chinook_MySql",    // name of connection string in local App.Config/Web.Config - must be in same dir as this file
           TargetNamespace = "Chinook.Schema.MySql",    // what namespace to put the generated code in
           ClassNamePrefix = "MySql",                        // what prefix, if any, to put on the generated class names
           ClassVisibility = Visibility.Public                // visibility of the generated classes (Public or Internal)
       };
      
       LinqToSchemaClassesGenerator generator = new LinqToSchemaClassesGenerator(settings);
       generator.Run();
   #>

4. Save your just-created template for it to be processed by the T4 engine.
5. Compile/write other code that uses the generated classes. Such as:
   

   using (MySqlSchemaContext ctx = new MySqlSchemaContext(@"server=servername;user id=userid;persist security info=True;database=chinook"))
   {
       var query = from tbl in ctx.Tables
                   join col in ctx.Columns on tbl.TableName equals col.TableName
                   where tbl.TableName.ToLower() == "track"
                   select new
                   {
                       tbl.TableCatalog,
                       tbl.TableName,
                       col.ColumnName,
                       col.DataType,
                       col.IsNullable
                   };
       query.ToList().ForEach(p => Console.WriteLine(p));
   }

There are some more options such as explicitly setting the connection string, which are explained in the readme. And if it wasn’t already clear, because this is using DbConnection.GetSchema(), it’s usable on any database platform that returns meaningful metadata using from their platform-specific DbConnection implementation.