实体框架与Linq性能问题 [英] Entity Framework & Linq performance problem

问题描述

在分页产品对象列表时，我遇到了Entity Framework和Linq的性能问题:

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

我的测试数据库中有1958个产品，但是运行上述代码时，我可以看到执行了3916个(即1958 * 2)单独的查询(通过查看sql profiler).

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).

Product类类似于:

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
}

在sql profiler中，我可以看到此查询执行了3916次:

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]

我做错了什么? Product对象具有12个不同的导航属性，但仅对WarehouseProduct进行了3916次查询.请注意，该查询上没有WHERE子句，但是两个表之间存在外键关系(这就是为什么它是导航属性的原因)

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)

推荐答案

获得产品后，您必须正在访问Product.WarehouseProducts，因此 如果您使用的是实体，则要使用Products.Include("WarehouseProduct").Include("InvoiceLine") 在您的GetAll()方法中，该方法将告诉实体在同一查询中检索数据.

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.

默认情况下相关实体是延迟加载的，因此，如果您不使用Include()指定要在结果中包括哪些相关实体，那么每次在代码中访问相关实体时，都会触发另一个数据库查找.

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.

这篇关于实体框架与Linq性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！