Query with `groupjoin` cannot be translated although it's documened as being supported

Question

I don't understand why this doesn't translate. It seems to be exactly the use case described here.

The LINQ expression

DbSet<A>()
    .GroupJoin(
        inner: DbSet<B>(),
        outerKeySelector: a => a.AId,
        innerKeySelector: b => b.AId,
        resultSelector: (a, bs) => new {
            a = a,
            bs = bs
         })

produces the error:

could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

The LINQ code producing the exception is

from a in ctx.As
    join b in ctx.Bs on a.aId equals b.aId into bs
    select new {A = a, Bs = bs.ToList()};

Edit: maybe I misunderstood the doc and this is an example of something that does NOT translate.

Executing a query like the following example generates a result of Blog & IEnumerable. Since databases (especially relational databases) don't have a way to represent a collection of client-side objects, GroupJoin doesn't translate to the server in many cases. It requires you to get all of the data from the server to do GroupJoin without a special selector (first query below). But if the selector is limiting data being selected then fetching all of the data from the server may cause performance issues (second query below). That's why EF Core doesn't translate GroupJoin.

But then my question becomes instead : how do I achieve the result I'm looking for without requiring navigational properties ?

Answer 1

The explanation in the linked documentation just follows the EF Core team vision and is ridiculous, because of course it can easily be translated - I had a long discussion with the team here Query with GroupBy or GroupJoin throws exception #17068 and continue here Query: Support GroupJoin when it is final query operator #19930, trying to convince them why it should be supported, with no luck regardless of the arguments.

The whole point is (and that's the current workaround) it can be processed like if it was correlated subquery (SelectMany), which is translated and processed properly (even though the query result shape has no SQL equivalent.

Anyway, the current status is "Needs Design" (whatever that means), and the workaround is to replace the join with correlated subquery (which is what EF Core is using internally when "expanding" collection navigation properties during the query translation).

In your case, replace

join b in ctx.Bs on a.aId equals b.aId into bs

with

let bs = ctx.Bs.Where(b => a.aId == b.aId)

---

However, I highly recommend adding and using navigation properties. Not sure why you "can't use" them, in LINQ to Entities which do not project entities they serve just metadata for relationships, thus produce automatically the necessary joins. By not defining them you just put on yourself unneeded limitations (additionally to EF Core limitations/bugs). In general EF Core works better and support more things when using navigation properties instead of manual joins.

Answer 2

Try this query, it should work with EF Core:

var query =
    from a in ctx.As
    select new {A = a, Bs = ctx.Bs.Where(b => b.Id == a.aId).ToList()};