.Include() vs .Load() performance in EntityFramework

by

It depends, try both

When using Include(), you get the benefit of loading all of your data in a single call to the underlying data store. If this is a remote SQL Server, for example, that can be a major performance boost.

The downside is that Include() queries tend to get really complicated, especially if you have any filters (Where() calls, for example) or try to do any grouping. EF will generate very heavily nested queries using sub-SELECT and APPLY statements to get the data you want. It is also much less efficient — you get back a single row of data with every possible child-object column in it, so data for your top level objects will be repeated a lot of times. (For example, a single parent object with 10 children will product 10 rows, each with the same data for the parent-object’s columns.) I’ve had single EF queries get so complex they caused deadlocks when running at the same time as EF update logic.

The Load() method is much simpler. Each query is a single, easy, straightforward SELECT statement against a single table. These are much easier in every possible way, except you have to do many of them (possibly many times more). If you have nested collections of collections, you may even need to loop through your top level objects and Load their sub-objects. It can get out of hand.

Quick rule-of-thumb

Try to avoid having any more than three Include calls in a single query. I find that EF’s queries get too ugly to recognize beyond that; it also matches my rule-of-thumb for SQL Server queries, that up to four JOIN statements in a single query works very well, but after that it’s time to consider refactoring.

However, all of that is only a starting point.

It depends on your schema, your environment, your data, and many other factors.

In the end, you will just need to try it out each way.

Pick a reasonable “default” pattern to use, see if it’s good enough, and if not, optimize to taste.

More Related Contents:

  1. Decimal precision and scale in EF Code First
  2. Ignoring a class property in Entity Framework 4.1 Code First
  3. What difference does .AsNoTracking() make?
  4. What is the purpose of self tracking entities?
  5. How can I automatically filter out soft deleted entities with Entity Framework?
  6. LINQ to Entities does not recognize the method ‘Double Parse(System.String)’ method, and this method cannot be translated into a store expression
  7. Unable to update the EntitySet – because it has a DefiningQuery and no element exist
  8. Why is .Contains slow? Most efficient way to get multiple entities by primary key?
  9. Entity Framework – Code First – Can’t Store List
  10. Entity Framework/Linq EXpression converting from string to int
  11. LINQ to Entities only supports casting EDM primitive or enumeration types with IEntity interface
  12. How to compare only Date without Time in DateTime types in Linq to SQL with Entity Framework?
  13. linq to entities doesn’t recognize a method
  14. How to convert an expression tree to a partial SQL query?
  15. Enable Migrations with Context in Separate Assembly?
  16. Entity Framework – Cannot convert lambda expression to type ‘string’ because it is not a delegate type
  17. Internal .NET Framework Data Provider error 1025
  18. Include with FromSqlRaw and stored procedure in EF Core 3.1
  19. How to filter data using Entity Framework in a way that DataGridView be editable and context track changes?
  20. How can I disable model compatibility checking in Entity Framework 4.3?
  21. Could not load file or assembly Microsoft.Extensions.DependencyInjection.Abstractions, Version=1.1.0.0
  22. Entity Framework Code Only error: the model backing the context has changed since the database was created
  23. How to compare only Date without Time in DateTime types?
  24. Set database timeout in Entity Framework
  25. Global setting for AsNoTracking()?
  26. EF5 Getting this error message: Model compatibility cannot be checked because the database does not contain model metadata
  27. Remove item from list based on condition
  28. Change db table name in EF4 (entity framework 4)
  29. Repository Pattern, POCO, and Business Entities
  30. Net Core: Using OData in Onion Architecture, Convert Query Parameters Into Linq

Leave a Comment