Problem

You want to improve the performance of a query in a model with Table per Type inheritance.

Solution

Let’s say that you have a simple Table per Type inheritance model, like the one shown in Figure 13-1.

You want to query this model for a given employee. To improve the performance of the query when you know the type of employee, use the OfType<T>() operator to narrow the result to entities of the specific type, as shown in Listing 13-1.

Listing 13-1.  Improving the Performance of a Query Against a Table per Type Inheritance Model When You Know the Entity Type

using (var context = new EFRecipesEntities())
{
    context.Employees.Add(new SalariedEmployee { Name = "Robin Rosen",
                                       Salary = 89900M });
    context.Employees.Add(new HourlyEmployee { Name = "Steven Fuller",
                                       Rate = 11.50M });
    context.Employees.Add(new HourlyEmployee { Name = "Karen Steele",
                                       Rate = 12.95m });
    context.SaveChanges();
}
 
using (var context = new EFRecipesEntities())
{
    // a typical way to get Steven Fuller's entity
    var emp1 = context.Employees.Single(e => e.Name == "Steven Fuller");
    Console.WriteLine("{0}'s rate is: {1} per hour", emp1.Name,
                      ((HourlyEmployee)emp1).Rate.ToString("C"));
 
    // slightly more efficient way if we know that Steven is an HourlyEmployee
    var emp2 = context.Employees.OfType<HourlyEmployee>()
                                 .Single(e => e.Name == "Steven Fuller");
    Console.WriteLine("{0}'s rate is: {1} per hour", emp2.Name,
                      emp2.Rate.ToString("C"));
}

Following is the output of the code in Listing 13-1:

Steven Fuller's rate is: $11.50 per hour
Steven Fuller's rate is: $11.50 per hour

How It Works

The key to making the query in a Table per Type inheritance model more efficient is to tell Entity Framework explicitly the type of the expected result. This allows Entity Framework to generate code that limits the search to the specific tables that hold the values for the base type and the derived type. Without this information, Entity Framework has to generate a query that pulls together all of the results from all of the tables holding derived type values, and then it determines the appropriate type for materialization (for example, fetching and transformation of data to entity objects). Depending on the number of derived types and the complexity of your model, this may require substantially more work than is necessary. Of course, this assumes that you know exactly what derived type the query will return.

Problem

You want to retrieve a single entity using an entity key, regardless of whether you’re implementing a Database-First, Model-First, or Code-First approach for Entity Framework. In this example, you want to implement the Code-First approach.

Solution

Suppose that you have a model with an entity type representing a painting. The model might look like the one shown in Figure 13-2.

To start, this example leverages the Code-First approach for Entity Framework. In Listing 13-2, we create the entity class, Painting.

Listing 13-2.  The Painting Entity Object

public class Painting
{
    public string AccessionNumber { get; set; }
    public string Name { get; set; }
    public string Artist { get; set; }
    public decimal LastSalePrice { get; set; }
}

Next, in Listing 13-3, we create the DbContext object, which is our gateway into Entity Framework functionality when leveraging the Code-First approach.

Listing 13-3.  DbContext Object

public class Recipe2Context : DbContext
{
    public Recipe2Context()
        : base("Recipe2ConnectionString")
    {
        // Disable Entity Framework Model Compatibility
        Database.SetInitializer<Recipe2Context>(null);
    }
  
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // map AccessionNumber as primary key to table
        modelBuilder.Entity<Painting>().HasKey(x => x.AccessionNumber);
        modelBuilder.Entity<Painting>().ToTable("Chapter13.Painting");
    }
  
    public DbSet<Painting> Paintings { get; set; }

Next add an App.Config class to the project, and add to it the code from Listing 13-4, under the ConnectionStrings section.

Listing 13-4.   Connection String

<connectionStrings>
  <add name="Recipe2ConnectionString"
       connectionString="Data Source=.;
       Initial Catalog=EFRecipes;
       Integrated Security=True;
       MultipleActiveResultSets=True"
       providerName="System.Data.SqlClient" />
</connectionStrings>

In Listing 13-5, we load data and demonstrate both fetching an entity with a LINQ query and then with the Find() method.

Listing 13-5.  Retrieving an Entity with the Find() Method

private static void RunExample()
{
    using (var context = new Recipe2Context())
    {
        context.Paintings.Add(new Painting
        {
            AccessionNumber = "PN001",
            Name = "Sunflowers",
            Artist = "Rosemary Golden",
            LastSalePrice = 1250M
        });
    }
  
    using (var context = new Recipe2Context())
    {
// LINQ query always fetches entity from database, even if it already exists in context
         var paintingFromDatabase =
             context.Paintings.FirstOrDefault(x => x.AccessionNumber == "PN001");
             
         // Find() method fetches entity from context object
         var paintingFromContext = context.Paintings.Find("PN001");
     }

     Console.WriteLine("Press <enter> to continue...");
     Console.ReadLine();
}

How It Works

When issuing a LINQ query, a round trip will always be made to the database to retrieve the requested data, even if that data has already been loaded into the context object in memory. When the query completes, entity objects that do not exist in the context are added and then tracked. By default, if the entity object is already present in the context, it is not overwritten with the more recent database values.

