« How to clean your Endless Pool filter | Main | I met Steve Marx and Ryan Dunn, two stars from the Windows Azure Cloud! »

Microsoft Entity Framework 4: extending your DDL generation by adding indexes on Scalar Properties

In the last few days I have been learning how to use the Microsoft Entity Framework 4.0, LINQ to SQL, Entity SQl, and more.  Specifically, I started working on a brand new project, so I was able to use the “Model First” approach.

The Entity Data Model consists of the CSDL, the MSL,
the SSDL, and the generated DDL (SQL Scripts)

As I started working with the .EDMX file in Visual Studio I started researching articles and books that could help me out – and found this great article:

Model-First in the Entity Framework 4, by Jon Fancey, July 2010

Summary: In this paper we'll look at the new Entity Framework 4 that ships with .NET Framework 4 and Visual Studio 2010. I'll discuss how you can approach it's usage from a model-first perspective with the premise that you can drive database design from a model and build both your database as well as your data access layer declaratively from this model. The model contains the description of your data represented as entities and relationships providing a powerful approach to working with ADO.NET, creating a separation of concerns through an abstraction between a model definition and its implementation.

The author has an example on how to extend the DDL generation – allowing you to define indexes on Scalar Properties in your model. Unfortunately, the given example did not work - so after I found the issue and got it working, I wrote up this tutorial. Follow the instructions below to add extended properties to your model (CSDL) so when you generate your .SQL script (DDL) you'll create indexes on specific scalar properties you've defined.

See below for the tutorial and links I found useful while learning how to use the Entity Framework.  I have been using .netTiers (http://nettiers.com/), an awesome ORM set if tenplates for CodeSmith (http://codesmithtools.com/) for a few years, but now that Microsoft has its own ORM (the Entity Framework) and its data strategy is clearly set on EF, it’s a good idea to learn it and use it.

Microsoft Entity Framework 4: extending your DDL
generation by adding indexes on Scalar Properties

In this example, I created a model of a flight reservation database (following Jon Fancey’s steps in this article).  Let's suppose I created the following Reservation.edmx model in VS 2010 using EF4:


EF will automatically create the indexes on the primary keys - it also takes care of foreign keys when you create Associations between your Entities. But let's suppose I know I will generate a custom stored procedure that generates a report based on customer's seats -


For example we may want to know how many folks sat in first class in the last month - so the WHERE clause in our SQL query might be something like "WHERE Seat = '1A' OR Seat = '1B'" (on my model, these are very small planes, with only two first class seats).

Here's how to add a custom property to your CSDL that will automatically generate an index on the Seat property of your Customer entity -

1. Add an extension to your entity

Right click on your .EDMX file and choose "Open With…"


Choose the 'XML (Text) Editor' and choose OK -


If you have the model (*.EDMX file) open in the designer, you may get this warning -


Click 'Yes', and you should see the XML. Scroll down to the CSDL and find entity you'd like to add an index to - in our case the Customer Entity -


Add the following extension to the Seat scalar property -

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

So the Customer entity now looks like this -


Save your *.EDMX file.

2.  Edit your T4 template to create the index based on the extension

In this step, we'll edit the T4 template (.tt) to scan the CSDL for our custom properties - this will allow us to create the indexes when generating the SQL script. Pre-requesite: Download and install the “tangible T4 Editor” using the Extension Manager, helps with editing T4 templates.


Make a copy of "Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen\SSDLToSQL10.tt” to your local project.

(or if you are running a 32 bit version of windows you may find it in "Program Files\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen\SSDLToSQL10.tt”)

In my example, I copied it to my project under ddl\DDLGenerator.tt -


Double-click on your DDLGenerator.tt file, and add the following assembly references at the top -

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

So the top of your .tt file should look like this -


Now we'll basically loop through the properties looking for the extended properties to create our indexes. At the bottom of the file, right before 'Script has ended', add the following code -

-- --------------------------------------------------
-- 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;
  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#>]


So your file should look like this -


Now save your changes (CTRL-S) - if you get this warning when saving your changes, click 'OK' -


Generate the SQL Scripts

Now right-click on your .EDMX file and choose "Generate Database from Model..."


If you scroll down to the bottom of the generated .SQL script, you'll see an INDEX on the 'Seat' property is being created -


This is awesome! We can now extend our SQL script generation to whatever we want - automatically creating indexes on certain scalar properties or anything else we can dream of.

Good times!

Entity Framework Links

As I researched and played around with the Entity Framework I stumbled upon some great resources.  Here are the links I saved while doing my first baby steps with EF.

Model First in Entity Framework

Theoretical Overview: Model-First in the Entity Framework 4

Entity Framework FAQ

Forums: ADO.NET Entity Framework and LINQ to Entities

Entity Framework Design

Screencasts from Julie Lerman: Guide to the ADO.NET Entity Framework

Important: Consuming an Entity Data Model from a Separate .NET Project

1. Data project: Metadata Artifact processing = copy to output directory
2. Consumer project
  a. Add reference + Add config + connection string.
  b. Add reference to System.Data.Entity

ADO.NET Entity Framework in .NET 4

Entity Data Model Associations: Where's my Foreign Key?

Foreign Keys in the Entity Framework

Entity Designer Database Generation Power Pack

Entity SQL Overview


Download the ADO.NET C# POCO Entity Generator
using the Extension Manager in VS2010 by searching the Online Gallery


Entity Framework 4.0 – How To POCO

Walkthrough: POCO Template for the Entity Framework

Visual Studio Productivity Power Tools


| More



This page contains a single entry from the blog posted on July 31, 2010 9:51 AM.

The previous post in this blog was How to clean your Endless Pool filter.

The next post in this blog is I met Steve Marx and Ryan Dunn, two stars from the Windows Azure Cloud!.

Many more can be found on the main index page or by looking through the archives.

Powered by
Movable Type 3.35