Ben Reichelt’s Weblog

The Refresh Problem with LINQ to Sql

When the Orcas beta 2 came out this summer I decided to download it and take a look at the new changes in the IDE as well as check out LINQ.

After reading K. Scott Allen’s post about building a testable data access layer using LINQ to Sql, I wanted to finish his implementation. I was using a small database that I made at home to test out the data access layer, by using the visual LINQ designer in Orcas.

As I was playing with the designer I wanted to add a new column that I had added in the database. So I right-clicked on the table in the designer, looking for the “Refresh” command. I didn’t see it. I looked at the database level, not there either. I looked everywhere in the IDE, but there is no “Refresh” command because it doesn’t exist.

So the way you refresh a table in the designer is to delete it, and any column customizations you’ve done, and re-create it. This sucks. I frequently like to name my database columns all lowercase, with a “_” in between word (”customer_id” for instance), but I want my .net code to have standard .net names (”customer_id” -> “CustomerId”). So the visual designer is out.

SqlMetal

Next, I looked at SqlMetal. SqlMetal is a command line tool from Microsoft that you point at a database and it generates LINQ to Sql code based on the database schema, much like the visual designer does. There is an option in SqlMetal to instead generate a metadata xml file which can then be fed into SqlMetal to generate code based on the xml file. This got me thinking that if I could modify this metadata before it gets fed back into SqlMetal, I could rename my columns programmatically, which implies automation and consistency.

SqlPlastic

So I started working on my own tool, SqlPlastic, that wraps SqlMetal. SqlPlastic is a command line tool that takes arguments similar to SqlMetal. SqlPlastic runs SqlMetal on the specified server and database to generate the intermediate xml file. Then, SqlPlastic runs your custom actions, called “MetaDataTasks” that can modify the content of the xml file. After the custom actions are run, SqlPlastic runs the modified xml file into SqlMetal to generate c# or vb code.

You specify your custom actions in C# or vb class files, classes that implement SqlPlastic.MetaData.IMetaDataTask. At runtime, SqlPlastic finds all your custom classes and compiles them, then queries the assembly for all the classes that implement SqlPlastic.MetaData.IMetaDataTask, instantiates them, and executes them. This way your code is interpreted each time so you can make changes to your code and simply re-run SqlPlastic to see the changes.

The scenario that I had in mind when coding this was a continuous integration environment, where the persistence code gets generated during every build. The fact that SqlPlastic and SqlMetal are both command line tools makes them integrate easily into automated builds, and you gain confidence that your persistence code is current if you are constantly regenerating it.

As of right now, there are 8 built in tasks that SqlPlastic exposes for you to use:

Renaming Tasks

• RenameTableTask
  • Changes the .net name of a Table.
• RenameTypeTask
  • Changes the .net name of a Type. Database tables can have several types.
• RenameColumnTask
  • Changes the .net name of a Column.
• RenameAssociationTask
  • Changes the .net name of an Association .

Aggregate Tasks

• AggregateTableTask
  • Iterates over all tables and applies custom tasks to each one.
• AggregateTypeTask
  • Iterates over all types and applies custom tasks to each one.
• AggregateColumnTask
  • Iterates over all columns and applies custom tasks to each one.
• AggregateAssociationTask
  • Iterates over all associations and applies custom tasks to each one.

The way you use these rules is to create a new class that inherits from any of these rules and then simply set the necessary properties (for the Renaming tasks) or override ExecuteCore (for the Aggregate tasks). When SqlPlastic is run, it will find your custom tasks and compile and run them against the generated metadata.

Trying it out

SqlPlastic is hosted at Google: http://code.google.com/p/sqlplastic/

You can get the code at: http://sqlplastic.googlecode.com/svn/trunk

I haven’t put together a release yet, so you’ll have to download the code and open it in VS 2008 to compile it. Theres a unit test project that I maintain as well in there.

The code is all written in VB, but you can use vb or c# to write your custom tasks, you just need to specify which language you want to use by using the /language: command line switch.

I’d love to hear what people think of this, so please don’t hesitate to leave feedback, thanks!