However, the DbSet object, which wraps each of our entity objects, exposes a Find() method. Specifically, Find() expects a single argument that represents the primary key of the entity object. If necessary, an array of values can be passed into Find() to support a composite key. Find() is very efficient, as it will first search the underlying context for the target object. If found, Find() returns the entity directly from the context object. If not found, then it automatically queries the underlying data store. If still not found, Find() simply returns NULL to the caller. Additionally, Find() will return entities that have been added to the context (think of having a state of “Added”), but not yet saved to the underlying database. Fortunately, the Find() method is available with any of three modeling approaches: Database-First, Model-First, or Code-First.

In Listing 13-5, we invoke a LINQ query to retrieve a Painting. A LINQ query will always query the underlying database, even if the entity is already loaded into the context. Figure 13-3 shows the SQL query that is generated.

In the next line of code, we once again search for the same painting. This time, however, we leverage the Find() method exposed by the DbSet Class. Since the Painting entity is a DbSet class, we simply call the Find() method on it and pass in the primary key of the entity as an argument. Find() first searches the context object in memory for “PN001,” finds the object, and returns a reference to it, avoiding a round trip to the database. Note in Figure 13-4 how a SQL Query was not generated.

For a more detailed example of leveraging the Find() method in your application, take a look at Recipe 5-3.

Problem

You want to retrieve some entities efficiently that you will only display and not need to update. Additionally, you want to implement the Entity Framework Code-First approach.

Solution

A very common activity in many applications, especially websites, is to let the user browse through data. In many cases, the user will never update the data. For these situations, you can make your code much more efficient if you avoid the overhead of caching and change tracking from the context object. You can easily do this using the AsNoTracking method.

Let’s say that you have an application that manages appointments for doctors. Your model may look something like the one shown in Figure 13-5.

To start, this example leverages the Code-First approach for Entity Framework. In Listing 13-6, we create our entity classes, Company, Doctor, and Appointment.

Listing 13-6.  The Painting Entity Object

public class Company
{
    public Company()
    {
        Doctors = new HashSet<Doctor>();
    }
  
    public int CompanyId { get; set; }
    public string Name { get; set; }
  
    public virtual ICollection<Doctor> Doctors { get; set; }
}
 
public class Doctor
{
    public Doctor()
    {
        Appointments = new HashSet<Appointment>();
    }
 
    public int DoctorId { get; set; }
    public string Name { get; set; }
    public int CompanyId { get; set; }
  
    public virtual ICollection<Appointment> Appointments { get; set; }
    public virtual Company Company { get; set; }
}
 
public class Appointment
{
    public int AppointmentId { get; set; }
    public System.DateTime AppointmentDate { get; set; }
    public string Patient { get; set; }
    public int DoctorId { get; set; }
  
    public virtual Doctor Doctor { get; set; }
}

Next, in Listing 13-7, we create the DbContext object, which is our gateway into Entity Framework functionality when leveraging the Code-First approach.

Listing 13-7.  DbContext Object

public class Recipe3Context : DbContext
{
    public Recipe3Context()
        : base("Recipe3ConnectionString")
    {
        // Disable Entity Framework Model Compatibility
        Database.SetInitializer<Recipe3Context>(null);
    }
  
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Appointment>().ToTable("Chapter13.Appointment");
        modelBuilder.Entity<Company>().ToTable("Chapter13.Company");
        modelBuilder.Entity<Doctor>().ToTable("Chapter13.Doctor");
    }
  
    public DbSet<Appointment> Appointments { get; set; }
    public DbSet<Company> Companies { get; set; }
    public DbSet<Doctor> Doctors { get; set; }
    }

Next add an App.Config class to the project, and add to it the code from Listing 13-8, inside the ConnectionStrings section.

Listing 13-8.  Connection String

<connectionStrings>
  <add name="Recipe3ConnectionString"
       connectionString="Data Source=.;
       Initial Catalog=EFRecipes;
       Integrated Security=True;
       MultipleActiveResultSets=True"
       providerName="System.Data.SqlClient" />
</connectionStrings>

To retrieve the doctors and the companies they work for without adding them to the context object, chain the AsNoTracking method to the query that fetches Doctors, appointments, and company information as we have done in Listing 13-9.

Listing 13-9.  Doing a Simple Query Using the AsNoTracking Method

using (var context = new Recipe3Context())
{
    var company = new Company {Name = "Paola Heart Center"};
    var doc1 = new Doctor {Name = "Jill Mathers", Company = company};
    var doc2 = new Doctor {Name = "Robert Stevens", Company = company};
    var app1 = new Appointment
    {
        AppointmentDate = DateTime.Parse("3/18/2010"),
        Patient = "Karen Rodgers",
        Doctor = doc1
    };
    var app2 = new Appointment
    {
        AppointmentDate = DateTime.Parse("3/20/2010"),
        Patient = "Steven Cook",
        Doctor = doc2
    };
    context.Doctors.Add(doc1);
    context.Doctors.Add(doc2);
    context.Appointments.Add(app1);
    context.Appointments.Add(app2);
    context.Companies.Add(company);
    context.SaveChanges();
}
  
using (var context = new Recipe3Context())
{
    Console.WriteLine("Entities tracked in context for Doctors...");
  
    // execute query using the AsNoTracking() method
    context.Doctors.Include("Company").AsNoTracking().ToList();
     
    Console.WriteLine("Number of entities loaded into context with AsNoTracking: {0}",
        context.ChangeTracker.Entries().Count());
  
    // execute query without the AsNoTracking() method
    context.Doctors.Include("Company").ToList();
  
    Console.WriteLine("Number of entities loaded into context without AsNoTracking: {0}",
        context.ChangeTracker.Entries().Count());
  
}

