Improve Performance with Entity Framework 5

The newly released Entity Framework 5 (EF5) brings developers a number of long-awaited features, which are now possible thanks to changes in .NET 4.5 and Visual Studio 2012. See "What's New in .NET 4.5 and Visual Studio 2012" and "My Favorite Visual Studio 2012 Features" for more information. EF5 brings us support for enums, table-valued functions, and spatial data types (geometry and geography) and also provides a huge performance improvement over the previous version. Within the Visual Studio 2012 IDE, the Entity Framework Designer has also gained some great new capabilities. EF5 incorporates all of the work distributed via NuGet starting with the EF 4.1 release: Code First, the DbContext API, and Code First Migrations. Let's take a closer look at each of the new features.

Performance Improvements

In my opinion, the most important change in EF5 is one that greatly improves query performance. If you've been using Entity Framework (or even LINQ to SQL), you might be familiar with precompiled queries. Precompiling queries alleviates the work of repeatedly transforming a LINQ to Entities query into the store SQL (i.e., into T-SQL when you're working with a SQL Server database). For example, you might have the following query in a repository that gets executed frequently:

context.Customers.Include(c=>c.Orders).Where(c=>c.CustomerId== cId).ToList();

The query takes cId, a variable that represents a customer's ID value, and returns a customer instance from the database along with that customer's orders. Your application might execute that query many times, each time passing in a different variable to represent the CustomerId. And each time, Entity Framework must transform the LINQ query into T-SQL.

Since the first version of Entity Framework, the CompiledQuery class has allowed you to precompile the query, then invoke the query whenever you wanted to execute it. This way, Entity Framework only had to figure out the SQL once (during precompilation) and then reuse that SQL every time the CompiledQuery is invoked, along with any parameters you might pass in.

However, there are two downsides to using CompiledQuery. The first is that you must explicitly precompile each query that you plan to use more than once -- which is a bit of drudgery. But the second downside is worse. There's no way to use CompiledQuery when you're using the DbContext API; CompiledQuery works only with ObjectContext. If you're using Code First, you're most likely using the DbContext API. And Microsoft recommends that you use the DbContext API in new projects even if you'll be working with Database First or Model First models. This means that in all three of these scenarios, you can't benefit from the great time savings of CompiledQuery. This became a show-stopper for many developers, who had to choose between performance (ObjectContext with CompiledQuery) versus a simpler-coding API (DbContext with no CompiledQuery).

Thankfully, with EF5 you no longer need to make that choice. And as a bonus, you don't have to explicitly create CompiledQuery objects in code to benefit from the precompilation.

EF5 brings us auto-compiled queries, which work very differently than CompiledQuery. Instead of your writing code to compile each query and then invoking each as needed, Entity Framework caches the generated SQL for you as a background process, then searches the cache for already compiled queries when you execute any query. Figure 1 shows a simplified view of the auto-compiled query process.

Entity Framework will cache the queries for the lifetime of the application process, using an algorithm for evicting items from the cache if it exceeds 800 items. Sadly for me, this means I can't show you one line of code to demonstrate how to make it work. It just happens in the background, and you don't need to touch a thing -- which is good.

What's better is that you don't even need to use EF5 or change your apps to target .NET 4.5 to get this benefit. As long as your end users have .NET 4.5 running on their machines, Entity Framework will benefit from this great performance improvement. The ADO.NET blog has an excellent post about this -- "Sneak Preview: Entity Framework 5.0 Performance Improvements" -- showing the results of numerous benchmark tests.

Other Performance Improvements

EF5 incorporates other behind-the-scenes improvements to query generation and command execution. In the previously referenced blog post, the Entity Framework team tells us that

Internally we have a suite of performance tests that run a variety of operations designed to simulate the typical usage a real-world application would have. We set thresholds based on the performance of EF 4.0 to ensure that we do not regress performance. These test runs are showing a 67 percent performance increase over EF 4.0 since we upgraded the server to EF 5.0.

