I'm trying to write a query to get all restaurant tables if exists or not a opened sale on it. if a sale exists on a table I want to get the sum and couple details.that is my code:
db.SALETABLES
.GroupJoin(
db.SALES.Where(c => c.CLOSEDTIME == null),
t => t.ID,
sa => sa.ID_TABLE,
(ta, s) => new
{
ta.ID,
ta.DESCRIPTION,
NR_SALE = s.Any() ? s.First().NR_SALE : 0,
IDSALE = s.Any() ? s.First().ID : 0,
IDUSER = s.Any() ? s.First().IDUSER : 0,
USERNAME = s.Any() ? s.First().USERS.USERNAME :"" ,
SALESUM = s.Any() ? s.First().SALES_DETAIL.Sum(p => p.PRICE * p.CANT) : 0
}
but got this error:
Exception thrown: 'System.InvalidOperationException' in System.Private.CoreLib.dll
thanks for any help
Since the exception is by the EF Core infrastructure, apparently you are hitting current EF Core implementation bug.
But you can help EF Core query translator (thus avoiding their bugs caused by missing use cases) by following some rules when writing your LINQ to Entities queries. These rules will also eliminate in most of the cases the client evaluation of the query (or exception in EF Core 3.0+).
One of the rules which is the origin of issues with this specific query is - never use First
. The LINQ to Objects behavior of First
is to throw exception if the set is empty. This is not natural for SQL which naturally supports and returns NULL
even for values which normally do not allow NULL
. In order to emulate the LINQ to Objects behavior, EF Core has to evaluate First()
client side, which is not good even if it works. Instead, use FirstOrDefault()
which has the same semantics as SQL, hence is translated.
To recap, use FirstOrDefault()
when you need the result to be a single "object" or null
, or Take(1)
when you want the result to be a set with 0 or one elements.
In this particular case, it's better to incorporate the 0 or 1 related SALE
rule directly into the join subquery, by removing the GroupJoin
and replacing it with SelectMany
with correlated Where
. And the Any()
checks are replaced with != null
checks.
With that said, the modified working and fully server translated query looks like this:
var query = db.SALETABLES
.SelectMany(ta => db.SALES
.Where(s => ta.ID == s.ID_TABLE && s.CLOSEDTIME == null).Take(1), // <--
(ta, s) => new
{
ta.ID,
ta.DESCRIPTION,
NR_SALE = s != null ? s.NR_SALE : 0,
IDSALE = s != null ? s.ID : 0,
IDUSER = s != null ? s.IDUSER : 0,
USERNAME = s != null ? s.USERS.USERNAME : "",
SALESUM = s != null ? s.SALES_DETAIL.Sum(p => p.PRICE * p.CANT) : 0
});