Do You Make These Mistakes With Entity Framework? • Developing Software

Entity Framework is a powerful ORM tool which makes storage/retrieval of data simple. Although it’s simple to get going, there are a few gotchas that can cripple your app performance. Here’s my top 5 list of mistakes to look out for:

1. Do you instantiate the DbContext too many times?

Did you know? There is a slight overhead each time you instantiate a DbContext object. You also lose out on the first-level caching feature of the Entity Framework. If you’re using Entity Framework with ASP.NET MVC, it’s better to use a single instantiated instance for the full duration of the request.

Solution:

Keep the DbContext around for longer, for example, in ASP.NET MVC, use an IoC container to inject a single instance that lasts the full duration of the request.

2. Are you tracking entities when you don’t need to?

Whenever a query is run, the entities are loaded into the object state tracker. This is done to track what changes are made to the model during the life of the context, so that a call to SaveChanges will perform the required SQL queries on the database. This is a powerful feature, however, it does add a lot of overhead and the queries are slower than none tracked queries.

Solution:

Use the AsNoTracking method for queries that are read only. For example, _context.Posts.AsNoTracking().ToList();

3. Do you suffer from select N+1 issues?

When lazy loading is enabled, it makes it possible to introduce select N+1 issues into your code. Hereӳ an example of a select N+1:

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);
}

The above code will query the database multiple times when it’s more efficient to do it once.

Solution:

Disable Lazy Loading and Use Include

4. Are you including more fields in your queries than necessary?

If you have a really big model, the chances are, for some queries you don’t need all the data from the model. For example, let’s say you want a list of post titles. It makes no sense to call Posts.ToList(), even if you are using paging, because it will load all fields from the database.

Solution:

Use Entity Framework Projection Queries

5. Do you run queries that bring back too many results?

What’s wrong with these queries?

_context.Posts.ToList().Where(post => post.Active);
_context.Posts.Where(post => post.Active).ToList();

You guessed it, there both gonna load too many results. The first query will load all posts because the ToList method has been called before the Where. The second query will load less results, but still too many, because thereӳ no limit on the amount of active posts.

Solution:

Perform any filtering before the call to ToList.
Limit the amount of results returned by using a paging extension method.

1. Do you instantiate the DbContext too many times?

Solution:

2. Are you tracking entities when you don’t need to?

Solution:

3. Do you suffer from select N+1 issues?

Solution:

4. Are you including more fields in your queries than necessary?

Solution:

5. Do you run queries that bring back too many results?

Solution:

Further Reading