I have Item table(Main) and ItemAlt table(Sub (List)) I want to get all item alt rows where:
ItemAlt.WarehouseId is equal to parameter warehouseId
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?
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;