T4 Toolbox: LINQ to SQL classes generator


Build 9.1.20.1 of T4 Toolbox includes a new, ready-to-use code generator that produces LINQ to SQL classes in C#. This generator uses .dbml file produced by the standard LINQ to SQL designer in Visual Studio and can be used as an alternative to the standard MSLinqToSqlGenerator.

You may be wondering why do we need yet another LINQ to SQL code generator, especially now that ADO.NET team placed it in maintenance mode in favor of the Entity Framework. Even though the Entity Framework may be more flexible and powerful, LINQ to SQL is currently the only data access framework built into .NET Framework and Visual Studio that allows modeling and generating both database and application code. LINQ to SQL strikes a good balance between simplicity and power, making it a great fit for a large class of business applications. Simplicity of modeling both database and application code makes it superior to Entity Framework on smaller projects that don’t require advanced O/R mapping or don’t need to access different types of data sources.

However, built-in code generation tools, SqlMetaL and MSLinqToSqlGenerator, have several limitations that make it difficult to scale LINQ to SQL solutions beyond the simplest scenarios. In particular, the application code is generated in a single file, which can reach size of tens of thousands lines of code for medium-sized databases and hundreds of thousands lines of code for large-size databases. The other limitation is the lack of direct generation of database source code. You can use CreateDatabase method of DataContext class to create a database based on a LINQ to SQL model. However, you will need to reverse engineer this database into another database tool, such as Visual Studio Database Edition or Enterprise Architect in order to model, maintain and evolve a database on a non-trivial project.

The code generator described in this article addresses the first limitation of LINQ to SQL by splitting generated DataContext and entity classes into separate .cs files. These files are a lot smaller and easier to work with than the monolithic designer.cs file produced by standard tools. The second limitation of not generating database code directly will be addressed by another code generator called "LINQ to SQL Schema", which is still under development. Together, these two code generators will enable true model-driven development approach for LINQ to SQL solutions.

Usage

  • Download and install the latest version of T4 Toolbox from CodePlex.
  • In Visual Studio, create a new or open an existing C# project and select Project->Add New Item in the main menu.
  • In the Add New Item dialog, select Code Generation->LINQ to SQL model

image

  • Enter the name you want to use for the new LINQ to SQL model (.dbml) file. Note that this name also determines name of the DataContext class that will be generated (NorthwindDataContext will be generated for Northwind.dbml). Click the Add button when finished.

Solution Explorer, Empty LINQ to SQL model Note that two files were added to your project - Northwind.dbml and Northwind.tt (depending on the name you entered in the Add New Item dialog).

Northwind.dbml

This file contains a LINQ to SQL model. Based on its extension (.dbml) Visual Studio automatically opens the standard Object Relational Designer when you double-click this file in the Solution Explorer. You can populate the model using either the Toolbox or the Server Explorer.

Object Relational Designer

  • Populate the model by dragging all tables from the Northwind database in Server Explorer onto the design surface of Northwind.dbml.
  • Save Northwind.dbml.

Note that standard Northwind.designer.cs file is not created under Northwind.dbml. This file is normally produced by the MSLinqToSqlGenerator, but in our case the standard code generator was replaced with Northwind.tt.

Northwind.tt

This is a T4 code generation file. It creates an instance of LinqToSqlGenerator class, assigns the DbmlFile property and calls the Run method to generate DataContext and entity classes for the specified .dbml file.

<#
// <copyright file="Northwind.tt" company="Catapult Systems">
//  Copyright © Catapult Systems. All Rights Reserved.
// </copyright>
#>
<#@ template language="C#v3.5" hostspecific="True" #>
<#@ output extension="log" #>
<#@ include file="T4Toolbox.tt" #>
<#@ include file="T4Toolbox\LinqToSql.tt" #>
<#
    // Generate entity classes from a LINQ to SQL class model (.dbml file)
    LinqToSqlGenerator generator = new LinqToSqlGenerator();
    generator.DbmlFile = "Northwind.dbml"
    generator.Run();
#>

Solution Explorer, Populated LINQ to SQL model

Note that, unlike with MSLinqToSqlGenerator, DataContext and entity classes are not automatically regenerated when .dbml file changes. You will need to "Run Custom Tool" to regenerate the C# code manually.

  • Right-click Northwind.tt in Solution Explorer and click Run Custom Tool item in the context menu.

