Left Lobe Logic

Filter tables and rename associations generated by SqlMetal using Powershell August 26, 2008

One of the most irritating features I initially found when using LINQ to SQL diagrams was the all or nothing approach for managing tables and views in a way that allows quick regeneration after database schema changes. Out of the box (VS2008 pre-SP1) you have two options:

1. Build LINQ to SQL dbml mapping files by hand – dragging tables, views, and stored procedures from the Server Explorer onto the designer surface. This works great and has the added bonus of being able to rename tables, columns, associations, and of course remove them all as well. The designer surface entities are based on definitions in a .dbml file, and the entity code is generated into a .designer.cs file.
2. Use SqlMetal.exe to generate the .dbml and class for you. I typically use two commands and drop them into a “GenerateDbml.bat” file so that on a schema change we can just double click and go.

My preference definitely lies with the latter since despite the ability to do otherwise the object relation model should be close to the relational database model as possible. On the cosmetic side of things it ensures your naming standards are kept consistent, on the database level it ensures that all column types and foreign key mappings are well thought out and meaningfully names, and on the code level it means that the amount of custom code in the domain model is kept at a minimum.

My GenerateDbml.bat usually does it in two steps and looks like this:

One thing to notice is that the generated .dbml file is XML. Now, XML may not be great but at least it gives developers a tonne off standard API for dealing with it. In this instance we’re going to leverage it to quickly prune out unwanted tables and relationships before generating our object model and we’re going to make use of PowerShell to do it!

As David blogged recently, I’ve created a generic .ps1 script to handle our calling of SqlMetal as we did in our .bat file. We’ll also parameterise the script to help ditch all our Northwind references and help us set up some usual naming conventions. Our settings for cleaning our generated DBML by removing tables, renaming tables, and renaming associations are stored in an XML file.

Now you can simply call it with:

To download the full source code you’ll find it in Left Lobe Logic’s GoogleCode project (it’s SVN). Note: The code is substantially refactored from that posted by Dave.

I’ll try to share some of the individual learnings from the script in later posts.

Enjoy!