T4 Toolbox: LINQ to SQL schema generator


LINQ to SQL DataContext class provides CreateDatabase method, which can create an empty database based on the entity model. You can create a new entity model using the built-in O/R Designer; generate the application code and create initial version of the database when the application is deployed for the first time. However, once the database is populated with live data, it can no longer be changed by simply re-creating the entire database. Instead, ALTER scripts have to be used to modify the schema and preserve the existing data. In other words, built-in tools allow you to use model-first or model-driven approach with LINQ to SQL only while developing initial version of the database application. The built-in tools don’t support an ongoing model-first development.

This limitation of the built-in LINQ to SQL functionality is one of the reasons developers start using a second tool, such as SQL Server Database Diagrams, Visio for Enterprise Architects or Enterprise Architect by Sparx Systems, and designing the database schema separately from the application model. While this approach works, it significantly increases complexity of the development process. Every change now needs to be made in two places - the database model and the application model. In all but trivial cases, making these changes by hand is too tedious and error-prone to be practical. You are better off choosing one model and somehow synchronizing it with the other. Today, developers typically choose database model as the source of truth and update the entity model based on it, or in other words, use database-driven or database-first approach.

LINQ to SQL includes two tools that allow you to generate .dbml from an existing database - O/R Designer and SqlMetal.exe . Unfortunately, both of these tools overwrite existing dbml and don’t allow you to preserve customizations made in the raw relational model imported from the database. SqlMetal also allows you to generate application code from a database directly. By choosing these tools, you are limiting your LINQ to SQL model to a direct representation of your relational database schema and eliminate the possibility of taking advantage of the object-oriented features the framework supports, such as inheritance of entity classes, member access and virtual member modifiers. Alternatively, you can use these tools to create the initial version of the entity model you will customize later, however that brings you back to the issue of maintaining two models separately.

There are third-party tools that make ongoing database-driven development of LINQ to SQL applications more practical. In particular, PLINQO, a set of code generation templates developed by CodeSmith, includes a sophisticated model synchronization logic, which updates .dbml based on database schema while attempting to preserve the customizations. However, these tools don’t eliminate the fundamental requirement of maintaining the two models separately. By itself, this requirement is not a problem. On a large-scale project with a complex database schema, this may well be a necessity. However, on a small- to medium-scale project, having to maintain the two models separately is often an additional, unnecessary burden.

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 to generate both application code, such as entity and data context classes, and database code, such as table, primary and foreign key scripts, from a single LINQ to SQL dbml file in your solution. Generated database scripts can be automatically added to a VSTS Database project, which will automatically handle majority of database deployment issues by automatically generating the ALTER scripts. In other words, combined with VSTS Database edition, T4 Toolbox  allows you to use model-driven or model-first approach for ongoing development of LINQ to SQL applications.

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

Add new LINQ to SQL model

  • 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 (OrderEntryDataContext will be generated for OrderEntry.dbml). Click the Add button when finished.

Just like with the previous version of this template, you will see that two files, a .dbml and a .tt, were added to your project. This time, instead of populating the entity model by dragging and dropping tables from the Server Explorer to the O/R Designer, take a couple of minutes to create the model from scratch and take advantage of some of the LINQ to SQL features like inheritance and associations. There is no need to enter mapping information, such as table or column names and database types. The generator will automatically deduce them from the type names, property names and CLR types respectively.

LINQ to SQL O/R designer

Alternatively, you can download the sample source code accompanying this article. The sample includes an entity model of a simplistic Order Entry application shown above. This model showcases some of the features supported by the code generator, including auto-generated properties (Contact.Id), version properties (Contact.RowVersion), derived types (Employee, Customer, Vendor) , associations (Contact_Employee) and associations where a derived type serves as a parent (Customer_Order).

  • Add a new VSTS database project to the solution.

Add new database project

Make sure to select an appropriate project type in the Database Projects  folder of the Add New Project dialog. Legacy database project template, located under Other Project Types -> Database will not work. If you don’t have Database edition of VSTS installed, you can use any C# project type instead.

  • In the previously created .tt file (OrderEntry.tt in this example), modify the code to assign DatabaseProject property of the LinqToSqlGenerator instance.
