次の方法で共有

Automatically Generating LINQ To SQL Models From T-SQL

  • [アーティクル]

With LINQ to SQL, a couple of questions quickly arise:

  • Since you can create a data model directly in Visual Studio, where's your authoritative definition of the database?
  • If you generate a data model from your database, how do you maintain the model if you have to tweak it?

The first question is pretty easy to answer: Since T-SQL can express a lot more about your database than can a LINQ data model, T-SQL should be your authoritative definition. For example, you can define clustered and non-clustered indexes in T-SQL, but not in LINQ to SQL (as far as I know).

Since T-SQL should be your authoritative database definition, you can generate a LINQ to SQL data model from your database. Unfortunately, there's no tool that I'm aware of that you can point directly at a set of T-SQL scripts and have it generate a .dbml directly, so you have to have a SQL database your tool can use.

The next question that pops up, then, is how do you ensure that this particular database instance reflects your T-SQL script?

The answer to that question is to create the database just before you run your tool. If you've been following my posts on data access testing, you will know that I'm not in the habit of having specific databases on my machine - I have a database engine (SQL Server) on my machine, but no databases. When I run a test suite, it first creates a completely new test-specific, temporary database, runs all the tests, and finally deletes the database again.

When generating or updating the .dbml file, you can follow the same approach, and you can automate the whole thing in a script. The script should perform the following steps:

  • Create the database based on the authoritative T-SQL scripts.
  • Generate the data model using Sqlmetal.exe.
  • Modify the .dbml file if you need to customize it (such as changing generated property names, etc.)
  • Delete the database.

Using my HoneySqlAccess project as an example, let's have a look at how to accomplish this. I'll use a Visual Studio 2008 PowerShell to script this, but you can also do it in a Command prompt if you can find a command-line XSLT processor.

The first step is to create the database itself, complete with schema. Since I use an Installer for this, I do it with InstallUtil, but you could also execute a set of T-SQL scripts by using SqlCmd:

 InstallUtil /ConnectionString="Data Source=localhost;Initial Catalog=HoneyTemp;Integrated Security=True" bin\Debug\Ploeh.Samples.HoneySqlAccess.dll /LogFile=

The main thing to notice here is that I create a new database called HoneyTemp.

The next step is to create the LINQ to SQL data model. For that purpose, we can use a tool called Sqlmetal:

 SqlMetal /server:localhost /database:HoneyTemp /dbml:OriginalHoneyDB.dbml /namespace:Ploeh.Samples.HoneySqlAccess /pluralize

This creates the file OriginalHoneyDB.dmbl from the database HoneyTemp that was just created in the step before.

If you are inclined to accept the generated data model as is, you can just include the file in your project and go ahead and use it. When you add a .dbml file to Visual Studio 2008, it will automatically set it up with the MSLinqToSQLGenerator custom tool, which will cause it to autogenerate .NET code for you.

In many cases, however, you will want to tweak the model a bit (I'll get back to that later). Since a .dbml file is just an XML file, the most flexible approach is to apply an XSL transformation. Since this is PowerShell, I can just use .NET objects directly:

 $transformation = [System.IO.Path]::Combine((Get-Location).Path, "ModifyDataModel.xslt")

 $originalDbml = [System.IO.Path]::Combine((Get-Location).Path, "OriginalHoneyDB.dbml")

 $dbml = [System.IO.Path]::Combine((Get-Location).Path, "HoneyDB.dbml")

  

 $xslt = New-Object System.Xml.Xsl.XslCompiledTransform

 $xslt.Load($transformation)

 $xslt.Transform($originalDbml, $dbml)

This part of the script uses the ModifyDataModel.xslt transformation to create the HoneyDB.dbml file that I can then go ahead and include in my Visual Studio project.

Left is only a bit of clean-up:

 del $originalDbml

  

 InstallUtil /u /ConnectionString="Data Source=localhost;Initial Catalog=HoneyTemp;Integrated Security=True" bin\Debug\Ploeh.Samples.HoneySqlAccess.dll /LogFile=

The OriginalHoneyDB.dbml file is just for transitory use, so can be deleted. The last line is just my (Installer-based) way of removing the database from my system again (notice the /u switch to uninstall).

Most of this script just calls .NET SDK command-line utilities, or, if you want to use SqlCmd for executing T-SQL scripts, tools from the SQL Server Client Tools installation. The only part that isn't going to work in cmd.exe is the XSLT processing part, but if you can find a suitable command-line XSLT processor (there are several free ones available), you can mimic this script in a .bat file as well.

The last thing I want to show you is a couple of snippets from the XSL transformation.

Since the transitory database was called HoneyTemp, Sqlmetal creates a DataContext called HoneyTemp. That is obviously not an acceptable name, so it should be renamed to something better, like HoneyContext:

 <xsl:template match="dbml:Database">

   <xsl:copy>

     <xsl:copy-of select="@*" />

     <xsl:attribute name="Name">HoneyContext</xsl:attribute>

     <xsl:apply-templates select="node()" />

   </xsl:copy>

 </xsl:template>

Another modification I wanted to do was to rename Bee.ID to Bee.Id, since Code Analysis would otherwise complain about the naming of the ID property. On the other hand, I didn't want to change the column name in the database from ID to Id, so a mapping is in order. LINQ to SQL supports that scenario by adding a Member attribute to the appropriate Column element, so that's what's going on here:

 <xsl:template match="dbml:Database/dbml:Table/dbml:Type[@Name = 'Bee']/dbml:Column[@Name = 'ID']">

   <xsl:copy>

     <xsl:apply-templates select="@* | node()" />

     <xsl:attribute name="Member">Id</xsl:attribute>

   </xsl:copy>

 </xsl:template>

Although this recipe may seem complex, it's fully automated and sure beats having to do a series of manual steps. Whenever I need to change the database schema, I can just edit my T-SQL scripts and then run my PowerShell script to automatically update my LINQ to SQL data model. If I wish to do some customization of the model, I can add or edit those in the XSL transformation. Each schema item is defined in one, and only one, place.

Since the files involved in the example are a bit complex, I've attached a downloadable sample to this post. As always, this is only sample code and is provided as is, etc.

DacIntegrationTestLinqToSql.zip

Comments

  • Anonymous
    April 20, 2008
    PingBack from http://haylie.dishtvnews.com/accessdatabasetemplates.html

  • Anonymous
    September 30, 2008
    Just found this gem, but the xslt didnt work on my system, it gave me an error: "Attribute and namespace nodes cannot be added to the parent element after a text, comment, pi, or sub-element node has already been added." This solved by modifying the xslt transform, so that attributes are changed before the nodes are handled. Original xslt: <xsl:copy>  <xsl:apply-templates select="@* | node()" />    <xsl:attribute name="Member">Id</xsl:attribute>  </xsl:copy> Changed xslt: <xsl:copy>  <xsl:apply-templates select="@*" />    <xsl:attribute name="Member">Id</xsl:attribute>  <xsl:apply-templates select="node()" /> </xsl:copy>

  • Anonymous
    September 30, 2008
    Hi Peter Thanks for sharing :) BTW, now that the Entity Framework is out with .NET 3.5 SP1, all of this is not quite as relevant as it was before...

  • Anonymous
    January 21, 2010
    wow, excellent xslt! SqlMetal + this xslt + l2st4 = awesomeness! (http://l2st4.codeplex.com/)