Following is the output of the code in Listing 13-9:

Entities tracked in context for Doctors...
Number of entities loaded into context with AsNoTracking: 0
Number of entities loaded into context without AsNoTracking: 3

How It Works

When chaining the AsNoTracking method to your query, the objects resulting from that query are not tracked in the context object. In our case, this includes the doctors and the companies the doctors work for because our query explicitly included these.

By default, the results of your queries are tracked in the context object. This makes updating and deleting objects effortless, but at the cost of some memory and CPU overhead. For applications that stream large numbers of objects, such as browsing products at an ecommerce website, using the AsNoTracking option can result in less resource overhead and better application performance.

As you are not caching the results of a query, each time you execute the query Entity Framework will need to materialize the query result. Normally, with change tracking enabled, Entity Framework will not need to re-materialize a query result if it is already cached in the context object.

When you include the AsNoTracking option (as we do in Listing 13-9), it only affects the current query for the given entity and any related entities included in the query. It does affect subsequent queries that do not include the AsNoTracking option, as demonstrated in Listing 13-9.

Problem

You want to write a search query using LINQ so that it is translated to more efficient SQL. Additionally, you want to implement the Entity Framework Code-First approach.

Solution

Let’s say that you have a model like the one shown in Figure 13-6.

To start, this example leverages the Code-First approach for Entity Framework. In Listing 13-10, we create the entity class, Reservation.

Listing 13-10.  The Reservation Entity Object

public class Reservation
{
    public int ReservationId { get; set; }
    public System.DateTime ResDate { get; set; }
    public string Name { get; set; }
}

Next, in Listing 13-11, we create the DbContext object, which is our gateway into Entity Framework functionality when leveraging the Code-First approach.

Listing 13-11.  DbContext Object

public class Recipe5Context : DbContext
{
    public Recipe4Context()
        : base("Recipe4ConnectionString")
    {
        // disable Entity Framework Model Compatibility
        Database.SetInitializer<Recipe5Context>(null);
    }
  
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Reservation>().ToTable("Chapter13.Reservation");
    }
  
    public DbSet<Reservation> Reservations { get; set; }
}

Next add an App.Config class to the project, and also add to it the code from Listing 13-12, under the ConnectionStrings section.

Listing 13-12.  Connection String

<connectionStrings>
  <add name="Recipe4ConnectionString"
       connectionString="Data Source=.;
       Initial Catalog=EFRecipes;
       Integrated Security=True;
       MultipleActiveResultSets=True"
       providerName="System.Data.SqlClient" />
</connectionStrings>

You want to write a search query using LINQ to find reservations for a particular person, or reservations on a given date, or both. You might use the let keyword as we did in the first query in Listing 13-13 to make the LINQ expression fairly clean and easy to read. However, the let keyword is translated to more complex and often less efficient SQL. Instead of using the let keyword, consider explicitly creating two conditions in the where clause, as we did in the second query in Listing 13-13.

Listing 13-13.  Using Both the letKeyword and Explicit Conditions in the Query

using (var context = new Recipe4Context())
{
    context.Reservations.Add(new Reservation
        {Name = "James Jordan", ResDate = DateTime.Parse("4/18/10")});
    context.Reservations.Add(new Reservation {Name = "Katie Marlowe",
         ResDate = DateTime.Parse("3/22/10")});
              context.Reservations.Add(new Reservation {Name = "Roger Smith",
         ResDate = DateTime.Parse("4/18/10")});
    context.Reservations.Add(new Reservation {Name = "James Jordan",
              ResDate = DateTime.Parse("5/12/10")});
              context.Reservations.Add(new Reservation {Name = "James Jordan",
                    ResDate = DateTime.Parse("6/22/10")});
              context.SaveChanges();
          }
  
          using (var context = new Recipe4Context())
          {
              DateTime? searchDate = null;
              var searchName = "James Jordan";
  
              Console.WriteLine("More complex SQL...");
              var query2 = from reservation in context.Reservations
                  let dateMatches = searchDate == null || reservation.ResDate == searchDate
                  let nameMatches = searchName == string.Empty || reservation.Name.Contains(searchName)
                  where dateMatches && nameMatches
                  select reservation;
              foreach (var reservation in query2)
              {
                  Console.WriteLine("Found reservation for {0} on {1}", reservation.Name,
                      reservation.ResDate.ToShortDateString());
              }
  
              Console.WriteLine("Cleaner SQL...");
              var query1 = from reservation in context.Reservations
                  where (searchDate == null || reservation.ResDate == searchDate)
                        &&
                        (searchName == string.Empty || reservation.Name.Contains(searchName))
                  select reservation;
              foreach (var reservation in query1)
              {
                  Console.WriteLine("Found reservation for {0} on {1}", reservation.Name,
                      reservation.ResDate.ToShortDateString());
              }
          }

Following is the output of the code in Listing 13-13:

More complex SQL...
Found reservation for James Jordan on 4/18/2010
Found reservation for James Jordan on 5/12/2010
Found reservation for James Jordan on 6/22/2010
Cleaner SQL...
Found reservation for James Jordan on 4/18/2010
Found reservation for James Jordan on 5/12/2010
Found reservation for James Jordan on 6/22/2010

How It Works

Writing conditions inline, as we did in the second query in Listing 13-13, is not very good for readability or maintainability. Typically, we would use the let keyword to make the code cleaner and more readable. In some cases, however, this leads to more complex and often less efficient SQL code.

