I am in the process of upgrading to EF Core 3.0 and .NET Core 3.0, but some of my queries stopped working. Here is an example:
I have a table called Bins
, I have another table which is called BinItems
, now it has, of course, a one to many relationship. BinItems
has a property called Qty
, and I want to sum up all the Qty
from BinItems
based on criteria given by the client in a filter.
So here is the code:
var query = _binRepository.Table;
if (filter.LastRecountDate != null) {
query = query.Where(x => x.LastRecountDate.Date == filter.LastRecountDate.Value.Date);
}
if (filter.StartRecountDate != null) {
query = query.Where(x => x.LastRecountDate.Date >= filter.StartRecountDate.Value.Date);
}
if (filter.EndRecountDate != null) {
query = query.Where(x => x.LastRecountDate.Date <= filter.EndRecountDate.Value.Date);
}
if (filter.Active != null) {
query = query.Where(x => x.Active == filter.Active);
}
if (!string.IsNullOrEmpty(filter.BinLocation)) {
query = query.Where(x => x.BinLocation == filter.BinLocation);
}
if (!string.IsNullOrEmpty(filter.Gtin)) {
query = query.Where(x => x.BinItems.Any(o => o.UPC == filter.Gtin));
}
if (filter.WarehouseIds.Count() > 0) {
query = query.Where(x => filter.WarehouseIds.Contains(x.Zone.Id));
}
if (!string.IsNullOrEmpty(filter.Keywords)) {
query = query.Where(x => x.BinItems.Select(o => o.UPC).Contains(filter.Keywords));
}
query = query.Include(x => x.BinItems).Include(x => x.Zone);
if (!string.IsNullOrEmpty(filter.Keywords)) {
return await query.SumAsync(x => x.BinItems.Where(p => p.UPC.Contains(filter.Keywords)).Sum(o => o.Qty));
}
return await query.SumAsync(x => x.BinItems.Sum(o => o.Qty));
I get an exception thrown:
Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot perform an
aggregate function on an expression containing an aggregate or a
subquery.
It worked perfectly OK in .NET Core 2.1 and EF Core 2, but now I keep getting these errors in all my queries that I do this way.
Any idea how I can get this work in .NET Core 3.0/EF Core 2?
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…