Note that a number of .cs files were generated under Northwind.tt. Each of them contains a single class, which you can recognize by the first part of the file name: ClassName.generated.cs. All of these classes are declared as partial. You can extend these classes manually, by adding a new C# file to the project, such as Customer.cs to extend the partial class defined in Customer.generated.cs.

For complete details about this code generator, please refer to the following source files: LinqToSqlGenerator.tt, LinqToSqlDataContextTemplate.tt and LinqToSqlEntityClassTemplate.tt, which are installed in C:\Program Files\T4 Toolbox\T4Toolbox\LinqToSql directory by default.

Deviations from MSLinqToSqlGenerator

There are several important differences in code produced by this code generator compared to the standard MSLinqToSqlGenerator.

  • While MSLinqToSqlGenerator uses fully-qualified, global:: type names, this template produces code with “using” directives and abbreviated names. This is done to make the generated code easier to read.
  • While MSLinqToSqlGenerator generates field names as property name with an underscore prefix, this method simply converts the property name to camelCase. This is done for consistency with the StyleCop rule SA1306: Variable names must start with a lower-case letter.
  • While MSLinqToSqlGenerator assumes that unknown data types are reference types this method assumes that unknown types are value types. Assuming that users will mostly use built-in types or known CLR types in the model, the most frequently used custom types will be enumerations. Treating unknown types as value types allows us to generate nullable enum types when model defines a nullable property.
  • While MSLinqToSqlGenerator always emits InheritanceMapping attributes with inheritance code values enclosed in double quotation marks, making them strings, this generator emits them verbatim, preserving the original data type. This allows using enum values as inheritance code directly, without the need to convert. User can still use string values by enclosing them in double quotation marks in the model/designer.
  • While MSLinqToSqlGenerator produces entity code that fires PropertyChanging event with empty property name, this template produces code fires PropertyChanging events with actual property names.
  • While MSLinqToSqlGenerator produces a DataContext code that always uses an empty, explicitly created AttributeMappingSource in constructors that don’t receive mappingSource as a parameter, this generator simply calls the matching base constructor.
  • Generation of method wrappers for stored procedures and functions is not supported at this time.

Closing thoughts

Give this code generator a try. I don’t claim to be a LINQ to SQL expert and will not be surprised if you find bugs or missing functionality. I would love to hear your feedback about this code generator and about the idea of model-first approach to LINQ to SQL solution development.

Special thanks to Jeff Odell, who did a lot of work on this code generator, and still refuses to blog.

Download

Article Updates


Write a Comment

Take a moment to comment and tell us what you think. Some basic HTML is allowed for formatting.

Reader Comments

Oh - you are supposed to WRITE SOMETHING on a blog?

I agree Linq to SQL solves a whole class of applications in a simpler fashion that EF. If you have a SQL Database and you control its structure, it is a great solution.

Alas it appears we will have to settle for a kinder, gentler EF - maybe in version 2.0, or 3.0 …

Great work on this generator, which also shows how T4, with the T4 Toolbox, provides a viable code generation platform wired into Visual Studio.

Good development. But true model driven development is not possible with LINQ to SQL. Many-to-many mapping would require a class for the table that is not needed in the model.

Another thing, migrations are the future for database versioning. How will your tool address evolution of the model without affecting the data already in the previous database schema?

I agree, a true swiss army knife should have at least 12 tools. I can’t believe I have one with just 10 :).

For deployment of the new scema to existing database, I would use another tools, such as Database edition of Visual Studio Team System or Red Gate.

Hi Oleg,
After reading your blog I’ve decided to use T4 templates in my Windows Forms application to generate HTML pages, I’ve been satisfied with this solution and would like to thank you for all the information.

The only unclear question is there any way to distribute Microsoft.VisualStudio.TextTemplating.dll with my application? This http://social.msdn.microsoft.com/Forums/en-US/vsx/thread/64d182ca-7cd3-429c-b7d6-706663ee9402/ page says it’s impossible, but it would be great to hear you opinion on that.

I think I will agree with Gareth on this, after all he designed it :). T4 cannot be redistributed separately from Visual Studio. However, it may be possible to redistribute pre-compiled templates in a future version (more here).