Let’s take a look at the SQL generated by both approaches. Listing 13-14 shows the SQL generated for the first query. Notice that the where clause contains a case statement with quite a bit of cast’ing going on. If we had more parameters in our search query beyond just name and reservation date, the resulting SQL statement would get even more complicated.

Listing 13-14.  SQL Generated When let Is Used in the LINQ Query

SELECT
[Extent1].[ReservationId] AS [ReservationId],
[Extent1].[ResDate] AS [ResDate],
[Extent1].[Name] AS [Name]
FROM [Chapter13].[Reservation] AS [Extent1]
WHERE (
  (CASE WHEN (@p__linq__0 IS NULL OR
         @p__linq__1 =  CAST( [Extent1].[ResDate] AS datetime2))
        THEN cast(1 as bit)
        WHEN ( NOT (@p__linq__0 IS NULL OR
         @p__linq__1 =  CAST( [Extent1].[ResDate] AS datetime2)))
        THEN cast(0 as bit) END) = 1) AND
  ((CASE WHEN ((@p__linq__2 = @p__linq__3) OR
          ([Extent1].[Name] LIKE @p__linq__4 ESCAPE N''∼''))
         THEN cast(1 as bit)
         WHEN ( NOT ((@p__linq__2 = @p__linq__3) OR
          ([Extent1].[Name] LIKE @p__linq__4 ESCAPE N''∼'')))
         THEN cast(0 as bit) END) = 1)

Listing 13-15 shows the SQL generated from the second query, where we created the conditions inline. This query is simpler and might execute more efficiently at runtime.

Listing 13-15.  Cleaner, More Efficient SQL Generated When Not Using let in a LINQ Query

SELECT
[Extent1].[ReservationId] AS [ReservationId],
[Extent1].[ResDate] AS [ResDate],
[Extent1].[Name] AS [Name]
FROM [Chapter13].[Reservation] AS [Extent1]
WHERE (@p__linq__0 IS NULL OR
       @p__linq__1 = CAST( [Extent1].[ResDate] AS datetime2)) AND
      ((@p__linq__2 = @p__linq__3) OR
       ([Extent1].[Name] LIKE @p__linq__4 ESCAPE N''∼''))

Problem

You are using POCO, and you want to improve the performance of change tracking while at the same time minimizing memory usage. Additionally, you want to implement the Entity Framework Code-First approach.

Solution

Suppose that you have a model with an account and related payments like the one shown in Figure 13-7.

To start, this example leverages the Code-First approach for Entity Framework. In Listing 13-16, we create two entity classes: Account and Payment. To achieve the best change-tracking performance, we need to allow Entity Framework to wrap our entity classes automatically with change-tracking proxy classes, which immediately notify the underlying change-tracking mechanism any time that the value of a property changes. With proxies, Entity Framework knows the state your entities at all times. When creating the proxy, notification events are added to the setter method of each property, which are processed by the Object State Manager. Entity Framework will automatically create a proxy class when two requirements are met: (1) all properties in an entity must be marked as virtual, and (2) any navigation property referencing a collection must be of type ICollection<T>. Meeting these requirements allows Entity Framework to override the class and add the necessary change-tracking plumbing.

Both of our Account and Payment entity classes meet these requirements, as seen in Listing 13-16.

Listing 13-16.  Our Entity Classes with Properties Marked as virtual and the Navigation Properties Are of Type ICollection<T>

public class Account
{
    public virtual int AccountId { get; set; }
    public virtual string Name { get; set; }
    public virtual decimal Balance { get; set; }
    public virtual ICollection<Payment> Payments { get; set; }
}
 
public class Payment
{
    public virtual int PaymentId { get; set; }
    public virtual string PaidTo { get; set; }
    public virtual decimal Paid { get; set; }
    public virtual int AccountId { get; set; }
}

Next, in Listing 13-17, we create the DbContext object, which is our gateway into Entity Framework functionality when leveraging the Code-First approach.

Listing 13-17.  DbContext Object

public class Recipe5Context : DbContext
{
    public Recipe5Context()
        : base("Recipe5ConnectionString")
    {
        // Disable Entity Framework Model Compatibility
        Database.SetInitializer<Recipe6Context>(null);
    }
  
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Account>().ToTable("Chapter13.Account");
        modelBuilder.Entity<Payment>().ToTable("Chapter13.Payment");
    }
  
    public DbSet<Account> Accounts { get; set; }
    public DbSet<Payment> Payments { get; set; }
}

Next add an App.Config class to the project, and add to it the code from Listing 13-18 under the ConnectionStrings section.

Listing 13-18.  Connection String

<connectionStrings>
  <add name="Recipe5ConnectionString"
       connectionString="Data Source=.;
       Initial Catalog=EFRecipes;
       Integrated Security=True;
       MultipleActiveResultSets=True"
       providerName="System.Data.SqlClient" />
</connectionStrings>

The code in Listing 13-19 illustrates inserting, retrieving, and updating our model.

Listing 13-19.  Inserting, Retrieving, and Updating Our Model

using (var context = new Recipe5Context())
{
    var watch = new Stopwatch();
    watch.Start();
    for (var i = 0; i < 5000; i++)
    {
        var account = new Account { Name = "Test" + i, Balance = 10M,
        Payments = new Collection<Payment> { new Payment {PaidTo = "Test" + (i + 1), Paid = 5M }},} ;
        context.Accounts.Add(account);
        Console.WriteLine("Adding Account {0}", i);
    }
    context.SaveChanges();
    watch.Stop();
    Console.WriteLine("Time to insert: {0} seconds", watch.Elapsed.TotalSeconds.ToString());
}
  
