Customize Entity Framework SSDL & SQL Generation

Comments | Share

In almost every talk I have done on Entity Framework I get questions on how to do custom SSDL or SQL when using model first development.  Quite a few of these questions have required custom changes to the SSDL, which of course can be a problem if it is getting auto generated.  Luckily, there is a tool that can help. 

In the Visual Studio Gallery on MSDN, there is the Entity Designer Database Generation Power Pack. You have the ability to select different generation strategies and it also allows you to inject custom T4 Templates into the generation workflow so that you can customize the SSDL and SQL generation. 

When you select to generate a database from a model the dialog is replaced by one with more options:

 

You can clone the individual workflow for either the current project or current machine.  The templates are installed at “C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen” on my local machine and you can make a copy of any template there.  If you clone the strategy and open it up, you will get the following workflow:



Each item in the sequence is defining the execution of a T4 template.  The XAML for the workflow is listed below so you can see where the T4 files are defined.  You can simply make a copy of an existing template and make what ever changes you need.
 

   1: <Activity x:Class="GenerateDatabaseScriptWorkflow" ... >

   2:   <x:Members>

   3:     <x:Property Name="Csdl" Type="InArgument(sde:EdmItemCollection)" />

   4:     <x:Property Name="ExistingSsdl" Type="InArgument(s:String)" />

   5:     <x:Property Name="ExistingMsl" Type="InArgument(s:String)" />

   6:     <x:Property Name="Ssdl" Type="OutArgument(s:String)" />

   7:     <x:Property Name="Msl" Type="OutArgument(s:String)" />

   8:     <x:Property Name="Ddl" Type="OutArgument(s:String)" />

   9:     <x:Property Name="SmoSsdl" Type="OutArgument(ss:SsdlServer)" />

  10:   </x:Members>

  11:   <Sequence>

  12:     <dbtk:ProgressBarStartActivity />

  13:     <dbtk:CsdlToSsdlTemplateActivity SsdlOutput="[Ssdl]" TemplatePath="$(VSEFTools)\DBGen\CSDLToSSDL_TPT.tt" />

  14:     <dbtk:CsdlToMslTemplateActivity MslOutput="[Msl]" TemplatePath="$(VSEFTools)\DBGen\CSDLToMSL_TPT.tt" />

  15:     <ded:SsdlToDdlActivity ExistingSsdlInput="[ExistingSsdl]" SsdlInput="[Ssdl]" DdlOutput="[Ddl]" />

  16:     <dbtk:GenerateAlterSqlActivity DdlInputOutput="[Ddl]" DeployToScript="True" DeployToDatabase="False" />

  17:     <dbtk:ProgressBarEndActivity ClosePopup="true" />

  18:   </Sequence>

  19: </Activity>

So as you can see, this tool enables you to make some pretty heavy customizations to how the SSDL and SQL get generated.  You can get more info and the tool can be downloaded from: http://visualstudiogallery.msdn.microsoft.com/en-us/df3541c3-d833-4b65-b942-989e7ec74c87.  There is a comments section on the site so make sure you let the team know what you like and what you don’t like.  Enjoy!