Group join in EF Core 3.1

Question

I am trying to group join in EF core 3.1 the problem it returns

Processing of the LINQ expression 'DbSet failed. This may indicate either a bug or a limitation in EF Core

my code is like this

 var employees = await (from enrollment in RepositoryContext.Enrollments
                join allowance in RepositoryContext.Allowances.Include(y=>y.AllowanceType) on enrollment.EmployeeId equals allowance.EmployeeId
                    into allowances

                select new
                {
                    enrollment,
                    allowances

                }
            ).AsNoTracking().ToListAsync();

the allowances is list of items , is there any workaround to run the query like this cause i need it for better berformance.

Answer 1

Here Query with GroupBy or GroupJoin throws exception is the now closed GitHub issue/discussion where I was trying to convince EF Core team to add GroupJoin translation. They refused to do that and opened the useless Query: Support GroupJoin when it is final query operator #19930 where I continue the fight for such translation. So please go there and comment/vote up for the full translation request.

You will also find there the workaround - instead of unsupported GroupJoin use the equivalent supported correlated subquery approach, e.g. replace

join allowance in RepositoryContext.Allowances.Include(y => y.AllowanceType)
    on enrollment.EmployeeId equals allowance.EmployeeId
into allowances

with

let allowances = RepositoryContext.Allowances.Include(y => y.AllowanceType)
    .Where(allowance => enrollment.EmployeeId == allowance.EmployeeId)