using (var context = new Recipe5Context())
{
    var watch = new Stopwatch();
    watch.Start();
    var accounts = context.Accounts.Include("Payments").ToList();
    watch.Stop();
    Console.WriteLine("Time to read: {0} seconds", watch.Elapsed.TotalSeconds.ToString());
    watch.Restart();
    foreach (var account in accounts)
    {
        account.Balance += 10M;
        account.Payments.First().Paid += 1M;
    }
    context.SaveChanges();
    watch.Stop();
    Console.WriteLine("Time to update: {0} seconds", watch.Elapsed.TotalSeconds.ToString());
}

How It Works

With later versions of Entity Framework, including version 6.0, we specificy POCO classes to represent entities. POCO is an abbreviation for a Plain Old CLR Object, which is a class that typically contains only states, or properties, that map to corresponding database columns. POCO classes have no dependencies beyond the .NET CLR base classes and, specifically, no dependency on Entity Framework.

Change tracking with POCO entity classes occurs using either snapshots or proxy classes. With the snapshot approach, Entity Framework takes a picture, so to speak, of the data values of an entity as it is loaded into the context object from a query or an Attach() operation. Upon a SaveChanges() operation, the original snapshot is compared to the current data values to determine the data values that have changed. Using this approach, Entity Framework maintains two copies of each object and compares them, generating the necessary corresponding SQL Update, Insert, and Delete statements. You might expect this approach to be very slow, but Entity Framework is very fast in finding the differences.

The second approach, depicted in Listing 13-19, wraps the underlying entity POCO object with a proxy object that implements the IEntityWithChangeTracking interface. This proxy is responsible for notifying the Object State Manager of changes to values and relationships on the object. Entity Framework automatically creates these proxies for your POCO object when you mark all of the properties on your POCO class as virtual and mark all Navigation properties that return a collection as ICollection<T>. Proxies avoid the potentially complex object-by-object comparisons of the snapshot approach. It does, however, require some overhead to track each change as it occurs.

Although change-tracking proxies immediately notify the change tracker components about changes to the objects and avoid object comparisons, in practice, performance benefits are typically seen only when the model is quite complex and/or when few changes are made to a large number of objects. The model in Figure 13-7 is very simple, and every object is updated in the code in Listing 13-19. If you were to change the code to use snapshots, you would notice only a second or so saved for the updates when proxies are used.

Problem

You want to improve the performance of queries that are reused several times, and you would like to achieve this performance upgrade with no additional coding or configuration.

Solution

Let’s say that you have a model like the one shown in Figure 13-8.

In this model, each Associate has zero or more paychecks. You have a LINQ query that is used repeatedly throughout your application, and you want to improve the performance of this query by compiling it just once and reusing the compiled version in subsequent executions.

When executing against a database, Entity Framework must translate your strongly typed LINQ query to a corresponding SQL query, based upon your database provider (SQL Server, Oracle, and so on). Beginning with version 5 of Entity Framework, each query translation is cached by default. This process is referred to as auto-caching. With each subsequent execution of a given LINQ query, the corresponding SQL query is retrieved directly from query plan cache, bypassing the translation step. For queries containing parameters, changing parameter values will still retrieve the same query. Interestingly, this query plan cache is shared among all instances of a context object instantiated with the application’s AppDomain, meaning that, once cached, any context object in the AppDomain has access to it.

In Listing 13-10, we compare performance with caching enabled and then disabled. To illustrate the performance benefit, we’ve instrumented the code in Listing 13-10 to print the number of ticks for each of ten iterates taken for both the uncompiled and the compiled versions of the LINQ query. In this query, we can see that we get roughly a 2X performance boost. Most of this, of course, is due to the relatively high cost of compiling versus the low cost for actually performing this simple query.

Listing 13-20.  Comparing the Performance of a Simple Compiled LINQ Query

private static void RunUncompiledQuery()
{
    using (var context = new EFRecipesEntities())
    {
        // Explicitly disable query plan caching
        var objectContext = ((IObjectContextAdapter) context).ObjectContext;
        var associateNoCache = objectContext.CreateObjectSet<Associate>();
        associateNoCache.EnablePlanCaching = false;
  
        var watch = new Stopwatch();
        long totalTicks = 0;
  
        // warm things up
        associateNoCache.Include(x => x.Paychecks).Where(a => a.Name.StartsWith("Karen")).ToList();
  
        // query gets compiled each time
        for (var i = 0; i < 10; i++)
        {
            watch.Restart();
            associateNoCache.Include(x => x.Paychecks).Where(a => a.Name.StartsWith("Karen")).ToList();
            watch.Stop();
            totalTicks += watch.ElapsedTicks;
            Console.WriteLine("Not Compiled #{0}: {1}", i, watch.ElapsedTicks);
        }
        Console.WriteLine("Average ticks without compiling: {0}", (totalTicks/10));
        Console.WriteLine("");
    }
}
  
