Entity Framework Code Generation with Indexes

Is there a way to tell .NET 4 ADO.NET Entity's SQL Generator to create indexes for a s开发者_高级运维pecific column?

Indices are not supported natively, but if you head to the "Influencing the DDL Generation" section of this article you can see how to add this custom functionality to an existing template.

In the article's example, the new index in your EDMX's CSDL would look something like this:

<Property ... >
  <myExtensions:Index indexName="Seat" edmx:CopyToSSDL="true"/>
</Property>

But to get this working you would have to modify a few things (see the link I provided for the details). Firstly, you'd have to declare that "myExtensions" namespace on the schema node:

<!-- CSDL content -->
<edmx:ConceptualModels>
  <Schema [...] xmlns:myExtensions="http://www.microsoft.com/userExtensions">
  [...]
</edmx>

Secondly, you'd have to modify the template found at:

\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen\SSDLToSQL10.tt

The solution requires Linq, so add this to the top of the template:

<#@ assembly name="System.Xml.Linq" #>

And then add this to the bottom:

-- Creating index for table based on custom extensions --
<#
   foreach (EntitySet entitySet in Store.GetAllEntitySets())
   {
     string tableName = Id(entitySet.GetTableName());
     string schemaName = Id(entitySet.GetSchemaName());
     EdmProperties props = entitySet.ElementType.Properties;
     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");
            string indexName = attr.Value;     
            // create an index for specified column
#>
CREATE INDEX [IX_<#=indexName#>]
ON <#if (!IsSQLCE) {#>[<#=schemaName#>].<#}#>[<#=tableName#>]
([<#=indexName#>]);
<#
         }
     }   
   }
#>

Most of that could be pretty easily modified to fit your needs. The article goes into more of the details, but the most important line in the above code is the one which fetches that custom "Index" extension node:

MetadataProperty meta = ep.MetadataProperties.FirstOrDefault(mp => mp.Name == "http://www.microsoft.com/userExtensions:Index");

Hope that helps!

Complementing Smudge's answer, there are some tricks that need to be done for this to work in EF 5.

For instance, edmx:CopyToSSDL="true" doesn't work right off the bat. You have to do some hacks:

<Schema xmlns="http://schemas.microsoft.com/ado/2009/11/edm" xmlns:cg="http://schemas.microsoft.com/ado/2006/04/codegeneration"
              xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" Namespace="CPEData" Alias="Self"
              xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" annotation:UseStrongSpatialTypes="false"
              xmlns:myExtensions="http://www.microsoft.com/userExtensions"
              xmlns:edmxv2="http://schemas.microsoft.com/ado/2008/10/edmx" >

And then in the custom property (notice the edmxv2):

<myExtensions:Index edmxv2:CopyToSSDL="true" IndexName="Name" Columns="Name" >

See this link for more info.

Also, I did change some of the T4 code to make it more easy. I took this example as a working base to accomplish a more flexible custom elements syntax.

You could add a custom element at the end of the EntityType element for instance (you don't have to put it inside a <Property></Property> tag):

<myExtensions:Index edmxv2:CopyToSSDL="true" IndexName="Name" Columns="Name" >
    Custom metadata (not needed)
</myExtensions:Index>

And then modify the .tt template:

-- --------------------------------------------------
-- Creating all Indexes based on custom extensions   
-- --------------------------------------------------

<#
   foreach (EntitySet entitySet in Store.GetAllEntitySets())
   {
     string tableName = Id(entitySet.GetTableName());
     string schemaName = Id(entitySet.GetSchemaName());
     var props = entitySet.ElementType.MetadataProperties.Where(p => p.Name == "http://www.microsoft.com/userExtensions:Index");
     foreach (MetadataProperty meta in props)
     {
        System.Xml.Linq.XElement e = meta.Value as System.Xml.Linq.XElement;
        string indexName = e.Attributes().FirstOrDefault(a => a.Name == "IndexName").Value;
        string columnsName = e.Attributes().FirstOrDefault(a => a.Name == "Columns").Value;
        // create an index for specified column
#>
CREATE INDEX [IX_<#=indexName#>]
ON <#if (!IsSQLCE) {#>[<#=schemaName#>].<#}#>[<#=tableName#>]
([<#=columnsName#>]);
<#

     }   
   }
#>