How many Include I can use on ObjectSet in EntityFramework to retain performance?

by

A query with includes returns a single result set and the number of includes affect how big data set is transfered from the database server to the web server. Example:

Suppose we have an entity Customer (Id, Name, Address) and an entity Order (Id, CustomerId, Date). Now we want to query a customer with her orders:

var customer = context.Customers
                      .Include("Orders")
                      .SingleOrDefault(c => c.Id == 1);

The resulting data set will have the following structure:

 Id | Name | Address | OrderId | CustomerId | Date 
---------------------------------------------------
  1 |  A   |   XYZ   |    1    |     1      | 1.1.
  1 |  A   |   XYZ   |    2    |     1      | 2.1.

It means that Cutomers data are repeated for each Order. Now lets extend the example with another entities – ‘OrderLine (Id, OrderId, ProductId, Quantity)andProduct (Id, Name)`. Now we want to query a customer with her orders, order lines and products:

var customer = context.Customers
                      .Include("Orders.OrderLines.Product")
                      .SingleOrDefault(c => c.Id == 1);

The resulting data set will have the following structure:

 Id | Name | Address | OrderId | CustomerId | Date | OrderLineId | LOrderId | LProductId | Quantity | ProductId | ProductName
------------------------------------------------------------------------------------------------------------------------------
  1 |  A   |   XYZ   |    1    |     1      | 1.1. |     1       |    1     |     1      |    5     |    1      |     AA
  1 |  A   |   XYZ   |    1    |     1      | 1.1. |     2       |    1     |     2      |    2     |    2      |     BB
  1 |  A   |   XYZ   |    2    |     1      | 2.1. |     3       |    2     |     1      |    4     |    1      |     AA
  1 |  A   |   XYZ   |    2    |     1      | 2.1. |     4       |    2     |     3      |    6     |    3      |     CC

As you can see data become quite a lot duplicated. Generaly each include to a reference navigation propery (Product in the example) will add new columns and each include to a collection navigation property (Orders and OrderLines in the example) will add new columns and duplicate already created rows for each row in the included collection.

It means that your example can easily have hundreds of columns and thousands of rows which is a lot of data to transfer. The correct approach is creating performance tests and if the result will not satisfy your expectations, you can modify your query and load navigation properties separately by their own queries or by LoadProperty method.

Example of separate queries:

var customer = context.Customers
                      .Include("Orders")
                      .SingleOrDefault(c => c.Id == 1);
var orderLines = context.OrderLines
                        .Include("Product")
                        .Where(l => l.Order.Customer.Id == 1)
                        .ToList();

Example of LoadProperty:

var customer = context.Customers
                      .SingleOrDefault(c => c.Id == 1);
context.LoadProperty(customer, c => c.Orders);

Also you should always load only data you really need.

Edit: I just created proposal on Data UserVoice to support additional eager loading strategy where eager loaded data would be passed in additional result set (created by separate query within the same database roundtrip). If you find this improvement interesting don’t forget to vote for the proposal.

More Related Contents:

  1. How to avoid Query Plan re-compilation when using IEnumerable.Contains in Entity Framework LINQ queries?
  2. ASP.NET MVC 2.0 Implementation of searching in jqgrid
  3. LINQ to Entities does not recognize the method
  4. Dynamic where clause (OR) in Linq to Entities
  5. How to create LINQ Expression Tree to select an anonymous type
  6. ‘Contains()’ workaround using Linq to Entities?
  7. “A lambda expression with a statement body cannot be converted to an expression tree”
  8. Scalable Contains method for LINQ against a SQL backend
  9. LINQ to Entities does not recognize the method ‘System.String Format(System.String, System.Object, System.Object)’
  10. Bulk-deleting in LINQ to Entities
  11. The type of one of the expressions in the join clause is incorrect in Entity Framework
  12. Entity framework left join
  13. Convert String to Int in EF 4.0
  14. LEFT JOIN in LINQ to entities?
  15. How to compare only date components from DateTime in EF?
  16. “The LINQ expression node type ‘Invoke’ is not supported in LINQ to Entities” – stumped!
  17. Entity Framework query slow, but same SQL in SqlQuery is fast
  18. Instantiating a context in LINQ to Entities
  19. Linq To Entities – how to filter on child entities
  20. Linq to Entity Join table with multiple OR conditions
  21. ‘Date’ is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported
  22. Entity Framework 4 Single() vs First() vs FirstOrDefault()
  23. Dynamic query with OR conditions in Entity Framework
  24. Calling a SQL User-defined function in a LINQ query
  25. The LINQ expression node type ‘ArrayIndex’ is not supported in LINQ to Entities
  26. Entity Framework Skip/Take is very slow when number to skip is big
  27. Debugging Entity Framework SQL statements
  28. Why is LINQ JOIN so much faster than linking with WHERE?
  29. What’s the fastest way to merge/join data.frames in R?
  30. In SQLite, do prepared statements really improve performance?

Leave a Comment