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
http://msdn.microsoft.com/en-us/library/ff830362Summary: 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#>]
([<#=indexName#>]);
<#
}
}
}#>
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
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
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
Comments