Hi and welcome back to Weekly Dev Tips. I’m your host Steve Smith, aka Ardalis.

This is episode 34, in which we'll talk about lazy loading in ASP.NET and ASP.NET Core apps, and why it's evil.

If you’re enjoying these tips, please leave a comment or rating in your podcast app, tell a friend about the podcast, or follow us on twitter and retweet our episode announcements so we can increase our audience. I really appreciate it.

Avoid Lazy Loading in ASP.NET (Core) Apps

This week's tip is on the topic of lazy loading using Entity Framework or EF Core in ASP.NET and ASP.NET Core apps. Spoiler alert: don't do it. Keep listening to hear why.

Sponsor - devBetter Group Career Coaching for Developers

Last week I announced my new developer career coaching program, devBetter. If you're not advancing as quickly in your career as you'd like, and you could use someone in your corner pushing you to succeed and opening up doors to new opportunities, consider joining a handful of like-minded developers at devbetter.com.

Show Notes / Transcript

Lazy loading is a feature that EF6 has had for a long time and EF Core only recently added with version 2.1. The way it works is, entities are fetched without their related entities, and these related entities are loaded "just in time" as code references them. This seems to follow the best practice of deferred execution, but unfortunately the downsides far outweigh the benefits the vast majority of the time in this case. I recommend disabling lazy loading in all ASP.NET and ASP.NET Core apps. Let's look at why.

On any given web request, the goal should be to return a response to the client as quickly as possible. The fewer out-of-process calls the request needs to make before it can return a response, the faster it will be, all things being equal. If a round-trip to the database takes 20ms and processing the request requires 10 database calls, then assuming they can't be made in parallel the minimum time for this is 200ms. If the same data could be fetched in a single round-trip to the database, it would cut page load time by 180ms, not counting the time to execute the queries themselves which might also be faster if done in one batch.

When you use lazy loading, your code will make more calls to the database than if you had used eager loading. It's also deceptively easy to write code that will result in lazy loading being done within some kind of loop, resulting in dozens or hundreds of database calls. This can be difficult to detect in development and even in testing, but in production where usually there are more users and larger sets of data in use, the problem can have huge performance implications.

I have a GitHub repo that demonstrates lazy loading using ASP.NET MVC 5 and EF 6 and also ASP.NET Core with EF Core. I encourage you to download it and run it yourself. It demonstrates the problem using a conference web site as its sample data. There are conference sessions. Each session has one or more speakers presenting it. Each session can have one or more tags. For sample data I have 2 sessions with 2 speakers and 3 tags total. Displaying the page shows each session and its speakers, tags, and description, all done with some simple razor code in the view. The initial query just pulls in the sessions - the speakers and tags are lazy loaded. How many queries do you think this page makes to the database?

Let's think about how many it should make. Assuming the site's just loaded and has no cache in place, it should be able to load the data for this page using a single query. At worst a couple of queries. This kind of data is also highly cacheable, so after the first load the page should render with 0 queries. For this reason I like to say "caching hides many sins" because even if you do use lazy loading and have way too many queries, if you add caching it'll be the rare user who has to suffer for it.

Coming back to the sample, with 2 sessions, 2 speakers, and 3 tags, the page makes 22 database queries to render the page using lazy loading. It should be clear that this number is going to grow rapidly as the number of sessions, speakers, and tags increases. Most conferences have more than 2 sessions, after all, but during development maybe only a couple are used and only one user is hitting the page, so the performance impact might not be felt until the worst possible time: the day of the conference. At which point it may be too late to fix and redeploy the code.

Lazy loading is a tool that makes sense in certain situations. It's especially effective when the application and the database are colocated and there's just one user. If you're writing bookkeeping software that runs locally and communicates with a local database, it might make sense to use lazy loading as the user navigates around the system rather than trying to eager load all of the data. But in the context of processing a single web request, when every extra trip to the database slows the page down further, and where it can be easy to inadvertently add dozens or more requests, you should avoid it.

Show Resources and Links

• devBetter
• Avoid Lazy Loading Entities in ASP.NET Apps
• Lazy Loading GitHub Sample
• How to Disable Lazy Loading in EF

That’s it for this week. Thank you for subscribing to Weekly Dev Tips, and we’ll see you next week with another great developer tip.