Speed O/R Mapping with LINQ to SQL: Visual Studio 2008 SP1 Add-Ins Remove LINQ to SQL Roadblocks

• 11/19/2008

LINQ to SQL is more stable and bug-free than most Microsoft version 1 products, but data-oriented .NET developers com-­plain the free O/RM tool doesn't offer the Entity Framework (EF) capability to update its object model from changes to the underlying data store's schema. Also, conforming object and property names to an organization's naming conventions takes time and effort. Another developer issue is the difficulty of customizing the code-generation process without access to LINQ to SQL's source code. Here, I'll describe a commercial third-party add-in to solve the first problem pair and a Text Template Transformation Toolkit (T4) template add-in from a LINQ to SQL team developer that customizes code-generation.

Update the Object Model
Thailand's Huagati Systems Co. Ltd. is a .NET and SQL Server consultant and ISV of commercial .NET applications for the financial and human resources and recruitment industries, as well as utilities for Visual Studio 2008 Service Pack 1 (SP1). The Huagati DBML/EDMX Tool began life as a tool to eliminate the single most common cause of LINQ to SQL's rejection as a O/RM tool for anything but extremely small projects: Any change to the persistence data store's schema required manually hacking the XML designer and mapping (ContextName.DBML) and class (ContextName.designer.cs or VB) files or creating a new object model from scratch, which erased all previous modifications to these files. Kristofer Andersson, Huagati's owner and lead developer, designed version 1 of the DBML/EDMX Tool to compare the LINQ to SQL O/RM diagram with the database and flag all changes due to new or dropped tables, as well as new, modified, or dropped columns or foreign keys, and modified primary keys. You then run the update option and confirm the changes to be made. A ContextName.dbml.p-refs file stores the list of accepted and rejected changes for use during the next update.

Andersson's first update to the tool added the capability to rename EntityType, EntitySet and property (member) names, and Entity property names according to .NET naming standards, including removing underscores and applying title case. The add-in's Class and Property Naming Preferences dialog also has text boxes that let you remove and add name prefixes and suffixes. Andersson recently extended the renaming feature to work with the Entity Framework's .EDMX files also; you can revert EF version 1's automatic addition of "Set" suffixes to pluralized EntitySet names when you singularize EntityType names (see Figure A).

The last Tools update -- at the time I wrote this sidebar -- adds documentation for LINQ to SQL EntityTypes and properties similar to that provided by EF's EDM Designer. However, the EDM Designer doesn't add documentation to the [System.ComponentModel.Description()] attribute. The Tool's LINQ to SQL implementation improves greatly on the EF feature by adding Description text (if present) and a list of indexes to the /// <summary> element and Description() attribute:

///<doc><summary>dbo.Categories: 
///Categories of Northwind products.
///Indexes:
///PK_Categories (CategoryID); 
///CategoryName (Name of food /// category.)
///</summary></doc>
[System.ComponentModel.Description(
    "dbo.Categories: Categories of Northwind  
         products.\r\n
     Indexes:\r\n
         PK_Categories (CategoryID); \r\n
         CategoryName (CategoryName)"
)]
public System.Data.Linq.Table<Category> Categories
{
	get
	{
		return this.GetTable<Category>();
	}
}

Documentation appears in tooltips in the LinqToSqlRadCS.sln sample project's Categories EntitySet, and Category EntityType has documentation examples based on the table and column Description properties from later Access versions of Northwind.

Huagati offers a 30-day free trial version for a maximum of 150 database tables per DBML or EDMX file; a single-user license for the unlimited-usage Professional Edition is $119.95.

Customize LINQ to SQL Code-Gen
Many developers need to customize the C# or VB class code generated from LINQ to SQL's .DBML files. For example, LINQ to SQL version 1's SerializationMode property value is restricted to None or Unidirectional. .NET 3.5 SP1's Windows Communication Foundation now supports bidirectional serialization, but unidirectional serialization can't handle object graphs with cyclic references because it doesn't permit decorating both EntityRef and EntitySet properties of an EntityType with DataMember attributes (see Additional Resources). This problem could have been corrected with a simple change to the LINQ to SQL implementation, a change that -- for reasons unknown -- the team didn't make when updating LINQ to SQL for SP1.

To overcome problems such as this, Damien Guard, a developer on the LINQ to SQL team, developed a T4 template that you can substitute for SqlMetal.exe; T4 is included with VS 2008 SP1. The T4 template takes the .DBML file as its input and uses LINQ to XML to wrap the elements with objects and transform the XML to a custom C# or VB class file. Moving a LINQ to SQL project from standard to T4 template code-gen requires several steps:

1. Add the L2ST4.ttinclude and CSharpDataContext.tt or VBNet- DataContext.tt to your project depending on your language type.
2. Rename xDataContext.tt to match your .DBML file but with a .TT extension instead of .DBML and include it (and, optionally, the DbmlName.tt file) in your project.
3. Set the existing DbmlName.designer.cs/vb Build Action property to None to ignore LINQ to SQL's built-in code generation with the MSLinqToSQLGenerator custom tool.
4. Right-click the DbmlName.tt file icon in Solution Explorer and choose Run Custom Tool to generate the DbmlName.generated.cs/.vb class file.
5. If you're using design-time databound controls, add the project's namespace to the DbmlName.generated.cs/.vb file.
6. Press F5 to build and run the project with code generated by the T4 template.

To enable bidirectional serialization, right-click on the O/R Designer surface, choose Properties, change SerializationMode's value from None to Unidirectional, and save the change. Open the DbmlName.tt file, uncomment the data.Serialization = SerializationMode.DataContractSP1; line, save the changes, right-click on the file, and choose Run Custom Tool to generate the DbmlName.generated.cs/.vb class file.

Making other changes requires editing the DbmlName.tt file, but that probably will be simpler than wrapping the CodeDOM and overriding the AlterStream() method, as Kathleen Dollard described in her article, "Customize Code Generation in EF" (September, 2008). -- R.J.