Additionally, EF5 provides some help to queries that are generated for table-per-type relationships. You can read about this in my blog post "Entity Framework June 2011 CTP: TPT Inheritance Query Improvements."

Enum Support

The Entity Framework team gathers feature requests from the development community at the Entity Framework Feature Suggestions page. The most requested feature at one point was enumeration support. Prior to EF5, there was no way to map properties that were defined as enums, regardless of whether you were using an .edmx file or Code First to describe your model. Enum support has now been added into the core Entity Framework API in .NET 4.5, and therefore EF5 comprehends enums, both in the Entity Framework Designer and Code First mappings.

When you use Code First, the mapping conventions (defaults) recognize enums that are simply defined in the typical way you'd use them with any other .NET coding. Listing 1 shows a simple example of what that would look like where, by my preference, I'm explicitly defining the values of the enums.

enum BloodColor{    BrightRed=1,    PaleRed=2,    DarkRed=3,    Brown=4,    Black=5,    Clear=6}class BloodSample{    public int Id { get; set; }    public decimal Volume { get; set; }    public BloodColor Color { get; set; }}

Code First will work with the enums seamlessly. You can express queries using the enums and set values with them. When Entity Framework returns query results, it transforms values into enums for the relevant properties.

Here's a query along with its SQL. Notice that although the enum was used in the query, Entity Framework supplied the enum value, 4, in the SQL.

context.BloodSamples.Where(b => b.Color == BloodColor.Brown).ToList()SELECT [Extent1].[Id]     AS [Id],       [Extent1].[Volume] AS [Volume],       [Extent1].[Color]  AS [Color]FROM   [dbo].[BloodSamples] AS [Extent1]WHERE  4 = CAST([Extent1].[Color] AS int)

For the following insert statement that also uses an enum, Entity Framework creates a parameterized SQL command, which results in the enum's value, 4, being inserted into the table.

context.BloodSamples.Add(new BloodSample {Color = BloodColor.Brown, Volume = 123});insert [dbo].[BloodSamples]       ([Volume],  [Color])values (123,  4)

When you use the designer to build your model as an .edmx file, incorporating enums takes a little bit more effort. To create an entity property that's an enum instead of a known type, you first need to make the model aware of the enum.

If you're using Database First, initially the model will see the property as whatever type it discovered from the database. You'll have to define the enum and then change the property's type definition to use that enum. If you're using Model First, you can set up the enum in advance. The designer offers a few ways to add a new Enum type. Figure 2 shows the dialog box where I've defined the BloodColor enum. Notice that there's support for using enums as Flags (and defining them as types other than Int32 -- for example, as a byte). Flags will allow you to combine enums.

