Introduction
LINQ to SQL is used to manipulate data in a SQL Server database. Visual Studio ships with the LINQtoSQL designer which is a very powerful tool to generate LINQ. It is fast, easy to use, and great in lots of aspects.
There is a shortcoming though. You cannot customize the generated code. As with basically all the other designers, it works, it's easy to crank up a demo, but once you get in real life situations, you always want that little bit more. That extra bit is always something that depends on the problem. I will, therefore, not attempt to create better LINQ to SQL code. I will just show you how to customize and enrich the generated code in a matter of minutes. The customization is completely transparent to the standard LINQtoSQL designer, and you will still be using it for modeling your classes and data access.
Brief Explanations
We can split the problem into three scenarios depending on how different the desired generated code is from what LINQtoSQL generates. Each scenario will be discussed in more detail below, and will be implemented based on a free Visual Studio tool named Reegenerator.
With this tool, you define in the solution a separate project that holds code generators that is not part of the build. You then attach the LINQtoSQL project items (.dbml) to the code generation classes defined in that project. When you use the LINQtoSQL designer and save a file, the code generation will be invoked automatically in order to create the .Designer.cs that is dependent on the DBML file.
Using the Code
Download the zip file from the link. You will need Visual Studio 2008 and Reegenerator installed. Extract it, and load the solution file from the extracted folder into Visual Studio. The solution contains a project named CustomizeDesigners that contains the code generators, and a project named Business containing the DBML files and the database that is used as a source for the LINQtoSQL designer. In both the Business and RenderersLibrary projects, there are three Scenario folders that host the DBML and the code generators, as described below, for each scenario.
In order to trigger the code generation, you need to edit and save a DBML file, or right-click on it and invoke "Run Custom Tool". To debug, you simply set a breakpoint in a code generator code in the RenderersLibrary project and start a debug session. The session will invoke a second instance of Visual Studio. Load the same solution in the second instance, and trigger the code generation by saving a DBML file. The generated code will be saved as project items (generally ".Designer.cs") dependent on the DBML file. You will need to click on the plus sign that sits on the left side of the DBML project item in order to expand its children.
To inspect what a DBML project item is attached to, right click on it and click "Attach Renderers...".
The image below shows how your working environment will look like:
Scenario 1 - Small Changes
In this case, we most likely want to simply invoke the LINQtoSQL default code generator, grab the resulting code as a string, manipulate it the way we want, and then send the modified code back to Visual Studio.
The DBML file located in the Scenario1 folder in the business project is linked with the CustomizeDesigners\Scenario1\CustSmallChangesToDefaultLINQtoSQL class. The code to achieve the desired modification is remarkably simple:
public class SmallChangesToDefaultLINQtoSQL : Generators.CodeRenderer
{
public const string CustomToolName = "MSLinqToSQLGenerator";
public override Generators.RenderResults Render()
{
byte[] resultsAsBytes = base.RunOtherCustomTool(CustomToolName);
string results = System.Text.Encoding.Default.GetString(resultsAsBytes);
string modifiedResults = AddSomething(results);
return new Generators.RenderResults(modifiedResults);
}
private string AddSomething(string results)
{
results = string.Format(@"// -------------------------------------------------------
// Automatically generated.
// Generation date: {0}
// Generated by: {1}
// -------------------------------------------------------
{2}",
System.DateTime.Now.ToString("yyyy-MM-dd hh:mm"),
System.Security.Principal.WindowsIdentity.GetCurrent().Name,
results);
return results;
}
}
As you can see in the code above, we invoke the standard code generator to retrieve the code generated by Microsoft's designer. Once you have it, you can manipulate it by adding code to it, or replace certain portions of it with string manipulation routines or Regular Expressions.
To test, edit Business\Scenario1\CustomersLINQ.dbml in the Business project, save it, expand its children, and open Business\Scenario1\CustomersLINQ.Designer.cs. You can see that the file contains extra code added to Microsoft's code.
To debug, set a breakpoint in the Render method, launch a debug session, load the same solution in the second Visual Studio instance, and perform the same steps as you do when you test.
The example code generator has another method, AddAttribute, that shows how to do a slightly more complex manipulation. You can use Regular Expressions if the desired changes are more complex.
Scenario 2 - Significant Additions
In this scenario, you are fine with what Microsoft generates, but you want extra code. Examples of extra code could be adding a method to each class generated from a table, or creating SQL scripts for the database objects.
The easiest way to achieve this is to link the DBML file to two code generators. The first one invokes Microsoft's code generator, and you might want to slightly alter the results as per Scenario 1. The second one generates the extra code.
For properly building a code generator for the DBML file, you need to be ble to deserialize it into a serializable class instance. This is achieved by having a schema in the code generators project that creates serializable classes from it. The second code generator deserializes the DBML file into such a class and then uses it to generate what is needed.
The DBML file schema can be found at C:\Program Files\Microsoft Visual Studio 9.0\Xml\Schemas\DbmlSchema.xsd supposing that you installed Visual Studio in the default location. This schema has been added to the CustomizeDesigners project to Dbml\DbmlSchema.xsd. It is linked with the CustomizeDesigners\XsdRenderers.rgt code generator that invokes the same mechanism as xsd.exe to create serializable classes from schemas. Expand the children project items for DbmlSchema.xsd to see the serializable classes.
Business\Scenario2\CustomersLINQ.dbml is linked to two code generators (right click on it and click "Attach Renderers..." to inspect), and two .cs files get created/updated when you save it. The Business\Scenario2\CustomersLINQ.Designer.cs file is generated by CustomizeDesigners\Scenario2\SmallChangesToDefaultLINQtoSQL.cs exactly the same as per Scenario1.
The Business\Scenario2\CustomersLINQ.Hello.cs file is generated by CustomizeDesigners\Scenario2\AddHelloMethodToTables.rgt. This is a code generator template file that looks pretty much like an ASPX page. There is also a code-behind for it, CustomizeDesigners\Scenario2\AddHelloMethodToTables.rgt.cs, that contains manually written code. CustomizeDesigners\Scenario2\AddHelloMethodToTables.Designer.cs is the code representation of the template. Together with the code-behind, it forms a class that will be invoked when the DBML file gets saved.
The CustomizeDesigners\Scenario2\AddHelloMethodToTables.rgt code generator template looks like:
<%@ Template Language="C#" ClassName="AddHelloMethodToTables" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="CustomizeDesigners.Dbml" %>
namespace <%= base.ProjectItem.CodeNamespace %>
{
<% RenderTables(); %>
}
<%@ Method Name="RenderTable" %>
<%@ Parameter Name="table" Type="Table" %>
partial class <%= table.Type.Name %>
{
public string Hello()
{
return "Hello from table <%= table.Type.Name %>";
}
}
<%/ Method %>
As opposed to ASPX, you can define render methods that help you break down your code generation work into more manageable units. Also note the <% RenderTables(); %> call just under the namespace. This calls a function that is manually written in the code-behind.
The Scenario2\AddHelloMethodToTables.rgt.cs template code-behind looks like:
public partial class AddHelloMethodToTables
{
Database _database = null;
public override void PreRender()
{
base.PreRender();
this._database = DbmlSchemaFactory.Create<Database>(base.ProjectItem);
}
private void RenderTables()
{
if (this._database.Table == null)
return;
foreach (Table table in this._database.Table)
{
RenderTable(table);
}
}
}
The DBML file is deserialized into the _database property before the code generation takes place. Please note the implementation of RenderTables which gets called from the template file. In its turn, it calls the RenderTable(Table table) that is defined in the template.
To test it, edit Business\Scenario2\CustomersLINQ.dbml, save it, expand its children, and inspect the content of the two children cs files.
To debug, set a breakpoint in the PreRender method, launch a debug session, load the same solution in the second Visual Studio instance, and perform the same steps as you do when you test.
Scenario 3 - Complete Rewrite
This scenario applies when you are completely unhappy with what the designer generates for you, or you simply want to use the designer for something else (e.g., create pure business objects that know nothing about LINQtoSQL).
CustomizeDesigners\Scenario3\DbmlRenderer.rgt does a good enough job at replicating Microsoft's code generator. In itself, it has little value as, if Microsoft's code was close to good enough for you, your problem would fall under Scenario 1 or 2.
Its purpose is to be an example and provide the familiarity and structure to start with for building your heavily customized code generator, taking advantage of an already made designer.
Business\Scenario3\CustomersLINQ.dbml is linked with CustomizeDesigners\Scenario3\DbmlRenderer.rgt, and you can do the testing and the debugging the same way as per the previous scenarios.
Final Note
The possibilities are limitless on what you can now do with the LINQtoSQL designer. Here are some examples:
- You can have your own LINQtoSQL customizations that magically get created when you use the designer.
- You can generate something different, like SQL scripts for the tables, and Stored Procedures used by the DBML file.
- You can use the designer for something completely different and unrelated to LINQtoSQL as long as the designer does a good enough job for what you are trying to achieve.
