All Blog Posts

Entity Framework Series – Part 1: Query Optimizations

Entity Framework SpidermanEntity Framework (EF) is Microsoft’s recommended data access technology for new applications. It’s very easy to set up, eliminates a ton of cumbersome plumbing code, and is extremely powerful. But like my old pal Spiderman says, “With great power comes great responsibility.”

Writing our data access code primarily in LINQ is a very good thing. We get better Intellisense when building our queries (what was that field name again??), make our codebase well-suited to refactoring, and avoid the context shift from our programming language to SQL. However, we give up the ultimate control over our queries, and oftentimes we incur performance penalties if we’re not careful. While Entity Framework is easy to use, it’s also easy to be satisfied with whichever solution completes our task the quickest and not factor in performance and maintainability.

In this blog series I will review several Entity Framework Code First concepts. If you have questions, comments, or suggestions please either comment on the post or contact me on Twitter. Here’s the agenda:

Part 1 – Query Optimizations

All of my code examples are available on Github, so please follow along as I go (also, feel free to review my code as needed—I welcome feedback!). I am using a typical Blog model in my examples.

EF enables lazy loading out of the box if your navigation property is virtual, which means that when you access child objects they will automatically be loaded from the database. This is great in some cases, but you need to be aware of possible issues. Take the following for example (click the image to view on Github):
Lazy Loading Entity Framework

It’s easy to expect that this will run one DB query, but when we’re populating our PostsViewModel, we’re accessing the Authors child object and EF will lazy load each object as it needs it. As a result, we’re not executing one DB query, we’re executing 1 + (distinct count of authors). When loading a large amount of records with a complex object graph, we can run into big time performance issues, commonly referred to as SELECT N + 1.

A small adjustment fixes the issue. We’re issuing Include commands to EF so that it will eagerly load the Authors, resulting in one query. A lot of examples I see use the string notation, but I prefer to include System.Data.Entity and use the lambda notation to take advantage of Intellisense.
Entity Framework: Eager Loading

Here are a few more examples of using Includes to increase query performance. Please note: just because you CAN eager load doesn’t always mean you SHOULD. Use your own judgment and consult with your DBA if needed.
Entity Framework Eager Loading Example


Nothing has changed—indexes are still important. They are a bit difficult to integrate into a Continuous Integration environment without using Entity Framework Migrations (Part 3 of this series) or manual post-deployment scripts. As developers get more used to ORMs and not directly working with SQL Server, sometimes our SQL skills decline. Work with your DBA to identify indexing opportunities.

Link Dump

Entity Framework Profiler: This is an incredibly useful tool to profile your queries. Learn this. Use this. Love this.

Julie Lerman’s PluralSight videos: Julie provides good real-world examples. I specifically recommend Entity Framework in the Enterprise and Entity Framework Code First (a few versions older but still applicable).

Julie Lerman’s Blog: Lots of good stuff!

In part 2 of this EF series, I’ll cover model mapping. Check back in 2 weeks for more excitement.

Update! Part 2 is posted. Read it now.