Graeme Hill's Dev Blog

Manipulating automatically generated LINQ to SQL classes

Star date: 2009.042

There are a few problems with the data model code that is automatically generated in LINQ to SQL. The most obvious issues are that class names are not capitalized and that tables with two foreign keys to the same table will not have descriptive names. For example, if a table has columns firstUserID and secondUserID which are both foreign keys to the users table then you would probably hope to see the properties FirstUser and SecondUser on that class. Unfortunately, what you will actually get is User and User1 which is pretty much pointless because it is very difficult to tell which one is which. One way to tackle this is to simply change the code after it has been generated so that it looks like you want it to. But then as soon as you change the database and import the model again your changes are lost and have to be redone every time. Ideally, the automatically generated code would be formatted exactly as you want it.

The solution I came up with was to use SqlMetal to generate XML output, then manipulate that output and feed it back into SqlMetal so that it can generate the code from the altered XML. As a naming heuristic, I specified that a foreign key column's property would have a name derived from the column name without the "ID" suffix and a capitalized first letter. Depending on the extent of the changes you plan on making, you may want to make an XSLT file to translate the XML, or simply use some regular expressions.

One downside to this approach is that SqlMetal often cannot generate code for stored procedures because it is unable to determine the return type without actually running the procedure. To get around this I told SqlMetal not to generate any code for the stored procedures (just omit the /sprocs argument), then manually incorporated the XML for my stored procedures in the XSLT file so that it would be injected into the second input to SqlMetal that actually generates the code.