« 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.

image 
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
http://msdn.microsoft.com/en-us/library/ff830362

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:

image

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 -

image

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…"

image

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

image

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

image

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 -

image

Add the following extension to the Seat scalar property -

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

So the Customer entity now looks like this -

image

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.

image

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 -

image

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 -

image

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#>]
([<#=indexName#>]);
<#
    }
  }
}

#>

So your file should look like this -

image

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

image

Generate the SQL Scripts

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

image

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

image

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
http://msdn.microsoft.com/en-us/library/ff830362

Entity Framework FAQ
http://blogs.msdn.com/b/dsimmons/archive/2007/11/08/entity-framework-faq.aspx

Forums: ADO.NET Entity Framework and LINQ to Entities
http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/threads

Entity Framework Design
http://blogs.msdn.com/b/efdesign

Screencasts from Julie Lerman: Guide to the ADO.NET Entity Framework
http://msdn.microsoft.com/en-us/data/ee712907.aspx

Important: Consuming an Entity Data Model from a Separate .NET Project
http://msdn.microsoft.com/en-us/data/ff628208.aspx

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
http://msdn.microsoft.com/en-us/data/aa937723.aspx

Entity Data Model Associations: Where's my Foreign Key?
http://thedatafarm.com/blog/data-access/entity-data-model-associations-where-s-my-foreign-key/

Foreign Keys in the Entity Framework
http://blogs.msdn.com/b/efdesign/archive/2009/03/16/foreign-keys-in-the-entity-framework.aspx

Entity Designer Database Generation Power Pack
http://visualstudiogallery.msdn.microsoft.com/en-us/df3541c3-d833-4b65-b942-989e7ec74c87

Entity SQL Overview
http://msdn.microsoft.com/en-us/library/bb387145.aspx

POCO

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

image

Entity Framework 4.0 – How To POCO
http://devtalk.dk/2009/05/22/Entity+Framework+40+How+To+POCO.aspx

Walkthrough: POCO Template for the Entity Framework
http://blogs.msdn.com/b/adonet/archive/2010/01/25/walkthrough-poco-template-for-the-entity-framework.aspx

Visual Studio Productivity Power Tools

http://go.microsoft.com/fwlink/?LinkId=189201
http://visualstudiogallery.msdn.microsoft.com/en-us/d0d33361-18e2-46c0-8ff2-4adea1e34fef


| More



Comments

About

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