Post date: Feb 18, 2011 7:34:28 AM
How to add nonclustered indexes from the Entity Framwork edmx.
How to add unique constraints from the Entity Framework edmx. Unique constraints are really the same thing as an Unique nonclustered index, so make sure you do not mix the both.
If you generate the database from Entity Framwork the default generator works fine for generating tables and foreign keys but there are several features missing. You can add any other missing feature with a bit of modification of the T4 template and some XML editing of your edmx file.
Why generate the database from the model?
In early stages of development using agile methods you may find yourself adding and removing tables and fields in the database as you complete user stories. If you generate the database from the model it may help minimizing differences in the model vs the database. Also, each member of the team can use a local database and work on their part of the model independent of the other team members. When the model is commited(checked in) the model will be merged and a new database can be created containing all the team members changes.
Read more http://msdn.microsoft.com/en-us/library/ff830362
http://blog.ehuna.org/2010/07/microsoft_entity_framework_4_e.html
Note: You will need to already have a model to use this example.
First open the edmx in XML editor and locate the <edmx:ConceptualModel> element.
In the <Schema> element add this attribute xmlns:myExtensions="http://www.microsoft.com/userExtensions"
This namespace will be used to identify our customizations of the edmx when we work on the T4 generator in a later.
The result should look like this.
<edmx:ConceptualModels>
<Schema Namespace="CustomerInvoicingSystemModel" Alias="Self"
xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation"
xmlns="http://schemas.microsoft.com/ado/2008/09/edm"
xmlns:myExtensions="http://www.microsoft.com/userExtensions"
>
We can now add some fun customizations to the conceptual model. Any customization we do here will need to be handled in the T4 template later, else they will make no change of the generated sql script.
Find one of the <EntityType> elements where you would like to add an index.
Open up any of the <Property> element and insert an <myExtensions:Index indexName="INDEX_NAME" edmx:CopyToSSDL="true"/> element on fields that you would like to create an index on.
The IndexName attribute is what we would like to name our index. This attribute is 100% custom and you could add several other custom attributes if you have the need. For example, you might want to be able to say that this should be an unique index or that the index should be sorted Descending.
The edmx:CopyToSSDL attribute is required for the T4 generator. Can't remember why...
Add <myExtensions:Unique UniqueGroupName="UNIQUE_NAME" edmx:CopyToSSDL="true"/> on any <Property> that you would like to have a unique constraint on.
In my T4 template i will handle the UniqueGroupName so that every <Property> that has the same UniqueGroupName will be part of the Unique key. I will do the same with Indexes so that each <Property> with the same indexName will be part of the key for that index.
The result may look like this.
<EntityType Name="Employee">
<Key>
<PropertyRef Name="Id" />
</Key>
<Property Name="Id" Type="Int32" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
<Property Name="SSN" Type="String" Nullable="false" Unicode="false" FixedLength="false" MaxLength="22" >
<myExtensions:Index indexName="SSN" edmx:CopyToSSDL="true"/>
</Property>
<Property Name="FirstName" Type="String" Nullable="false" MaxLength="100" Unicode="false" FixedLength="false" >
<myExtensions:Unique UniqueGroupName="PersonNrFtgId" edmx:CopyToSSDL="true"/>
</Property>
<Property Name="LastName" Type="String" Nullable="false" MaxLength="100" Unicode="false" FixedLength="false" >
<myExtensions:Unique UniqueGroupName="PersonNrFtgId" edmx:CopyToSSDL="true"/>
</Property>
</EntityType>
The Edmx is now prepared and we can start working on the T4 template
The default T4 template is located in C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen.
named SSDLToSQL10.tt
Copy SSDLToSQL10.tt to the same folder and give it a fancy name like SSDLToSQL10 Customized.tt
At the bottom add these snippets
-- --------------------------------------------------
-- Creating INDEXES based on custom properties
-- --------------------------------------------------
-- Creating index for table based on custom extensions --
<#
foreach (EntitySet entitySet in Store.GetAllEntitySets())
{
string tableName = Id(entitySet.GetTableName());
string schemaName = Id(entitySet.GetSchemaName());
IList<EdmProperty> props = entitySet.ElementType.Properties;
Dictionary<string, string> dic = new Dictionary<string, string>();
foreach (EdmProperty ep in props.Where(p => p.TypeUsage.EdmType is PrimitiveType))
{
MetadataProperty meta = ep.MetadataProperties.FirstOrDefault(mp => mp.Name == "http://www.microsoft.com/userExtensions:Index");
if (meta != null)
{
System.Xml.Linq.XElement e = meta.Value as System.Xml.Linq.XElement;
System.Xml.Linq.XAttribute attr = e.Attributes().FirstOrDefault(a => a.Name == "indexName");
if(attr != null)
{
string indexName = attr.Value;
if (dic.ContainsKey(indexName))
dic[indexName] = string.Format("{0}, [{1}]", dic[indexName], ep.Name);
else
dic.Add(indexName, string.Format("[{0}]", ep.Name));
}
}
}
foreach (var item in dic.Keys)
{
#>
CREATE INDEX [IX_<#=item#>]
ON <#if (!IsSQLCE) {#>[<#=schemaName#>].<#}#>[<#=tableName#>] (<#= dic[item] #>);
<#
}
}
#>
-- --------------------------------------------------
-- Creating UNIQUE constraints based on custom properties
-- --------------------------------------------------
-- Creating UNIQUE constraints for fields based on custom extensions --
<#
foreach (EntitySet entitySet in Store.GetAllEntitySets())
{
string tableName = Id(entitySet.GetTableName());
string schemaName = Id(entitySet.GetSchemaName());
IList<EdmProperty> props = entitySet.ElementType.Properties;
Dictionary<string, string> dic = new Dictionary<string, string>();
foreach (EdmProperty ep in props.Where(p => p.TypeUsage.EdmType is PrimitiveType))
{
MetadataProperty meta = ep.MetadataProperties.FirstOrDefault(mp => mp.Name == "http://www.microsoft.com/userExtensions:Unique");
if (meta != null)
{
System.Xml.Linq.XElement e = meta.Value as System.Xml.Linq.XElement;
System.Xml.Linq.XAttribute attr = e.Attributes().FirstOrDefault(a => a.Name == "UniqueGroupName");
if(attr != null)
{
string groupName = attr.Value;
if (dic.ContainsKey(groupName))
dic[groupName] = string.Format("{0}, [{1}]", dic[groupName], ep.Name);
else
dic.Add(groupName, string.Format("[{0}]", ep.Name));
}
}
}
foreach (var item in dic.Keys)
{
#>
ALTER TABLE <#if (!IsSQLCE) {#>[<#=schemaName#>].<#}#>[<#=tableName#>] ADD UNIQUE(<#= dic[item] #>)
<#
}
}
#>
These 2 snippets do more or less the same thing. They collect information about what columns to include as keys for each Unique and Index and then genereate SQL code.
You can see that the indexName configured in the edmx will be prefixed with IX_.
Finally:
Open your Edmx in the regular Entity Framework editor. Click on the surface and look at the properties window (F4 to show).
Change the DDL Generation Template to your newly created T4 file. If it not visible in the dropdown list, make sure that you saved it in the same location as the default template. You may need to restart visual studio.
That's it.
Right click on the edmx-surface and select Generated Database from model and look at the generated code for Unique and Indexes at the bottom.