Friends Don’t Let Friends use Lazy Loading on Windows Azure!

Now don’t get me wrong, I’m a HUGE fan of Entity Framework. What I don’t get though, is why Lazy Loading is on by default.

As one of my friends likes to quote “with great power comes great responsibility” and he’s absolutely right! Lazy loading is very powerful and should be used selectively. I’m not saying that you should never use it, but you should really think long and hard about how you use it.

By forgetting that Lazy Loading is on by default, it’s quite easy to create N+1 situations where what could have been a simple read from the database can turn into a nightmare!

N+1 situations are caused by reading a collection of entities from the database and iterating over them to read something from a Navigation Property. Since it’s not loaded when we try to access it, Entity Framework will try to load it from the database. Consequently, we are hitting the database once for the initial list and once for every entity in that list. This is why we call this N+1.

Now imagine that you have a Worker Role whose job is to query the database to generate graphs for reports. A single instance of this Role might work out fine, but the second you start scaling out… let’s just say that you might find the results interesting.

But since you’re here let’s dive a bit deeper into this.

For the sake of this example, for each chart that we want to generate we are going to query Entity Framework for 1000 entities. Then we iterate over them in order to read a property from a Navigation Property.

Essentially we are going to make 1001 requests instead of 1.

Scaling out means that we duplicate our processes over multiple Virtual Machines. So when we start to Scale the Worker Role, we start to multiply the number of requests hitting our Windows Azure SQL Database. This means that if we scale out to 5 instances and that each instance generates only one chart at a time (which should never be the case) our Windows Azure SQL Database will receive 5005 requests instead of 5.

This situation could easily have been avoided by deactivating lazy loading. Doing so would force the developer to choose between Eager Loading, Explicit Loading or activating Lazy Loading.

Eager Loading is the process whereby a query for one type of entity also loads related entities as part of the query. Eager loading is achieved by use of the Include method.

Explicit Loading is the process whereby loading related entities is done with an explicit call

Lazy Loading is the process whereby an entity or collection of entities is automatically loaded from the database the first time that a property referring to the entity/entities is accessed.

Performance Considerations

When you choose a pattern for loading related entities, consider the behavior of each approach with regard to the number and timing of connections made to the data source versus the amount of data returned by and the complexity of using a single query. Eager loading returns all related entities together with the queried entities in a single query. This means that, while there is only one connection made to the data source, a larger amount of data is returned in the initial query. Also, query paths result in a more complex query because of the additional joins that are required in the query that is executed against the data source.

Explicit and lazy loading enables you to postpone the request for related object data until that data is actually needed. This yields a less complex initial query that returns less total data. However, each successive loading of a related object makes a connection to the data source and executes a query. In the case of lazy loading, this connection occurs whenever a navigation property is accessed and the related entity is not already loaded. If you are concerned about which related entities are returned by the initial query or with managing the timing of when related entities are loaded from the data source, you should consider disabling lazy loading. Lazy loading is enabled in the constructor of the Entity Framework-generated object context. [More]

Find examples and more information about Loading Related Objects.

Disabling Lazy Loading

Disabling Lazy Loading in Entity Framework Code First can be achieved by configuring the DbContext

public class BloggingContext : DbContext
{
    public BloggingContext()
    {
        this.Configuration.LazyLoadingEnabled = false;
    }
}

Disabling Lazy Loading using the Entity Framework Designer can be achieved by going into your model’s properties to set it to false.

Wrapping it up

When I start new projects using ORMs like Entity Framework, I disable Lazy Loading because it allows my team to ask the right questions at the right moment. If at some point we really need Lazy Loading and that other options aren’t acceptable, then I happily turn it on.