Common Pitfalls with Include operator

Problem: What are some common gotchas and incorrect usages of Include operator that EF developer must be aware of when writing eager loading queries.

Solution: Include operator offers eager loading of related entities. However if the related end is the many side of the relationship, then EF would bring redundant data to application server memory and then based on meta data would remove redundancy and build the complete object graph. So Include would offer good performance when the related end is one side of the

relationship because then there would not be any duplication of data. Having too many include can complicate the query causing the query timeout. You should evaluate the scenario and decide if lazy loading related entities would offer better performance in contrast to eager loading.

When Include is used to eagerly load related enteritis on derived entities, you must use OfType operator to reach to derive entity before you can apply include operator otherwise EF will try to look for a navigation property on base entity which would not exist and cause runtime errors.

When Include is used with an entity that participate in an existing query such as join or nested from clause, EF will lose the include operation and the related entities would not be eagerly loaded. To ensure Include works correctly, ensure that Include is the last operation performed on the query.

If Include is applied on an entity that participates in a query but the final projection of the query, the select statement is an anonymous type, EF will silently drop the include statements. The reason is anonymous type results in ObjectQuery<DbDataRecord> and Include only works when the return type is entities.

Currently in version 1 release of EF, there is no way to filter entity or entities defined inside the include statement. If you have a need to filter related

entities, consider changing the query to use anonymous type and inside the select portion of the query, load both the original entity and the filtered

related entity. Then using the Attach method, attach the related entity back to the original entity.

Discussion: On the above solution, I mentioned common pitfalls I perceive developers may run into. For instance, you have to take caution when you include a navigation property that is many side of the relationship because

this would lead to redundant data brought from the database. The code below retrieves all the addresses for a customer.

var db = new OneToManyEntities();

var customer = db.Customer

.Include("Addresses")

.First(c => c.ContactName == "Zeeshan");

Console.WriteLine("Customer:{0} Total Addresses:{1}", customer.ContactName,customer.Addresses.Count());

Since a customer has many addresses, EF will flatten the query to retrieve customer and its address in one single query. To identity the problem of

redundancy, I have captured the sql statement that was executed for the above linq query.

SELECT ..

FROM ( SELECT ..,

CASE WHEN ([Extent2].[AddressId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2],

CASE WHEN ([Extent2].[AddressId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C3]

WHERE N'Zeeshan' = [Extent1].[ContactName] ) AS [Limit1]

LEFT OUTER JOIN [onetomany].[Address] AS [Extent2] ON [Limit1].[CustomerId] = [Extent2].[CustomerId]

) AS [Project2]

ORDER BY [Project2].[CustomerId] ASC, [Project2].[C3] ASC

Screen shot below shows the above query executed on the sql server.

Although there is only one contact Zeeshan, from the above result you can see that Zeeshan is repeated twice because there are two addresses which results in duplication of data. You can imagine duplication of data could increase rapidly if we start to introduce more includes that are many side of the relationship. So beware when using includes that sometimes lazy loading a

collection may outperform eager loading a collection that could potentially have lot of redundant data.

If you are performing any grouping operation in a linq query, you must use Include as the last operation. For instance the code below shows an incorrect and correct usage of grouping.

var db = new NorthwindEFEntities();

//incorrect query Include is lost

The above commented linq query will not cause Category to be eagerly loaded with Product entity because when we apply grouping operation, Include is lost. To ensure that eager loading works as desired make sure that Include is the last operation performed on the query. Not all query operators cause this behavior with Include. For instance if you use orderby or where operators, Include works correctly when you use it early in the query.

When include is used with nested from clause, Include operator is also as well. Code below shows an example of that.

//nested from clause would also cause the include to get lost var db = new NorthwindFullEntities();

Console.WriteLine("OrderID:{0}

Customer:{1}",order.OrderID,order.Customer.ContactName);

}

The commented out query where we are eagerly loading Customer for an Order, does not work as expected and we do not get the customer for an order.

However the next query applies the Include operator as the last operation which ensures that customer for the Order is loaded.

Another subtle case to be aware of is the join clause. Join clause meets the same fate where using Include early in the query results in losing the Include operation. Code below shows an example of that.

var db = new NorthwindFullEntities();

var ods = from od in db.Order_Details

Code above shows the correct usage where I am loading product information for every order detail item. As suggested the correct usage is to ensure that Include is the last operation on the query.

What if we wanted to filter the related collection being eagerly loaded?

Currently Include operator does not have any option to filter on the included collection. If you have requirement which the related collection must be filtered, then you should consider return an anonymous type that contains the original entity as well the related collection. Code below shows an example of using anonymous type to filter the related collection.

var db = new NorthwindFullEntities();

var cat = from c in db.Categories

var prods = cat.SelectMany(c => c.Products).AsEnumerable();

beverage.Products.Attach(prods);

Console.WriteLine("Category:{0} TotalProducts:{1}",

beverage.CategoryName,beverage.Products.Count());

On the above code, I am retrieving the Category and its related products.

Since I am only interested in related products where supplierid equal to one, I am apply the where to filter the Products returned for a given category. Then using the Select operator, I retrieve the first category from the anonymous types. Similarly to retrieve the Products I use SelectMany operator to fetch the product collection from the anonymous type. To build the complete graph in memory I attach the products retrieved from the anonymous type to the product collection of the category. This is one of the ways you can return a partial collection for products for a given category.

3.2 Using Load Operator to Lazy Load Collection