• Article

September 2011

Volume 26 Number 09

Entity Framework - New Features in the Entity Framework June CTP

By Srikanth Mandadi | September 2011

The recently released Microsoft Entity Framework (EF) June 2011 CTP includes support for a number of frequently requested features, like enums, spatial types and table-valued functions (TVFs). We’ll take a look at these features using simple walkthroughs. I’m assuming you’re familiar with the EF (https://bit.ly/oLbjp0) and with the Code First development pattern (https://bit.ly/oQ77Hm) introduced in the EF 4.1 release.

Here’s what you need to be able to try out the samples in this article:

  • Visual Studio 2010 Express and SQL Server 2008 R2 Express or higher. You can download the Express editions of Visual Studio and SQL Server Express from https://bit.ly/rsFvxJ.
  • The Microsoft EF and EF Tools June 2011 CTP.
  • The Northwind database. You can download it from https://bit.ly/pwbDoQ.

Now, let’s get started.

Enums

Let’s start with one of the most requested features in the EF—enums. Many programming languages, including .NET languages like C# and Visual Basic, have support for enums. In the EF, the goal is to allow users to have enums in their CLR types and map them to the underlying Entity Data Model (EDM), and in turn allow persisting these values to the database. Before going into detail, let’s look at a simple example. Enums are supported in the Code First, Database First and Model First approaches. I’ll start with the Database First approach and then show an example that uses the Code First approach.

For the Database First example, you’ll use the Products table in the Northwind database. You need to make sure you’re targeting the EF June 2011 CTP before adding the model, so be sure to:

  1. Launch Visual Studio 2010 and create a new C# Console Application project.
  2. Right-click on your project in Solution Explorer and select Properties.
  3. Select Microsoft Entity Framework June 2011 CTP from the Target framework drop-down (see Figure 1).
  4. Press Ctrl+S to save the project. Visual Studio asks for permission to close and reopen the project; click Yes.
  5. Add a new model to the project by right-clicking Project | Add New Item (or Ctrl+Shift+A), and selecting ADO.NET Data Entity Model from Visual C# Items (we called ours “CTP1EnumsModel.edmx”), then click Add.
  6. Follow the steps of the wizard to point to the Northwind database. Select the Products table and click Finish.
  7. The Entity Model resulting from these choices has a single Entity, as shown in Figure 2.

Targeting the Entity Framework June 2011 CTP
Figure 1 Targeting the Entity Framework June 2011 CTP

Entity Model for Product Entity
Figure 2 Entity Model for Product Entity

Here’s a LINQ query to get all products belonging to the Beverages category. Note that the CategoryID for Beverages is 1:

var ctx = new NorthwindEntities();
var beverageProducts = from p in ctx.Products
                       where p.CategoryID == 1

Of course, to write this query you’d have to know that CategoryID for Beverages is 1, which would only be possible if you remembered it or went to the database to look up the value. The other problem with this query is that it’s not clear which Category the code is querying for. It needs either some documentation in every place CategoryID is used, or some knowledge on the part of the person reading the code about the CategoryID values for various Categories.

Another problem pops up when you try to insert a new Product. Use the following code to do an insert into the Products table:

var ctx = new NorthwindEntities();
var product = new Product() { ProductName = "place holder",
  Discontinued = false, CategoryID = 13 };
ctx.AddToProducts(product);
ctx.SaveChanges();

When you run this code, you’ll get a foreign key constraint exception from the database. The exception is thrown because there’s no Category with an ID value of 13. It would be much nicer if the programmer had a way to know the list of categories and assign the correct one instead of having to remember the set of valid integer values.

Let’s introduce enums into the model and see how this improves the scenarios.

Here are the steps to change CategoryID into an enum:

  1. Open the model in the designer by double-clicking on the CTP1EnumsModel.edmx file.
  2. Right-click CategoryID property in Product Entity and choose Convert to Enum.
  3. Create an enum type and enter the values for enums members in the new dialog that opens up (see Figure 3). Name the enum type Category and select the underlying type as Byte. The underlying type is the integral type that represents the value space for enums. You can choose it based on the number of members in the enum. For this particular enum, there are eight members, which fit in a byte. Enter the members in the ascending order of the CategoryID values. Enter the Value for the first Category (Beverages) as 1 and leave the Value field empty for other members because the values for the other members automatically increment by 1 in the database. This is the default chosen by the EF as well. But if the values were different in the database, you’d enter them in the Value field for all the Categories. If the value of Beverages was 0 instead of 1, you could leave that empty as well because the EF chooses 0 as the default for the first member of an enum.
  4. When creating an enum, you can choose to designate it as a flag using the “Is Flag?” option. This is only used during code generation; if it’s checked, the enum type will be generated with a Flags attribute (see https://bit.ly/oPqiMp for more information on flags enumerations). For this example, leave the option unchecked.
  5. Rebuild the application to regenerate the code, and the resulting code now includes enums.

The Enum Type Creation Window
Figure 3 The Enum Type Creation Window

You can rewrite the query to get all products that are Beverages as follows:

var ctx = new NorthwindEntities();
var beverageProducts = from p in ctx.Products
                       where p.Category == Category.Beverages
                       select p;

Now IntelliSense helps you write the query, rather than you having to go to the database to find the value of Beverages. Similarly, in updates, IntelliSense will show me the correct values for the Category.

We just looked at enums using a Database First approach. Now I’ll use the Code First approach to write the query to get all Beverages using enums. To do this, create another Console Application and add a C# file with the types shown in Figure 4.

Figure 4 Using Enums with a Code First Approach

public enum Category : byte
{
  Beverages = 1,
  Condiments,
  Confections,
  Dairy,
  Grains,
  Meat,
  Produce,
  Seafood
}
 
public class Product
{
  public int ProductID { get; set; }
  public string ProductName { get; set; }
  public int? SupplierID { get; set; }
  [Column("CategoryID", TypeName = "int")]
  public Category Category { get; set; }
  public string QuantityPerUnit { get; set; }
  public decimal? UnitPrice { get; set; }
  public short? UnitsInStock { get; set; }
  public short? UnitsOnOrder { get; set; }
  public short? ReorderLevel { get; set; }
  public bool Discontinued { get; set; }
}
public class EnumsCodeFirstContext : DbContext
{
  public EnumsCodeFirstContext() : base(
    "data source=<server name>; initial catalog=Northwind;
    integrated security=True;multipleactiveresultsets=True;")
  {
  }
  public DbSet<Product> Products { get; set; }
}

The class EnumsCodeFirstContext inherits from DbContext. DbContext is a new type that shipped in the EF 4.1 and is similar to ObjectContext—but is much simpler and more straightforward to use. (For more information on using the DbContext API, see https://bit.ly/eeEsyt.)

 A couple of things to note in the code in Figure 4:

  • The Column attribute above the Category property: This is used to map between CLR properties and columns when the two have different names or types.
  • The constructor for EnumsCodeFirstContext, which invokes the base class constructor by passing in a connection string: By default, DbContext creates a database in the local SqlExpress with a fully qualified class name that derives from DbContext. For this sample, we simply use the existing Northwind database.

Now you can write code similar to the Database First context to get all the products that belong to the Beverages Category:

EnumsCodeFirstContext ctx = new EnumsCodeFirstContext();
var beverageProducts = from p in ctx.Products
                       where p.Category == Category.Beverages
                       select p;

Table-Valued Functions

Another significant feature added in this CTP is support for TVFs. TVFs are very similar to stored procedures with one key difference: the result of a TVF is composable. That means the results from a TVF can be used in an outer query. So, a major implication for developers using the EF is that a TVF can be used in a LINQ query while a stored procedure can’t. I’ll walk through an example that shows how TVFs can be used in an EF application. In the process, you’ll see how to take advantage of the Full-Text Search (FTS) functionality in SQL Server (see https://bit.ly/qZXG9X for more information). 

FTS functionality is exposed via a couple of predicates and TVFs. In previous versions of the EF, the only way to use the full-text TVFs would be to either invoke them in a T-SQL script using ExecuteStoreCommand or use a stored procedure. But both of these mechanisms are not composable and can’t be used in LINQ to Entities. My example will show you how to use these functions as composable functions with the support for TVFs in this CTP. To do this, I’ve taken a query from MSDN documentation for ContainsTable (https://bit.ly/q8FFws). The query searches for all product names containing the words “breads,” “fish” or “beers,” and different weights are given to each word. For each returned row matching these search criteria, the relative closeness (ranking value) of the match is shown:

SELECT FT_TBL.CategoryName, FT_TBL.Description, KEY_TBL.RANK
  FROM Categories AS FT_TBL
    INNER JOIN CONTAINSTABLE(Categories, Description,
    'ISABOUT (breads weight (.8),
    fish weight (.4), beers weight (.2) )' ) AS KEY_TBL
      ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;

Let’s try to write the same query in LINQ to Entities. Unfortunately, you can’t expose ContainsTable directly to the EF because it expects the first two parameters (table name and column name) as unquoted identifiers—that is, as Categories instead of ‘Categories,’ and there’s no way to tell the EF to treat these parameters specially. To work around this limitation, wrap ContainsTable in another user-defined TVF. Execute the following SQL to create a TVF called ContainsTableWrapper (the TVF executes the ContainsTable function on the Description column in Categories table):

Use Northwind;
Create Function ContainsTableWrapper(@searchstring nvarchar(4000))
returns table
as
return (select [rank], [key] from ContainsTable(Categories, Description,
  @searchstring))

Now, create an EF application and use this TVF. Follow the same steps as described for the enums example to create a console application and add an entity model by pointing to Northwind. Include Categories, Products and the newly created TVF. The model will look like the one shown in Figure 5.

Entity Model with Products and Categories from Northwind
Figure 5 Entity Model with Products and Categories from Northwind

The TVF is not shown on the designer surface, but you can view it in the Model Browser by expanding the Stored Procedures/Functions in the Store section.

To use this function in LINQ, add a function stub (as described at https://bit.ly/qhIYe2). I added the function stub in a partial class for the ObjectContext class, in this case NorthwindEntities:

public partial class NorthwindEntities
  {
    [EdmFunction("NorthwindModel.Store", "ContainsTableWrapper")]
    public IQueryable<DbDataRecord> ContainsTableWrapper(string searchString)
    {
      return this.CreateQuery<DbDataRecord>(
        "[NorthwindModel.Store].[ContainsTableWrapper](@searchstring)",
        new ObjectParameter[] {
        new ObjectParameter("searchString", searchString)});
    }
  }

You can now start using this function in your queries. Simply print out the Key—that is, CategoryId and Rank for the full-text query, mentioned earlier:

var ctx = new NorthwindEntities();
var fulltextResults = from r in ctx.ContainsTableWrapper("ISABOUT (breads weight (.8),
  fish weight (.4), beers weight (.2) )")
                    select r;
foreach (var result in fulltextResults)
{
  Console.WriteLine("Category ID:" +  result["Key"] + "   Rank :" + result["Rank"]);
}

The output on the console when you run this piece of code is as follows:

Category ID:1   Rank :15
Category ID:3   Rank :47
Category ID:5   Rank :47
Category ID:8   Rank :31

But this isn’t the query we were trying to write. The one we want actually does a little more. It provides the Category Name and Description along with the rank, which is more interesting than just the Category ID. Here’s the original query:

SELECT FT_TBL.CategoryName, FT_TBL.Description, KEY_TBL.RANK
  FROM Categories AS FT_TBL
    INNER JOIN CONTAINSTABLE(Categories, Description,
    'ISABOUT (breads weight (.8),
    fish weight (.4), beers weight (.2) )' ) AS KEY_TBL
      ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC;

In order to use LINQ for this query, you need to map the TVF to a Function Import in EDM with a Complex type or an Entity return type, because Function Imports that return Row Types are not composable.

To do the mapping, here’s what you need to do:

  1. Double-click the Store function in the model browser to open the Add Function Import dialog shown in Figure 6.
  2. Enter the Function Import Name as ContainsTableWrapperModelFunction.
  3. Check the Function Import is Composable? box.
  4. Select the ContainsTableWrapper function from the drop-down list for Stored Procedure/Function Name.
  5. Click the GetColumnInformation button to populate the table below the button with information about the result type returned from the function.
  6. Click the Create New Complex Type button. It results in changing the selection of “Returns a Collection Of” radio button to Complex with a generated name for the complex type.
  7. Click OK.

Mapping a TVF to a Function Import
Figure 6 Mapping a TVF to a Function Import

For the TVFs that are mapped to Function Imports in the model, you don’t need to add a corresponding function in the code because the function is generated for you.

Now you can write the T-SQL query that was using the FTS function ContainsTable in LINQ, as follows:

var ctx = new NorthwindEntities();
var fulltextResults = from r in ctx.ContainsTableWrapperModelFunction("ISABOUT
  (breads weight (.8), fish weight (.4), beers weight (.2) )")
                     join c in ctx.Categories
                     on r.key equals c.CategoryID
                     select new { c.CategoryName, c.Description, Rank = r.rank };
 
foreach (var result in fulltextResults)
{
  Console.WriteLine("Category Name:" + result.CategoryName + "   Description:" +
    result.Description + "   Rank:" + result.Rank);
}

When you run this code, the output on the console is:

Category Name:Beverages   Description:Soft drinks, coffees, teas, beers, and ales   Rank:15
Category Name:Confections   Description:Desserts, candies, and sweet breads   Rank:47
Category Name:Grains/Cereals   Description:Breads, crackers, pasta, and cereal Rank:47
Category Name:Seafood   Description:Seaweed and fish   Rank:31

Spatial Types Support

Now let’s look at a new feature that has caused a lot of excitement: support for spatial types. Two new types have been added to the EDM—DbGeometry and DbGeography. The next example will show you how straightforward it is to use spatial types in the EF using Code First.

Create a ConsoleApplication project called EFSpatialSample, then add a C# file to this project with the following types:

namespace EFCTPSpatial
{
  public class Customer
  {
    public int CustomerID { get; set; }
    public string Name { get; set; }
    public DbGeography Location { get; set; }
  }
 
  public class SpatialExampleContext : DbContext
  {
    public DbSet<Customer> People { get; set; }
  }
}

The Location property in Customer is of type DbGeography, which has been added to the System.Data.Spatial namespace in this CTP. DbGeography is mapped to SqlGeography in the case of SQL Server. Insert some spatial data using these types and then use LINQ to query for the data (see Figure 7).

Figure 7 Working with Spatial Data

static void Main(string[] args)
  {
    var ctx = new SpatialExampleContext();
    ctx.Customers.Add(new Customer() { CustomerID = 1, Name = "Customer1",
      Location = DbGeography.Parse(("POINT(-122.336106 47.605049)")) });
    ctx.Customers.Add(new Customer() { CustomerID = 2, Name = "Customer2",
      Location = DbGeography.Parse(("POINT(-122.31946 47.625112)")) });
    ctx.SaveChanges();
 
    var customer1 = ctx.Customers.Find(1);
    var distances = from c in ctx.Customers                           
                    select new { Name = c.Name, DistanceFromCustomer1 =
                    c.Location.Distance(customer1.Location)};
    foreach (var item in distances)
    {
      Console.WriteLine("Customer Name:" + item.Name + ",
        Distance from Customer 1:" + (item.DistanceFromCustomer1 / 1609.344 ));
    }               
  }

What happens in the code is pretty simple. You create two Customers with two different locations and specify their locations using Well-Known Text (you can read more about this protocol at https://bit.ly/owIhfu). These changes are persisted to the database. Next, the LINQ to Entities query gets the distance of each customer in the table from Customer1. The distance is divided by 1609.344, which converts from meters to miles. Here’s the output from the program:

Customer Name:Customer1,  Distance from Customer 1:0
Customer Name:Customer2,  Distance from Customer 1:1.58929160985881

As expected, the distance from Customer1 to Customer 1 is zero. The distance of Customer 1 from Customer 2 is in miles. The Distance operation in the query gets executed in the database using the STDistance function. Here’s the SQL query that gets sent to the database:

SELECT 1 AS [C1], [Extent1].[Name] AS [Name], [Extent1].[Location].STDistance(@p__linq__0)
  AS [C2]
FROM [dbo].[Customers] AS [Extent1]

Auto-Compiled LINQ Queries

When you write a LINQ to Entities query today, the EF walks over the expressiontree generated by the C# or Visual Basic compiler and translates (or compiles) it into SQL. Compiling the expression tree into SQL involves some overhead, though, particularly for more complex queries. To avoid having to pay this performance penalty every time the LINQ query is executed, you can compile your queries and then reuse them. The CompiledQuery class allows you to pay the overhead of compilation just once, and gives you back a delegate that points directly at the compiled version of the query in the EF cache.

The June CTP supports a new feature called the Auto-Compiled LINQ Queries, which lets every LINQ to Entities query you execute automatically get compiled and placed in the EF query cache. Every time you run the query subsequently, the EF will find it in its query cache and won’t have to go through the whole compilation process again. This feature also provides a boost to queries issued using WCF Data Services, as it uses LINQ under the covers. For more on expression trees, see https://bit.ly/o5X3rA.

Wrapping Up

As you can see, there are a number of exciting features coming in the next release of the EF—and there are even more than those I’ve been able to touch on here, such as SQL generation improvements for Table per Type, or TPT, mapping and multiple result sets from stored procedures. The CTP gives you a chance to try out the bits and provide feedback to fix any bugs or improve the experience of the new features. You can report any bugs using the Microsoft Data Developer Connect site (https://connect.microsoft.com/data), or suggest new features via the EF user voice (https://ef.mswish.net) Web site.

Srikanth Mandadi is a development lead on the Entity Framework team.

Thanks to the following technical experts for reviewing this article:* The Entity Framework Team***