EF Core 3.1 - Include filtering throw an error 'Lambda expression used inside Include is not valid.'

Question

I have Item table(Main) and ItemAlt table(Sub (List)) I want to get all item alt rows where:

1. ItemAlt.WarehouseId is equal to parameter warehouseId

2. Item.Id is equal to parameter itemId

   public async Task<Item> GetItemWithAlt(int itemId, int warehouseId)
   {
       var query = from i in _dbContext.Item.Include(a => a.ItemAlt.Where(c => c.WarehouseId == warehouseId && c.IsActive == true))
                   where i.IsActive.Equals(true) && i.Id.Equals(itemId)
                   select i;
       return await query.SingleOrDefaultAsync();
   }
   

the problem is it throws the exception "Lambda expression used inside Include is not valid" in

.Where(c => c.WarehouseId == warehouseId && c.IsActive == true)

Do you know how to get around this issue?

• I already tried searching for the error message but the queries are different from mine

Answer 1

Filtered includes are available only since EF Core 5.0 as docs state:

This feature was introduced in EF Core 5.0.

So you need either to update your project to corresponding .NET and EF Core versions or rewrite query to manual join with filtered ItemAlt's.

Or try to select into anonymous type and rely on the relationship fixup. Something along this lines (not tested):

var result = await _dbContext.Item
    .Where(i => i.IsActive.Equals(true) && i.Id.Equals(itemId))
    .Select(i => new 
    {
        Item = i,
        ItemAlts = i.ItemAlt
            .Where(c => c.WarehouseId == warehouseId && c.IsActive == true))
            .ToList()
    }
    .SingleOrDefaultAsync();

return result?.Item;