ef core 5.0 How to Filtered Include

Question

I'm trying to use the new function Filtered on Include but I'm not getting it right somehow. https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-5.0/whatsnew#preview-3

I got a product with categories. I need to filter Products on Category title/name. It's a many to many relations.

The category title should contain "Vlees". But somehow ALL 6 products in the database get returned, even with the wrong Category.Title name.

var products = await _context.Product
   .Include(c => c.ProductCategories.Where(c => c.Category.Title.Contains(containsTitle)))
   .ThenInclude(c => c.Category)
   .ToListAsync();

Answer 1

But somehow ALL 6 products in the database get returned

That's because you're querying _context.Product. Filtered Include is for filtering child collections, not the main query. Therefore you get all products, but all of these products should only contain categories that pass the filter.

You seem to expect that EF only returns product that have categories containing "Vlees" in their names, but that's not the purpose of filtered Include. To achieve that, you have to filter the products themselves:

_context.Product
    .Where(p => p.ProductCategories.Any(c => c.Category.Title.Contains(containsTitle)))

This can be combined with the filtered Includes, but not necessarily. If you don't filter the Includes you get products filtered by category name, but containing all categories in their collections.

So filtered Include gives you the freedom to filter the query result and child collections separately.