Entity Framework & Linq performance problem

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> I开发者_JAVA百科nvoiceLines { // 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)

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.

The page.AsEnumerable is causing the queried sequence to be evaluated and materialized. The previous statements, assuming Lazy Loading is still enabled and you are querying an SQL data source, are setting up the conditions that will make up the SQL statement to execute.

You didn't post the GetAll() method, so that may be the source of your additional records.

The SQL query that was generated should have a TOP as part of the select, and it should be returning an resultset with multiple records, so you may not be profiling the correct query.