Oleg, is it possible to add ‘_’ for all private members of generated class? I’m having trouble with field names like ‘class’…

You can remove the “Run Custom Tool” step by adding a pre-build step to the project containing the dbml file.

See my post here: http://melgrubb.spaces.live.com/blog/cns!A44BB98A805C8996!256.entry

Good point, Vadim. I will change it to generate literal identifiers (@class) for keywords.

Hello,

I keep getting the following errors:

Running transformation: Invalid association. Number of columns in ThisKey () and OtherKey (ProductID) doesn’t match

Help??

Rasheed, could you post XML definition of the association that is causing the error?

Thanks, Oleg Sych. I’m able to consistently reproduce this. Here are the steps:

1. Create a C# class library project in VSTS 2008
2. Create a DBML file and connect to Northwind Database (SQL Server 2005)
3. Drag and Drop all the tables to DBML
4. Build the project
5. Right click the .tt file and click “run custom tool”
6. You would receive 7 errors as I reported earlier

Rasheed, have you saved the DBML file after adding the tables to it (step #3 in your description) and before generating code (step #5)?

Yes. I saved the DBML. I just verfied one more time. The issue is same.Thanks for your prompt response

Rasheed, could you submit a new bug on CodePlex and upload your DBML?

[…] Architecture Edition user. A short quote below explains: Build 9.5 of T4 Toolbox extends the LINQ to SQL generator introduced in version 9.1 with support for database schema generation. This gives you the ability […]

[…] Desde AjGenesis puedo leer el archivo .dbml de Linq2Sql, que se puede generar desde la línea de comando con SqlMetal, tengo que estudiar Oleg Sych - » T4 Toolbox- LINQ to SQL classes generator […]

Thanks Oleg for this thorough explanation. So by changing the tt file(s) I can change what code is generated based on the dbml.
But I am wondering: is it also possible to extend the dbml file itself, so that I can add extra properties to the columns present in the tables? (By properties I mean those in the “Properties Window” of Visual Studio.) I would like to use these extra properties in the code generation.
As a simple example: Wuold it be possible to add a “DefaultValue” property that I can set on each column and use this to add a DefaultValueAttribute to each generated property (in addition to the currently added ColumnAttribute)?

Wouter,

It would be great if LINQ to SQL designer could support additional attributes. Unfortunately, I don’t think it does. It may be possible to hack the DBML in XML editor, but at that point, you would be better off writing this logic by hand in partial classes.

Oleg

Did Rasheed ever find a solution to his problem? I’m getting the same issue.

Erik, could you be more specific?

How can I change the code generation to change the names of generated classes? Like getting rid of prefixes etc.
Thanks

Jacobus,

In the LINQ to SQL designer, you can specify different names for the class and for the table it is mapped to. This can be done using the Properties window.

Oleg

Oleg

I had the same error messages as Rasheed when I ran your demo. After some digging, I found following single line solved the problem.

I am sure you bloged about this but the line is missing in your sample code.

Anyway, great blog! Learned a lot.

This seems like a very interesting tool, but the lack of code generation for sprocs and removing the underscores from the property backers concerns me.

Why all the rigmarole with changing the property backers to camelCase without the leading underscore?

Being a long-time VB programmer, I like the property name with an underscore as the name of the private field. While this isn’t really a show-stopper with VB anymore, it’s still much more readable and avoids problems like the one Vadim described above.

You mentioned the reason behind this change is to comply with StyleCop. Why is this even a rule in StyleCop? I don’t use StyleCop, but it seems like a ridiculous rule tailored for C# code.

Apparently, MS programmers don’t use StyleCop, either. The field names for the property backers of the new “auto-implemented properties” in VS 2010 use underscores.

I tried the NorthwindSolution but when I exceute the Northwind.tt-file I get an error:
LinqToSqlDataContextTemplate.tt line 1 Column 1, here the File:
The LinqToSqlDataContextTemplate.tt is:
/// mappingSource as a parameter, this generator simply calls the matching base
/// constructor.
///
///
Hwat I deed wrong?

[…] file and push it to both C# modals, and update the database.  The best I found at the time was the T4 Toolbox that allowed me to better customize the outputs from MSLinqToSqlGenerator.  This was great, and […]