private static void RunCompiledQuery()
{
    using (var context = new EFRecipesEntities())
    {
        var watch = new Stopwatch();
        long totalTicks = 0;
  
        // warm things up
        context.Associates.Include(x => x.Paychecks).Where(a => a.Name.StartsWith("Karen")).ToList();
  
        totalTicks = 0;
        for (var i = 0; i < 10; i++)
        {
            watch.Restart();
            context.Associates.Include(x => x.Paychecks).Where(a => a.Name.StartsWith("Karen")).ToList();
            watch.Stop();
            totalTicks += watch.ElapsedTicks;
            Console.WriteLine("Compiled #{0}: {1}", i, watch.ElapsedTicks);
        }
        Console.WriteLine("Average ticks with compiling: {0}", (totalTicks/10));
    }
}

Following is the output of the code in Listing 13-20:

Not Compiled #0: 10014
Not Compiled #1: 5004
Not Compiled #2: 5178
Not Compiled #3: 7624
Not Compiled #4: 4839
Not Compiled #5: 5017
Not Compiled #6: 4864
Not Compiled #7: 5090
Not Compiled #8: 4499
Not Compiled #9: 6942
Average ticks without compiling: 5907
 
Compiled #0: 3458
Compiled #1: 1524
Compiled #2: 1320
Compiled #3: 1283
Compiled #4: 1202
Compiled #5: 1145
Compiled #6: 1075
Compiled #7: 1104
Compiled #8: 1081
Compiled #9: 1084
Average ticks with compiling: 1427

How It Works

When you execute a LINQ query, Entity Framework builds an expression tree object for the query, which is then converted, or compiled, into an internal command tree. This internal command tree is passed to the database provider to be converted into the appropriate database commands (typically SQL). The cost of converting an expression tree can be relatively expensive depending on the complexity of the query and the underlying model. Models with deep inheritance or horizontal splitting introduce enough complexity in the conversion process that the compile time may become significant relative to the actual query execution time. However, in Version 5 of the Entity Framework, automatic query caching for LINQ queries was introduced. You can get an idea of the performance benefits of this feature by examining the results of Listing 13-20.

Additionally, as shown in Listing 13-20, you can disable the auto-compiling features by dropping down from the DbContext object into the underlying ObjectContext object, obtaining a reference to the entity object and setting its EnablePlanCaching property to false.

To track each compiled query, Entity Framework walks the nodes of the query expression tree and creates a hash, which becomes the key for that compiled query in the underlying query cache. For each subsequent call, Entity Framework will attempt to locate the hash key from the cache, eliminating the overhead cost of the query translation process. It’s important to note that the cached query plan is independent of the context object, instead being tied to the AppDomain of the application, meaning that the cached query is available to all instances of a given Entity Framework context object.

Once the underlying query cache contains 800 or more query plans, a cache eviction process automatically kicks off. Each minute, a sweeping process removes entries based upon a LFRU (least frequently/recently used) algorithm, driven by hit count and age of the query.

Compiled queries are especially helpful in ASP.NET search page scenarios where parameter values may change, but the query is the same and can be reused on each page rendering. This works because a compiled query is parameterized, meaning that it can accept different parameter values.

Problem

You have a property on an entity that is seldom read and updated. This property is expensive to read and update because of its size. To improve performance, you want to populate this property selectively.

Solution

Let’s say that you have a model like the one shown in Figure 13-9.

We can simply avoid loading one or more properties on an entity by leveraging the SqlQuery() method from the context to execute a SQL statement. The code in Listing 13-21 illustrates this approach.

Listing 13-21.  Returning Partially Filled Entities Using Both eSQL and ExecuteStoreQuery()

using (var context = new EFRecipesEntities())
{
    var r1 = new Resume
    {
        Title = "C# Developer",
        Name = "Sally Jones",
        Body = "...very long resume goes here..."
    };
    context.Resumes.Add(r1);
    context.SaveChanges();
}
  
using (var context = new EFRecipesEntities())
{
    // using SqlQuery()
    var result1 =
    context.Resumes.SqlQuery
        ("select ResumeId, Title, Name,'' Body from chapter13.Resume",
          "Resumes", MergeOption.AppendOnly).Single();
    Console.WriteLine("Resume body: {0}", result1.Body);

    var result2 =
    context.Database.SqlQuery<Resume>("select * from chapter13.Resume", "Resumes",
                     MergeOption.OverwriteChanges).Single();
    Console.WriteLine("Resume body: {0}", result2.Body);
}

Following is the output of the code in Listing 13-21:

Resume body:
Resume body: ...very long resume goes here...

How It Works

An approach for partially filling an entity is to use the SqlQuery() method that is exposed from the static Database object, which can be accessed from the DbContext object. Here we execute a SQL statement that fills all of the properties except for the Body property, which we initialize to the empty string. If needed, we can fill in the Body property from the database by setting the MergeOption to MergeOption.OverwriteChanges and requerying for the object for all of the properties. Be careful though, as the second query will overwrite any changes we’ve made to the object in memory. Keep in mind that this approach exposes the SQL query as string, which yields no compile-time checking or IntelliSense.

Recipe 13-8 shows a model-centric and perhaps cleaner approach to this problem.

Problem

You want to move a property to another entity so that you can lazy load that entity. This is often helpful if the property is particularly expensive to load and rarely used.

Solution

As with the previous recipe, let’s say that you have a model that looks like the one shown in Figure 13-10.

We’ll assume, as we did in the previous recipe, that the Body property for the Resume may contain a rather large representation of the applicant’s resume. We want to move this property to another entity so that we can lazy load, only if we really want to read the resume.

To move the Body property to another entity, do the following:

