The information in this post is out of date.

Visit msdn.com/data/ef for the latest information on current and past releases of EF.

For Model First see http://msdn.com/data/jj205424


 

In a previous post, we walked through the designer’s “out of the box” database schema generation experience. In this post, we show how some new designer features in VS2010 Beta 2 integrate with this capability, then we pop the hood and show how easy it is to replace or extend parts of the generation system.

We will begin with a simple model that contains two newly supported constructs: Employee.EmployerId is a foreign key that references Company.Id, and Person.Address is a complex type.

Untitled

To show the details of the model, we also include the EDM’s XML:

 1: <Schema xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" Namespace="SimpleModel" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
 2:         <EntityContainer Name="SimpleModelContainer" annotation:LazyLoadingEnabled="true" >
 3:           <EntitySet Name="Persons" EntityType="SimpleModel.Person" />
 4:           <EntitySet Name="Companies" EntityType="SimpleModel.Company" />
 5:           <AssociationSet Name="CompanyEmployee" Association="SimpleModel.CompanyEmployee">
 6:             <End Role="Company" EntitySet="Companies" />
 7:             <End Role="Employee" EntitySet="Persons" />
 8:           </AssociationSet>
 9:         </EntityContainer>
 10:         <EntityType Name="Person">
 11:           <Key>
 12:             <PropertyRef Name="Id" />
 13:           </Key>
 14:           <Property Type="Int32" Name="Id" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
 15:           <Property Type="String" Name="FirstName" Nullable="false" />
 16:           <Property Type="String" Name="LastName" Nullable="false" />
 17:           <Property Name="Address" Type="SimpleModel.Address" Nullable="false" />
 18:         </EntityType>
 19:         <EntityType Name="Employee" BaseType="SimpleModel.Person">
 20:           <Property Type="String" Name="Title" Nullable="false" />
 21:           <NavigationProperty Name="Company" Relationship="SimpleModel.CompanyEmployee" FromRole="Employee" ToRole="Company" />
 22:           <Property Type="Int32" Name="EmployerId" Nullable="false" />
 23:         </EntityType>
 24:         <EntityType Name="Company">
 25:           <Key>
 26:             <PropertyRef Name="Id" />
 27:           </Key>
 28:           <Property Type="Int32" Name="Id" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
 29:           <Property Type="String" Name="Name" Nullable="false" />
 30:           <Property Type="String" Name="Address" Nullable="false" />
 31:           <NavigationProperty Name="Employees" Relationship="SimpleModel.CompanyEmployee" FromRole="Company" ToRole="Employee" />
 32:         </EntityType>
 33:         <ComplexType Name="Address" >
 34:           <Property Type="String" Name="Street" Nullable="false" Unicode="true" MaxLength="250" FixedLength="false" />
 35:           <Property Type="String" Name="City" Nullable="false" Unicode="true" />
 36:           <Property Type="String" Name="Zip" Nullable="false" FixedLength="true" MaxLength="5" Unicode="false" />
 37:           <Property Type="String" Name="State" Nullable="true" FixedLength="false" MaxLength="100" Unicode="true" />
 38:           <Property Type="String" Name="Country" Nullable="false" Unicode="false" />
 39:         </ComplexType>
 40:         <Association Name="CompanyEmployee">
 41:           <End Type="SimpleModel.Company" Role="Company" Multiplicity="1" />
 42:           <End Type="SimpleModel.Employee" Role="Employee" Multiplicity="*" />
 43:           <ReferentialConstraint>
 44:             <Principal Role="Company">
 45:               <PropertyRef Name="Id" />
 46:             </Principal>
 47:             <Dependent Role="Employee">
 48:               <PropertyRef Name="EmployerId" />
 49:             </Dependent>
 50:           </ReferentialConstraint>
 51:         </Association>
 52: </Schema>

Note the ReferentialConstraint element above, which defines the foreign key, and the ComplexType element that defines the Address type.

When we run the database generation script against this model, we get the following table definitions:

-- Creating table 'Persons'
CREATE TABLE [dbo].[Persons] (
[Id] int IDENTITY(1,1) NOT NULL,
[FirstName] nvarchar(max) NOT NULL,
[LastName] nvarchar(max) NOT NULL,
[Address_Street] nvarchar(250) NOT NULL,
[Address_City] nvarchar(max) NOT NULL,
[Address_Zip] char(5) NOT NULL,
[Address_State] nvarchar(100) NULL,
[Address_Country] varchar(max) NOT NULL
);
GO
-- Creating table 'Companies'
CREATE TABLE [dbo].[Companies] (
[Id] int IDENTITY(1,1) NOT NULL,
[Name] nvarchar(max) NOT NULL,
[Address] nvarchar(max) NOT NULL
);
GO
-- Creating table 'Persons_Employee'
CREATE TABLE [dbo].[Persons_Employee] (
[Title] nvarchar(max) NOT NULL,
[EmployerId] int NOT NULL,
[Id] int NOT NULL
);
GO

