LINQ to Entities的Any()和Contains()在小列表下运行缓慢

Question

我正在使用EF 6从数据库获取产品。产品类别被映射为产品的导航属性，数据来自ProductCategory透视表。这些类别像树一样工作（即每个类别都可以有子类别），但只有最具体的产品-子类别关系存储在透视表中。例如，假设有这样的类别路径：

电子产品 > 音频 > 功放 > 集成功放。

作为集成功放器的产品在透视表中有一条记录，记录了它的产品ID和集成功放类别ID。

我需要按类别筛选，但即使按父级类别进行筛选，产品也应该出现在列表中，例如，一个集成功放器应该出现在功放器列表中。因此，首先我制作一个相关类别ID列表。（这涉及到对类别表的单独查询，但不会花费太长时间。）如果类别过滤器是功放器，则列表是功放器的ID和集成功放器的ID。

问题是，当我包含筛选器时，产品查询需要10-20倍的时间：

List<int> currentCategoryIdAndChildren = BuildCategoryIdList(currentCategoryId);

using (var db = new myContext())
{
    var products = db.Products
        .Select(p => new Product_PL
        {
            id = p.ID,
            name = p.Name,
            description = p.Description,
            categories = p.Categories
                        .Select(c => new Category_PL
                        {
                            categoryid = c.ID,
                        }),
        });

    // Filter by category
    products = products.Where(pl => pl.categories.Any(c => currentCategoryIdAndChildren.Contains(c.categoryid)));

    // Other filters, sorting, and paging here

    rptProducts.DataSource = products.ToList(); // Database call is made here
    rptProducts.DataBind();
}

我希望使用Any()和Contains()的组合可以快速处理大量记录，但是在products中只有22个项目，在pl.categories中只有1-3个项目，在currentCategoryIdAndChildren中只有1-5个项目。我很惊讶，即使只有这么少的记录，它也比其它方法慢了一个数量级。以这种速度，我最好在客户端进行过滤，尽管这意味着返回了很多不必要的记录。

我是否遗漏了什么？还有其他方法吗？

更新：Express Profiler报告数据库查询本身仅花费3ms，因此我猜测性能与Entity Framework的工作方式有关。确切地说，第一次运行LINQ时它是最慢的（我知道它需要编译查询），但是即使在后续调用中，它仍然相对较慢。

- MysteriousWhisper

---

在选择时应同时进行筛选。当您在第二行调用“products.Where”时，它首先必须枚举产品。将您的Where子句移动到第一个LINQ调用的末尾。 - DLeh

@DLeh 你确定那是正确的吗？第一个projection实际上并没有实现，所以我希望SQL代码生成足够智能。检查的一种方法是记录生成的SQL。 - Daniel Kelley

我不确定，但这是我会尝试的第一件事。 - DLeh

@Dleh 只有在实际访问时（例如使用 .ToList() 或 foreach），产品才会被枚举。我已经验证了 SQL 调用直到那时才会进行。除了上面的代码之外，我实际上还有几个其他条件过滤器以及分页，这最终会导致一个巨大的过滤 SQL 调用。但是我会尝试移动 Where 条件语句来测试一下。 - MysteriousWhisper

整个操作需要多长时间（不仅是 SQL 查询）？如果只是调用 ToList 而没有将其分配为 DataSource，是否有任何区别？ - SoftwareFactor

Answer 1

我尝试了许多不同的方法，最终找到了一个解决方案。

我认为主要的减速发生在EF将Contains()翻译成SQL查询时。然而，最明显的问题是它似乎没有缓存查询。据我所知，这是因为类别ID列表（currentCategoryIdAndChildren）是在EF之外生成的，因此它假定每次都会有所不同。

我通过使用LINQKit中的PredicateBuilder来加速处理。这使我能够更明确地创建逻辑：

var IsInCategory = PredicateBuilder.False<Product_PL>();

foreach (int categoryID in currentCategoryIdAndChildren)
{ IsInCategory = IsInCategory.Or(pl => pl.categories.Any(c => categoryID == c.categoryid)); }

products = products.Where(IsInCategory);

这使得我的初始查询性能有所提升，并且随后的查询性能大大提高。

- MysteriousWhisper

Answer 2

首先尝试过滤产品，然后再形成您的模型（Product_PL和Category_PL）：

var filteredProducts = db.Products.Where(p => p.Categories.Any(c => currentCategoryIdAndChildren.Contains(c.ID)))
    .Select(p => new Product_PL
    {
        id = p.ID,
        name = p.Name,
        description = p.Description,
        categories = p.Categories
                    .Select(c => new Category_PL
                    {
                        categoryid = c.ID,
                    }),
    });

- SoftwareFactor

---

无论我把Where放在哪里，性能都是一样的，尽管确切的SQL略有不同。我已经更新了我的问题，以澄清数据库调用何时进行。 - MysteriousWhisper