LINQ to Entities Any() and Contains() slow with small list

Question

I'm using EF 6 to get products from a database. The product categories are mapped as a navigation property on products, and the data is from a ProductCategory pivot table. The categories work like a tree (ie. every category can have sub-categories), but only the most specific product-subcategory relationship is stored in the pivot table. For example, suppose there is category path like this:

Electronics > Audio > Amplifiers > Integrated Amps.

A product that is an integrated amplifier has a record in the pivot table with its product ID and the Integrated Amps category ID.

I need to filter by category, but the product should show up even if filtering by a parent category, e.g. an integrated amplifier should show up in a list of Amplifiers. So first I make a List of the relevant category IDs. (This involves a separate query to the categories table, but it doesn't take long.) If the category filter is Amplifiers, the list is the ID of Amplifiers and the ID of Integrated Amps.

The problem is that the products query takes 10-20 times longer when I include the filter:

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();
}

I would expect that combination of Any() and Contains() to slow down quickly with large numbers of records, but I'm working with 22 items in products, 1-3 items in pl.categories, and 1-5 items in currentCategoryIdAndChildren. I'm surprised that with so few records it's slower by an order of magnitude. At this rate I'm better off filtering it client side, even though it means bringing back a lot of unnecessary records.

Is there something I'm missing? Is there another approach?

UPDATE: Express Profiler reports that the database query itself only takes 3ms, so I am guessing the performance has something to do with how Entity Framework works. Certainly it is slowest the very first time the LINQ is run (I know it needs to compile the query), but it's still relatively slow on subsequent calls.

Answer 1

I tried many different things and finally found a solution.

I believe the main slowdown happened when EF was translating the Contains() into a SQL query. The most noticeable thing, however, was that it did not appear to cache the query. From what I can gather, this is because the list of category IDs (currentCategoryIdAndChildren) was generated outside of EF, so it assumed it would be different every time.

I was able to speed things up by using the PredicateBuilder in LINQKit. This allowed me to create the logic more explicitly:

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

This got me a bit better performance with my initial query, and MUCH better performance with subsequent queries.