Entity Framework & Linq performance problem

Question

I have a performance problem with Entity Framework and Linq, when paging a list of Product objects:

var data =_service.GetAll(); 
var page = data.Skip((index) * pageSize).Take(pageSize);
list.Add(page.AsEnumerable); // ** its slow right here

There are 1958 products in my test database, but when the above code runs I can see 3916 (that's 1958 *2) separate queries executed (by looking at the sql profiler).

The Product class looks something like:

public class Product 
{
    public virtual int Id {get;set;}
    public virtual string ProductCode {get;set;}
    //..etc other properties
    public virtual ICollection<WarehouseProduct> WarehouseProducts { // etc }
    public virtual ICollection<InvoiceLine> InvoiceLines { // etc }
    // etc other navigation properties
}

In the sql profiler I can see this query executed 3916 times:

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[ProductId] AS [ProductId], 
// etc
FROM [dbo].[WarehouseProducts] AS [Extent1]

What have I done wrong? The Product object has 12 different navigation properties, but it was only WarehouseProduct was queried 3916 times. Note that there is no WHERE clause on that query, but there is a foreign key relationship between the two tables (that's why it is a navigation property)

Answer 1

You must be accessing Product.WarehouseProducts after you get the products, so if you're using Entities, you want to use Products.Include("WarehouseProduct").Include("InvoiceLine") inside your GetAll() method, which will tell Entities to retrieve the data in the same query.

Related entities are lazy-loaded by default, so if you don't use Include() to specify which related entities to include in your results, then each time you access the related entity in your code, you will trigger another database lookup.