<#@ template language="C#v3.5" hostspecific="True" debug="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 = "OrderEntry.dbml";
    generator.DatabaseProject = @"..\Database\Database.dbproj";
    generator.Run();
#>

Generated Database Code DatabaseProject value is a path to the database project file, relative to the location of the .tt file. When you save the .tt file, it will generate table, primary and foreign key scripts in this project. At this point, you can build the VSTS Database project and deploy the OrderEntry database to a SQL server. Detailed discussion of the VSTS Database project functionality is outside of scope this article, suffice it to say that this should require only right-clicking the database project in Solution Explorer and selecting Deploy from the context menu.

As you continue using O/R designer to add new properties and types to the LINQ to SQL model, simply right-click the OrderEntry.tt file in the Solution Explorer and select Run Custom Tool to regenerate the database code, build the database project, deploy and let VSTS automatically apply ALTER scripts to the existing database.

Feature Highlights

Let’s review some the features supported by this code generator.

CREATE TABLE dbo.Contact
(
    Id INT NOT NULL IDENTITY,
    Type tinyint,
    RowVersion ROWVERSION,
    FirstName NVARCHAR(4000),
    LastName NVARCHAR(4000),
    StreetAddress NVARCHAR(4000),
    City NVARCHAR(4000),
    State NVARCHAR(4000),
    Zip NVARCHAR(4000),
    Phone NVARCHAR(4000),
    Email NVARCHAR(4000),
    SocialSecurityNumber NVARCHAR(4000),
    EmergencyContactId INT,
    AccountNumber NVARCHAR(4000),
    CreditCardNumber NVARCHAR(4000),
    CreditCardExpiration NVARCHAR(4000)
);
Derived types mapped to base table

The generator automatically generates columns for derived types, such as Employee, in the table of their base type, such as Contact. In particular, SocialSecurityNumber is a property of the Employee type, stored in the Contact table.

IDENTITY columns

The generator automatically maps integer properties with Auto Generated Value attribute set to true, such as Id,  to IDENTITY columns.

ROWVERSION data type

The generator automatically maps byte[] and Binary properties with Time Stamp attribute set to true, such as RowVersion, to the ROWVERSION data type.

Enum columns

The generator automatically maps enum properties, such as Type property of the Contact class, to SQL data type INT. You can also specify the server data type explicitly. In the application, the property will be generated with the original enum type.

private ContactType type;

[Column(Name = "Type", /* … */ DbType = "tinyint")]
public ContactType Type { /* … */ }

This allows you to write strongly-typed queries like so:

var dc = new OrderEntryDataContext(Settings.Default.Database);
var employees = from c in dc.Contacts
                where c.Type == ContactType.Employee
                select c;

Primary keys
ALTER TABLE dbo.Contact
    ADD CONSTRAINT PK_Contact
    PRIMARY KEY CLUSTERED (Id);

The generator automatically produces a primary key script for properties with Primary Key attribute set to true.

Delimited Identifiers
CREATE TABLE dbo.[Order]
(
    Id INT NOT NULL IDENTITY,
    OrderDate DATETIME NOT NULL,
    EmployeeId INT NOT NULL,
    ProductId INT NOT NULL,
    RowVersion ROWVERSION,
    CustomerId INT NOT NULL,
    Price DECIMAL(29,4) NOT NULL,
    PONumber NVARCHAR(4000)
);

The generator automatically uses delimited identifiers to avoid errors when original identifier is a reserved SQL keyword, such as Order, or contains spaces, such as Order  Details. Otherwise, the generator uses the original, un-delimited identifiers to improve readability.

Foreign Keys
ALTER TABLE dbo.[Order]
    ADD CONSTRAINT FK_Customer_Order
    FOREIGN KEY (CustomerId)
    REFERENCES dbo.Contact (Id);

The generator automatically produces foreign key scripts for associations. Note that in case of Customer_Order association, the parent type Customer is actually derived from Contact. The foreign key correctly references the base table, however the generated association property (shown below), is of the derived type, Customer.

[Association(Name = "Customer_Order", /* … */ IsForeignKey = true)]
public Customer Customer { /* … */ }

Sample Source Code

