Why Is Entity Framework with ASP.NET Slow?

Do you have a slow running ASP.NET Web Application? There’s a good chance it’s because of lazy loading, an Entity Framework feature that’s enabled by default. In my opinion, you should not use lazy loading for web applications. In this post I will explain why lazy loading can slow down performance and what you can do instead.

What is Lazy Loading?

Lazy loading is a design pattern that delays the initialisation of an object until the point it’s first accessed. It’s purpose is to make your application use less memory and increase efficiency by reducing the amount of data transferred to/from the database.

Unfortunately, it can do more harm than good. Especially if someone on your team is not aware of lazy loading and what’s going on behind the scenes.

The hidden problem that lazy loading causes is the nasty select N+1 issue. Lets take a look at an example of Entity Framework lazy loading and what causes a select N+1 issue.

Entity Framework Lazy Loading

In the following example, imagine you have a User that can have many Roles. The Entity Framework model for this use case might look something like the following:

public class User
{
    public int Id { get; private set; }
    public string Name { get; set; }
    public virtual ICollection<Role> Roles { get; set; }
}

public class Role
{
    public int Id { get; private set; }
    public string Name { get; set; }
}

public class MyDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Role> Roles { get; set; }
}

Now lets say you have a User called Bob and a User called Smith, and they both have the Admin, Editor, Author and Guest Roles. As an example, lets say you wanted to print all the Users Names and Roles. The code that would cause a select N+1 issue might look something like this:

using (var context = new MyDbContext())
{
    var users = context.Users.ToList(); // first SQL query
    foreach (var user in users)
    foreach (var role in user.Roles)
    {
        // SQL query for each user
        ViewBag.Message += String.Format("User: {0} Role: {0},", user.Name, role.Name);
    }  
}

OK, so the above example is not likely to come up very often, but I have seen/inherited projects that are riddled with these select N+1 issues. This example would generate 3 SQL queries, 1 to get the Users and 1 for each call to the users Roles.

And that’s only for 2 Users.

You can see how much damage to an applications performance Entity Framework lazy loading could cause. How do we solve the issue?

Solving the select N+1 issue

To fix the issue I recommend always using Include when you need to access the child entities. The above improved example would look like this:

using (var context = new MyDbContext())
{
    var users = context.Users.Include(i => i.Roles).ToList();
    foreach (var user in users)
    foreach (var role in user.Roles)
    {
        ViewBag.Message += String.Format("User: {0} Role: {0},", user.Name, role.Name);
    }
} 

The call to Include in the above example makes the code run only 1 SQL query. This drastically improves the performance.

The best execution time on my machine for the first example (lazy loading) was 2.1 ms. The best time for the second example (no lazy loading) was 0.80 ms.

Disabling Lazy Loading

To make sure no one on your team accidentally causes an N+1, I would recommend turning off Lazy loading. This can be done by setting the Configuration.LazyLoadingEnabled property to false in your DbContext class constructor.

Now after doing this, you have to remember that queries that don’t have a call to Include will cause an exception when trying to access the child entities. So in the first example (lazy loading) you would get an exception when trying to loop through the users roles because Roles would be null.

Personally, I would rather scratch my head as to why I’m getting null child entities rather than why the performance is shocking. As soon as you see a null reference exception, you know there is an Include missing somewhere.

So when should you use Lazy Loading?

For web applications, I’ve yet to find a reason for using Lazy loading. I think this is because web applications are stateless, which makes lazy loading pointless.

If you want to select only a limited amount of data, there are better ways of doing this by using projection queries.

It probably makes more sense to use lazy loading for applications that are stateful (desktop). However, personally, I don’t think its worth the hassle of introducing possible performance issues.

Disable lazy loading, use projection queries, and your performance will rocket.