Enums aren't displayed on the designer surface but are visible in the Model Browser, where you can select, view, or edit enums as necessary. Once an enum is part of the model, you can specify the enum as the type of an entity's property (e.g., BloodSample.BloodColor), as shown in Figure 3. (Hint: You'll find the enum listed at the bottom of the Type drop-down list, using its strongly typed name.)


[Editor's Note: At press time, WCF Data Services 5.0.1 didn't support the new EF5 enums.]

Spatial Type Support: SqlGeography and SqlGeometry

SQL Server 2008 introduced the geography and geometry types, and Microsoft provided the .NET types SqlGeography and SqlGeometry to work with them in our applications. Geometry works with planar data whereas geography takes into account the shape of the earth. These types weren't part of .NET but were made available in a special assembly that you could download. But alas, Entity Framework didn't understand SqlGeography and SqlGeometry, so we had to lean on a workaround, using blob types in our classes and then writing extra code to work with the spatial data. In addition to requiring the extra coding, the limitation also meant that we couldn't express queries using convenient code such as SqlGeography.Distance.

.NET 4.5 brings us the System.Data.Spatial namespace (which is part of System.Data.Entity.dll) and, with it, the DbGeography and DbGeometry types, which Entity Framework can map to the database in our models. My BloodSample class now has a DbGeography property: SourceLocation, as shown in the following example:

public class BloodSample{    public int Id { get; set; }    public decimal Volume { get; set; }    public BloodColor Color { get; set; }    public DbGeography SourceLocation { get; set; }}

You can create a DbGeography instance as a point (expressed by combining a longitude and a latitude value) or a collection of points to represent a line or even a polygon. I'll keep it simple and provide a single point using the FromText method, one of a number of ways to define a DbGeography object. Note that you need to feed longitude first and latitude second -- not what most of us would expect.

new BloodSample    {        Color = BloodColor.Brown,        Volume = 123,        SourceLocation =  DbGeography.FromText("POINT(-73.19 44.47)")    };

You can also use these new types in LINQ queries. Entity Framework will translate its methods to the relevant SQL. Although there are many methods available, Distance is the easiest for a spatial newbie like me. Here's a query that finds blood samples within 250km of a random location in Montreal:

var montrealLocation = DbGeography.FromText("POINT(-73.55 45.50)");context.BloodSamples    .Where(b => b. SourceLocation.Distance(montrealLocation) < 250000).ToList();

The generated T-SQL query uses SourceLocation.STDistance to build the predicate that filters the results. The integration from LINQ to Entities to SQL using this data type is seamless. Now I'll have to start learning how to build mapping applications!

When you create spatial properties in an entity in Visual Studio 2012's Entity Framework Designer, the properties in your model entities will be defined from a more finely grained set of types that come from System.Spatial namespace. Figure 4 shows the complete list of types.

When you create spatial properties in an entity in Visual Studio 2012's Entity Framework Designer, the properties in your model entities will be defined from a more finely grained set of types that come from System.Spatial namespace. Figure 4 shows the complete list of types.

The code generated from the .edmx file will still use either DbGeography or DbGeometry. The benefit of these more granular types in your model definition is for use with Windows Communication Foundation (WCF) Data Services and OData, which already support the System.Spatial types. Note that EF5's spatial support for databases other than SQL Server is dependent on the database and provider that you're using.

[Editor's Note: WCF Data Services 5.0.1 doesn't support the SqlGeography types. It includes support for System.Spatial types, which are understood by EDMX but not by Code First.]

Table-Valued Function Support (But Only for Database First)

In a database, table-valued functions (TVFs) are like having your cake and eating it, too, because they bridge the gap between stored procedures and views. Stored procedures let you pass in parameters and express complex logic, but they aren't composable. In other words, you can't query over a stored procedure. A view is composable, but it doesn't take parameters and can only contain a single SELECT statement. Like views and stored procedures, TVFs return tabular data, but they let you create complex logic and pass in parameters (such as a stored procedure), and they're composable (like a view). EF5 now supports TVFs for Database First models.

The AdventureWorks database (SQL Server 2012 version) has a fairly complex TVF called ufnGetContactInformation that returns one or more rows of data chosen from different tables based on the ID of a person that's passed in as a parameter. When I instructed the designer to include this TVF in my model, the designer created a function import in my model, which I can use to execute the function.

The designer also created a complex type to capture the results. Complex types are unlike entities in that they don't have key values and don't get change-tracked for updates. Function imports aren't new to Entity Framework, but a function import that points to a TVF is a little different. The code generator will create a method in the context class to call the function, but this method has an EdmFunction attribute that lets us use the function in a LINQ query. Here's the signature of the function:

[EdmFunction("AdventureWorksEntities", "ufnGetContactInformation")]public virtual IQueryable  ufnGetContactInformation(Nullable personId)

When writing a LINQ query against this method:

var contacts = context.ufnGetContactInformation(125).Where(c=>c.LastName=="Lerman").ToList();

My SQL Server Profiler shows that the Where LINQ method was included in the generated SQL query, as you can see in Listing 2.

exec sp_executesql N'SELECT1 AS [C1],[Extent1].[PersonID] AS [PersonID],[Extent1].[FirstName] AS [FirstName],[Extent1].[LastName] AS [LastName],[Extent1].[JobTitle] AS [JobTitle],[Extent1].[BusinessEntityType] AS [BusinessEntityType]FROM [dbo].[ufnGetContactInformation](@PersonID) AS [Extent1]WHERE N''Lerman'' = [Extent1].[LastName]',N'@PersonID int',@PersonID=125

If I were using a stored procedure, not the TVF, the procedure would get executed without the extra WHERE filter. All the procedure's results would be returned to my application, but then the LINQ Where method would be applied in memory to those results. Composing on top of the TVF and having the database execute the full query can be a significant performance boost over using stored procedures. See the EF5 Table-Valued Functions walkthrough for a more detailed look at using TVFs in EDMX models in the designer or with Code First. Be sure not to confuse TVFs with table-valued parameters, which aren't supported in Entity Framework.

Visual Studio 2012 Entity Framework Designer Goodies

In Visual Studio 2012, the Entity Framework Designer also brings us many new features that I love. But first, let me address the two big features that were created to make it easier to visually navigate a large model: color and multiple diagrams.

Developers have different definitions of what's "too large" for an entity data model. From the perspective of working with a designer, that definition might be "more than you can manage on the designer surface." To me, that's a very small number: maybe anything over 30. Or it might be "so many entities that it slows down Visual Studio or even the runtime operations against the model." You'll start experiencing design time and runtime performance issues when you have many hundreds of entities in a single model. Yet another perspective comes from domain-driven design, where you might define a number of different physical models, each of which is focused on interacting with only those classes that pertain to the focus of a particular application feature. When you're working with an .edmx file, even a reasonably sized model might still be difficult to manage. These two new features, color and diagrams, provide help for this problem.

Color is a very nice way to quickly impart a visual understanding of the different "subdomains" of your model. The designer lets you change the color of one or more entities at a time through the Properties window, using a standard color selector. Figure 5 shows a section of a model based on the AdventureWorks database.



You can see how the color instantly lets you identify entities that have some type of relationship with one another. I chose which entities to color, so the grouping is by my own definition and not necessarily related to the physical relationships between the entities.

You might prefer to visually break up the model in a different way. You can have a single model with multiple views of that model using the new multi-diagram feature. The Model Browser is your friend here. In the Model Browser window, you can create new diagrams and drag entities from the Model Browser onto the diagram surface. You can also have the designer pull all of an entity's related entities into that diagram.

I've created some additional diagrams for my large model. Figure 6 shows a diagram I named PersonDiagram.



I dragged the Person entity (colored green) onto the diagram, then used the tool to automatically pull in all entities directly related to Person. You can see that many of the entities have navigation properties and therefore relationships to other entities. For example, Customer is related to Store, but because Store wasn't directly related to Person, the tool didn't pull Store into the diagram. I could direct the Designer to pull in entities related to Customer; then Store would be in the diagram as well. In my large model, I can now easily just focus on the entities related to a Person entity and not have to constantly reorganize and zoom the designer to see them.

In addition to the new color and multiple-diagrams features, Entity Framework Designer includes many other improvements that correct behaviors that I used to find annoying. Here are just a few:

Entity Framework Is Now Open-Sourced

While I'm on the subject of EF5, I should make sure you don't miss the fact that a few weeks before EF5 and Visual Studio 2012 were released, the Entity Framework team open-sourced Entity Framework. Development now lives on entityframework.codeplex.com. You can read the team's announcement in the blog post "Entity Framework and Open Source," as well as my perspective on my blog.

Dig In!

Of the many new features in EF5, I believe that the most important one is the improvement to performance. This is something that all users benefit from simply as a result of installing .NET 4.5 onto their machines and servers. The higher-level changes -- enums, spatial data, TVFs, and Entity Framework Designer features -- are huge changes for the developers who wanted them. With these improvements, Entity Framework is getting closer to its goal of being the data-access solution for .NET developers of every stripe.