Sample source code, available for download below, includes a solution with three projects - Model, Database and Application. Model is a C# class library project that contains OrderEntry.dbml (LINQ to SQL model) and OrderEntry.tt (code generator). Entity and Data Context classes are generated in the Model project, while SQL scripts are generated in the Database project. Note that this project requires VSTS Database edition. Application is a C# console application that creates a sample database on the local SQL Server instance, populates it with sample data and executes several queries.

Closing Thoughts

The new version of LINQ to SQL generator in T4 Toolbox allows you to use model-first approach for ongoing developing database applications.

So what’s the big deal? Simplicity.

  • There is no need to use a separate modeling tool when developing a database application. You can generate both database and application code without leaving Visual Studio IDE.
  • There is only one diagram to maintain, which reflects both entity and database models. If you are editing both at the same time, there is no way you can forget update one of them after updating the other.
  • There is only one step in the code generation process - Run Custom Tool from Solution Explorer. You don’t need to remember to make the changes in the modeling tool, export the schema, build the schema, synchronize the .dbml file with the schema and finally generate the application code from .dbml.

What’s the catch then? Limited scalability and flexibility.

  • O/R designer is limited to a single diagram, which puts practical limitations on the number of tables you can manage with this approach.
  • What you can generate is limited by what you can model in the O/R designer - tables, primary keys and foreign keys. (INSERT, UPDATE and DELETE procedures can also be generated, this is under development now).
  • Entity and database models are closely related and cannot be developed separately. If you have an existing database, you may not be able to use this approach.

Will it work for you? It depends.

This approach will work best on greenfield projects with a small- to medium-size database and teams who prefer implementing business logic in application code, not stored procedures and triggers. You can certainly optimize time-critical logic by selectively implementing it stored procedures and exposing them in the LINQ to SQL model. However, you will be swimming against the current if you want to rely on the stored procedures extensively. On brownfield projects, this approach will work as well if you have a database of appropriate size and can limit database modifications to the O/R Designer. This approach will not work well if you have a database with hundreds of tables and/or multiple database developers actively creating tables and stored procedures.

Download

Write a Comment

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

Reader Comments

T4 Toolbox: LINQ to SQL schema generator…

DotNetBurner - burning hot .net content…

Awesome work - congratulations!

Just one note why dataContext.CreateDatabase() is not enough. Oleg did this bunch of work as often just generation the DB only with the LINQ to SQL runtime is not enough. Often you need to add additional value restrictions or indexes. So it is much better to have bunch of seperate SQL files as shown here.
This is awesome!
Thanks Oleg.

[…] Oleg show how to use T4 in order to improve the model-driven approach for LINQ to SQL applications. […]

[…] T4 Toolbox: LINQ to SQL schema generator […]

[…] since that was written, the team then introduced in May 2009 the “LINQ to SQL Schema” available right now. This is great for those who have always wanted to be able to develop from a “model up” […]

Hi there… I’ve googled for it but I bet it’s something really simple I’m missing here. I’ve built the diagram normally but when I save it, although it generates the database as expected, the toolbox can’t create the constraints and the compiler keeps giving me SQL errors 1776 (TSD4001: There are no primary or candidate keys in the referenced table ‘dbo.CategoriaSegmento’ that match the referencing column list in the foreign key ‘FK_CategoriaSegmento_Segmento’.) and 1750 (TSD4001: Could not create constraint.)

I’ve searched for it but my guess is that I’m such a newbie that I can’t realize a simple mistake!

Appreciate any help. thanks.

Marcos,

Check the primary key in the Segmento table and make sure that foreign key FK_CategoriaSegmento_Segmento references it correctly. I can’t say anything else without looking at the source code. If you can’t figure it out, please post the question with the source code on CodePlex.

Oleg

I am new to T4. What do I need to do to get in non pluralized table names? Like
public Table PRRun
instead of
public Table PRRuns ?

Thanks

Jacobus,

You can change the class name in the LINQ to SQL designer and have a class called PRRun mapped to a table called PRRuns.

Oleg

Hi Oleg,

i run into trouble when trying to map stored procedures to a method of the context. I think the code generation for that is missing?

That’s correct. I didn’t have a chance to put it in yet.