Scripting changes to Linq To SQL DBML files

I've been playing with Linq To SQL and have been getting very bothered by the method MS expect us to use to develop the mappings. You can't regenerate your dbml schema without overwriting the changes you made in the designer. Not only this, but your changes won't merge well with changes made by other developers on your team when it comes to check-in time as they are all in xml files.

The process that is required for safe, team development that I've come up with is this:

1. Update database schema (using something like migratordotnet) 2. Generate just the dbml file from schema using sqlmetal.exe 3. Run a change script of some kind against the dbml file to update it to include your custom mappings. 4. Generate the classes from the dbml file using sqlmetal.exe

Step 3 is the missing link. You could use xslt, xpath or Linq to XML. You could even generate change scripts using the designer and MS XML Diff. But the easiest thing to use is a Linq To XSD program and put it in the post build events for a project that contains the generated XSD file as an embedded resource. Linq To XSD gives you typed control over the dbml file in C#.

This lets you write something like this in your script:

var dbml = XRoot.Load(@"..\..\GdwDb.dbml");
foreach (var table in dbml.Database.Table)
{
foreach (var column in table.Type.Column.Where(c => Char.IsLower(c.Name[0]))
{
column.Name = column.Name.ToUpper()[0] + column.Name.Substring(1);
}
}
dbml.Save(@"..\..\GdwDb.dbml");

This snippet will update all camelCase field names to PascalCase ones. Of course you can do anything, like creating inheritance mappings, removing prefixes etc. One you write a couple of helper methods you should be away.

I would have thought a similar process could be used for NHibernate and Entity Framework, although I know some people prefer to work backwards from code to database.

You can download the latest version of Linq To XSD from here and download the Linq classes I generated for the Dbml xsd here. Include that in a project with a reference to Microsoft.Xml.Schema.Linq.DLL and you can create your own mapping update script.

Info on using SqlMetal to generate the dbml file, and the appropriate classes can be found on Ben Hall's blog here.

I'll upload a demo project later, as this could all be a little confusing.