A few things to note:

  • The complex type was “rolled-up” into its owning type’s table, and the name of the complex property was appended to the name of the complex type properties, e.g. “Address_State”.
  • The foreign key property “EmployerId” was pulled through into the model, rather than a “hidden” foreign key being generated, as would have been the case for a relationship that did not specify a referential constraint. Another blog post will go into referential constraints in more detail.
  • The name of a table that stores instances of subtypes is composed of the name of the subtype’s set to which we append the name of the type, e.g. “Persons_Employee”.
  • We’ve set the Address.Zip property to be a fixed length non-unicode string, and so it is generated as a char(5).
  • If you do not specify unicode, the default is true. If you do not specify fixed length, the default is false. If you do not specify max length, the default is “max”.
  • Property Persons.Id’s StoreGeneratedPattern property is set to “Identity”, which makes it an IDENTITY(1,1) in the database.
  • Not shown in the above script are the key and constraint definitions, which are similar to those shown in the previous post.

Now, let’s take a look at how model first actually works, so we can see how to change what it does. Click on any empty area in the designer surface to see the top-level properties of the model:

Untitled

Of interest to us are two of the three “Database Script Generation” properties:

Database Generation Workflow – Controls the overall process by which the conceptual model is translated into a database script. The default workflow is “TablePerTypeStrategy.xaml”.

DDL Generation Template – Is called by the default database generation workflow to transform the generated database model to DDL. More on this below.

First, let’s take a look at TablePerTypeStrategy.xaml – it is located in Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen. If we open this file in Visual Studio, we see that it is a Workflow Foundation (WF) workflow:

Untitled

The first activity, “CsdlToSsdlAndMslActivity” generates the store model (SSDL) for the EDM, and then generates the mappings (MSL) that connect the two. It has the following properties:

Untitled

The “MslOutputGeneratorType” specifies a class that generates MSL based on a table-per-type mapping strategy. The “OuputGeneratorType” generates SSDL. Rather than replacing this activity and all of the plumbing it provides, you can replace these two classes with your own if you want to change the mapping strategy or otherwise alter the system. In future posts, we will provide examples and more details on the specifics of these classes. You can also replace this entire activity with your own activity that fills the MslOutput and SsdlOutput parameters using any mechanism you wish.

The second activity, “SsdlToDdlActivity” generates DDL from the store model, and it does this by using a T4 template, which makes for very easy customization. In the same folder that TablePerTypeStrategy.xaml is installed, there is also SSDLToSQL10.tt which generates TSQL DDL. Templates for supporting other database platforms will become available in the future from Entity Framework provider writers. T4 templates are simple text files whose intent is to transform some sets of inputs, in this case, SSDL, to some output, DDL in this case. A full description of the template is outside the scope of this post, but we’ll take a brief look at one section of the template, the one that generates the table definitions. To help with readability, we have bolded those parts of the script that generate DDL:

 

 

 


-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

<#
foreach (EntitySet entitySet in Store.GetAllEntitySets())
{
string schemaName = Id(entitySet.GetSchemaName());
string tableName = Id(entitySet.GetTableName());
#>
-- Creating table '<#=tableName#>'
CREATE TABLE <# if (!IsSQLCE) {#>[<#=schemaName#>].<#}#>[<#=tableName#>] (
<#
for (int p = 0; p < entitySet.ElementType.Properties.Count; p++)
{
EdmProperty prop = entitySet.ElementType.Properties[p];
#>
[<#=Id(prop.Name)#>] <#=prop.ToStoreType()#>
<#=WriteIdentity(prop, targetVersion)#> <#=WriteNullable(prop.Nullable)#>
<#=(p < entitySet.ElementType.Properties.Count - 1) ? "," : ""#>
<#
}
#>
);
GO
<#
}
#>

As you can see, the template iterates over the objects in the SSDL and the translation is fairly straightforward.

The experience when additional templates and workflows are installed is tremendously improved relative to Visual Studio 2010 Beta1. To demonstrate this, copy and paste TablePerTypeStrategy.xaml and rename it to CustomStrategy.xaml. Copy and paste SSDLToSQL10.tt and rename it SSDLToCustomDDL.tt. When we switch back to the designer and look at the designer property sheet, we will see these new files appear in the drop downs for the workflow and template properties:

Untitled

And:

Untitled

So now you can mix and match two different strategies with two different DDL generators, for example, you could mix and match a table-per-hierarchy strategy with either a SQL or a Firebird database schema template.

But there is a problem: Installing templates and workflows in the Visual Studio path is often neither advisable nor possible. So we’ve provided two additional install locations. The first is %localappdata%\Microsoft\[AppId]\10.0\Extensions\Microsoft\Entity Framework Tools\DBGen where [AppId] depends on your Visual Studio SKU, for example:

D:\documents and settings\sqlcl02\Local Settings\Application Data\Microsoft\VisualStudio\10.0\Extensions\Microsoft\Entity Framework Tools\DBGen.

Copy the same files to this folder and rename them to CustomUserStrategy.xaml and SSDLToCustomUserDDL.tt and they will immediately show up:

Untitled

 

 

Untitled

Finally, if you want a custom workflow or template specifically for your project, copy the template or workflow to your project and type in the path. For example:

Untitled

Make sure that the (VS) or (User) postfixes are not in the path.

In summary, it is very easy now to mix and much strategies and target database DDL formats, as well as to install your own custom workflows and templates. In a future post, we will walk through examples of custom strategies and templates.

Noam Ben-Ami
Program Manager,
Entity Designer