GroupJoin: exception thrown: System.InvalidOperationException

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
    });