1. Right-click the design surface, and select Add Entity. Name the new entity ResumeDetail, and uncheck the Create key property check box.
2. Move the Body property from the Resume entity to the ResumeDetail entity. You can use Cut/Paste to move the property.
3. Right-click the design surface, and select Add Association. Set the multiplicity to One on the Resume side and One on the ResumeDetail side. Check the Add foreign key properties box. (See Figure 13-11.)

   

   Figure 13-11. Adding an association between Resume and ResumeDetail

4. Change the name of the foreign key that was created by the association from ResumeResumeId to just ResumeId.
5. Select the ResumeDetail entity, and view the Mapping Details window. Map the entity to the Resume table. Map the Body property to the Body column. Map the ResumeId property to the ResumeId column. (See Figure 13-12.)

   

   Figure 13-12. Map the ResumeDetail entity to the Resume table. Map the ResumeId and Body properties as well

6. Select the ResumeId property on the ResumeDetail entity and view the properties. Change the EntityKey property to true. This marks the ResumeId property as the entity’s key. The completed model is shown in Figure 13-13.

   

   Figure 13-13. The completed model with the Body property moved to the new ResumeDetail entity

The code in Listing 13-22 demonstrates how to use the ResumeDetail entity.

Listing 13-22.  Using the ResumeDetail Entity to Lazy Load the Expensive Body Property

using (var context = new EFRecipesEntities())
{
    var r1 = new Resume {Title = "C# Developer", Name = "Sally Jones"};
    r1.ResumeDetail = new ResumeDetail {Body = "...very long resume goes here..."};
    context.Resumes.Add(r1);
    context.SaveChanges();
}
  
using (var context = new EFRecipesEntities())
{
    var resume = context.Resumes.Single();
    Console.WriteLine("Title: {0}, Name: {1}", resume.Title, resume.Name);
  
    // note, the ResumeDetail is not loaded until we reference it
    Console.WriteLine("Body: {0}", resume.ResumeDetail.Body);
}

Following is the output of the code in Listing 13-22:

Title: C# Developer, Name: Sally Jones
Body: ...very long resume goes here...

How It Works

We avoided loading the expensive Body property on the Resume entity by moving the property to a new related entity. By splitting the underlying table across these two entities, we can exploit the default lazy loading of Entity Framework so that the Body property is loaded only when we reference it. This is a fairly clean approach to the problem, but it does introduce an additional entity into our model that we have to manage in our code.

Problem

You want to eagerly load a related collection without using Include(). Additionally, you want to implement the Entity Framework Code-First approach.

Solution

Let’s say that you have a model like the one shown in Figure 13-14.

To start, this example leverages the Code-First approach for Entity Framework. In Listing 13-23, we create the Customer, CreditCard, and Transaction entity classes.

Listing 13-23.  The Reservation Entity Object

public class Customer
{
    public Customer()
    {
        CreditCards = new HashSet<CreditCard>();
    }
  
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string City { get; set; }
  
    public virtual ICollection<CreditCard> CreditCards { get; set; }
}
 
public class CreditCard
{
    public CreditCard()
    {
        Transactions = new HashSet<Transaction>();
    }
  
    public string CardNumber { get; set; }
    public string Type { get; set; }
    public System.DateTime ExpirationDate { get; set; }
    public int CustomerId { get; set; }
  
    public virtual Customer Customer { get; set; }
    public virtual ICollection<Transaction> Transactions { get; set; }
}
 
public class Transaction
{
    public int TransactionId { get; set; }
    public string CardNumber { get; set; }
    public decimal Amount { get; set; }
  
    public virtual CreditCard CreditCard { get; set; }
}

Next, in Listing 13-24, we create the DbContext object, which is our gateway into Entity Framework functionality when leveraging the Code-First approach.

Listing 13-24.  DbContext Object

public class Recipe9Context : DbContext
{
    public Recipe9Context()
        : base("Recipe9ConnectionString")
    {
        // Disable Entity Framework Model Compatibility
        Database.SetInitializer<Recipe10Context>(null);
    }
  
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // explicilty specify primary key for CreditCard
        modelBuilder.Entity<CreditCard>().HasKey(x => x.CardNumber);
  
        modelBuilder.Entity<Customer>().ToTable("Chapter13.Customer");
        modelBuilder.Entity<CreditCard>().ToTable("Chapter13.CreditCard");
        modelBuilder.Entity<Transaction>().ToTable("Chapter13.Transaction");
    }
  
    public DbSet<CreditCard> CreditCards { get; set; }
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Transaction> Transactions { get; set; }
}

Next add an App.Config class to the project, and add to it the code from Listing 13-25, under the ConnectionStrings section.

Listing 13-25.  Connection String

<connectionStrings>
  <add name="Recipe9ConnectionString"
       connectionString="Data Source=.;
       Initial Catalog=EFRecipes;
       Integrated Security=True;
       MultipleActiveResultSets=True"
       providerName="System.Data.SqlClient" />
</connectionStrings>

To load all of the Customers in a given city together with their credit cards and transactions without using Include(), explicitly load the entities and let Entity Framework fix up the associations, as shown in Listing 13-26.

Listing 13-26.  Loading Related Entities without Using Include()

