Writing efficient queries with Entity Framework Code First (Part 1)

In this series, we will explore the Social Recipes sample application. This is a simple application that is intended to show some of the common inefficient queries that can be generated using Entity Framework (EF) Code First. The application is built using ASP.NET MVC4 and EF 5 Code First 5. To make things a little easier, we also use the following nuget packages:

Install-Package nInject.MVC3
Install-Package MiniProfiler.MVC3
Install-Package MiniProfiler.EF

The application is site that allows users to post, rate, and review recipes, create and join groups, and share recipes with those groups.

Our domain model consists of 4 classes: User, Group, Recipe, and Comment

The database context contains 4 DbSets:

public class RecipeContext : DbContext
{ 
    public DbSet<User> Users { get; set; }
    public DbSet<Group> Groups { get; set; }
    public DbSet<Recipe> Recipes { get; set; }
    public DbSet<Comment> Comments { get; set; }
}

The classic n+1 select problem

Let’s start by exploring the My Recipes page.

This page displays all the recipes that I have posted. Each recipe is displayed on a card, which contains the title, a link to the recipe details, a count of the number of comments on the recipe and other basic information.

The controller for this page queries the context for the current user, then passes the current user’s recipes to the view.

public ActionResult Index()
{
    User currentUser = _recipeContext.Users
              .Single(u => u.Id.ToUpper() == User.Identity.Name.ToUpper());
    return View(currentUser.Recipes);
}

The view then iterates over each recipe and renders a div with the information for each recipe (including a count of the number of recipes).

At first glance, this page appears to load in a reasonable amount of time. However, once we turn MiniProfiler on, we that an unusually large number of SQL queries are executed to render this page.

What we have here is the classic N+1 select problem. First, there is 1 query to retrieve the current User. When we access the Recipes collection on that user, another query is executed to retrieve all the Recipes. This Recipes collection is not loaded until we actually try to iterate over the collection. This pattern is referred to as Lazy Loading. The real problem occurs when we try to get a count of the number of Comments for each Recipe. The Comments collection is also lazy loaded, so now we get an additional query for each Recipe in the collection. Where N=# of Recipes, we ended up with N+1 queries (+1 again for the initial query to initially retrieve the User). In our simple example, the My Recipes page resulted in 27 queries. That’s 27 round trips to the database just to display this simple little webpage. Clearly this is not an optimal solution!

How can we fix it?

Luckily, EF Code First provides a simple extension method called Include() that can be used to control when properties should be Eager Loaded (instead of the default Lazy Loaded). With eager loading, EF can generate a single query to load all the data we need.

Here’s all we need to do:

public ActionResult Index()
{
    User currentUser = _recipeContext.Users.Include("Recipes")
                                           .Include("Recipes.Comments")
                  .Single(u => u.Id.ToUpper() == User.Identity.Name.ToUpper());
    return View(currentUser.Recipes);
}

Now, when access the My Recipes page, we can see that only a single query is executed.

That’s a pretty huge improvement. The page loads almost twice as fast as it previously did, and we have reduced the number of database round trips from 27 to 1. Not bad for just calling an extension method!

What’s Next?

In the next part of this series, we will to explore some situations where eager loading can get us into trouble and some more advanced strategies we can use to generate efficient queries with EF Code First.

get the source – Social Recipes on GitHub

Part 2 - LINQ Projects
Part 3 - Paging