using (var context = new Recipe9Context())
{
   var cust1 = new Customer { Name = "Robin Rosen", City = "Raytown" };
   var card1 = new CreditCard { CardNumber = "41949494338899",
ExpirationDate = DateTime.Parse("12/2010"), Type = "Visa" };
   var trans1 = new Transaction { Amount = 29.95M };
   card1.Transactions.Add(trans1);
   cust1.CreditCards.Add(card1);
   var cust2 = new Customer { Name = "Bill Meyers", City = "Raytown" };
   var card2 = new CreditCard { CardNumber = "41238389484448",
ExpirationDate = DateTime.Parse("12/2013"), Type = "Visa" };
   var trans2 = new Transaction { Amount = 83.39M };
   card2.Transactions.Add(trans2);
   cust2.CreditCards.Add(card2);
   context.Customers.Add(cust1);
   context.Customers.Add(cust2);
   context.SaveChanges();
}
  
using (var context = new Recipe9Context())
{
   var customers = context.Customers.Where(c => c.City == "Raytown");
   var creditCards = customers.SelectMany(c => c.CreditCards);
   var transactions = creditCards.SelectMany(cr => cr.Transactions);
  
   // execute queries, EF fixes up associations
   customers.ToList();
   creditCards.ToList();
   transactions.ToList();
  
   foreach (var customer in customers)
   {
       Console.WriteLine("Customer: {0} in {1}", customer.Name, customer.City);
       foreach (var creditCard in customer.CreditCards)
       {
           Console.WriteLine("	Card: {0} expires on {1}",
creditCard.CardNumber, creditCard.ExpirationDate.ToShortDateString());
           foreach (var trans in creditCard.Transactions)
           {
               Console.WriteLine("		Transaction: {0}", trans.Amount.ToString("C"));
           }
       }
   }
}

Following is the output of the code in Listing 13-26:

Customer: Robin Rosen in Raytown
        Card: 41949494338899 expires on 12/1/2010
                Transaction: $29.95
Customer: Bill Meyers in Raytown
        Card: 41238389484448 expires on 12/1/2013
                Transaction: $83.39

How It Works

The Include() method is a powerful and usually efficient way to eagerly load related entities. However, Include() does have some performance drawbacks. Although using Include() results in just one round trip to the database in place of the three shown in Listing 13-26, the single query is quite complex and, in some cases, may not perform as well as three much simpler queries. Additionally, the result set from this single, more complex query contains duplicate columns that increase the amount of data sent over the wire if the database server and the application are on separate machines. As a rule, the more Includes contained in your query, the higher the performance penalty.

On the flip side, not using an Include() method and iterating over a large number of Customers can generate an excessive number of small queries, resulting in a large performance hit as well. Chapter 5 discusses the trade-offs and alternate approaches in detail.

Problem

You have POCO entities that use dynamic proxies. When you execute a query, you do not want to incur the cost of Entity Framework lazily creating the proxies.

Solution

Suppose that you have a model like the one shown in Figure 13-15.

The corresponding POCO classes are shown in Listing 13-27. Note how each property includes the virtual keyword and each reference to the Tracks property is of type ICollection. This will allow Entity Framework to create the tracking proxies dynamically. (See Recipe 13-5 for more information on tracking proxy objects.)

Listing 13-27.  The POCO Classes Along with Our Object Context

public partial class CD
{
    public CD()
    {
        this.Tracks = new HashSet<Track>();
    }
     
    public int CDId { get; set; }
    public string Title { get; set; }
     
    public virtual ICollection<Track> Tracks { get; set; }
}
 
public partial class Track
{
    public string Title { get; set; }
    public string Artist { get; set; }
    public int CDId { get; set; }
}

To cause Entity Framework to generate the proxies before they are required (before an entity is loaded), we need to use the CreateProxyTypes() method on the object context, as illustrated in Listing 13-28.

Listing 13-28.  Generating the tracking proxies before loading the entities

using (var context = new EFRecipesEntities())
{
            // to trigger proxy generation we need to drop-down into the underlying
            // ObjectContext object as DbContext does not expose the CreateProxyTypes() method
            var objectContext = ((IObjectContextAdapter) context).ObjectContext;
            objectContext.CreateProxyTypes(new Type[] { typeof(CD), typeof(Track) });
            var proxyTypes = ObjectContext.GetKnownProxyTypes();
            Console.WriteLine("{0} proxies generated!", ObjectContext.GetKnownProxyTypes().Count());
  
            var cds = context.CDs.Include("Tracks");
            foreach (var cd in cds)
            {
                Console.WriteLine("Album: {0}", cd.Title);
                foreach (var track in cd.Tracks)
                {
                    Console.WriteLine("	{0} by {1}", track.Title, track.Artist);
                }
            }
        }

Following is the output of the code in Listing 13-28:

2 proxies generated!
Album: Abbey Road
        Come Together by The Beatles
Album: Cowboy Town
        Cowgirls Don't Cry by Brooks & Dunn
Album: Long Black Train
        In My Dreams by Josh Turner
        Jacksonville by Josh Turner

How It Works

Dynamic proxies are created just before they are needed at runtime. This, of course, means that the overhead of creating the proxy is incurred on the first query. This lazy creation approach works well in most cases. However, you can generate the proxies before the entities are first loaded by calling the CreateProxyTypes() method.

The CreateProxyTypes() method takes an array of types and generates the corresponding tracking proxies. Once created, the proxies remain in the AppDomain for the life of the AppDomain. Notice that the lifetime of the proxy is tied to the AppDomain, not the object context. We could dispose of the object context and create another, and the proxies would not be disposed. You can retrieve the proxies in the AppDomain with the GetKnownProxyTypes() static method on the object context.