CODEWITHSUNDEEP
entity-frameworkentity-framework-core
jenson-button-event
jenson-button-event

Reputation: 18941

EF Core GroupBy query throws: could not be translated

Can anyone point me at the faux pas in this EF Core query?

var employers = await iqDbContext.Payrolls
    .GroupBy(p => p.PayeScheme.Employer, p => p)
    .Select(group => new EmployerViewModel
    {
        Id = group.Key.Id,
        Name = group.Key.Name
    })
    .ToListAsync();

throws:

System.InvalidOperationException: The LINQ expression 'DbSet<Payroll>()
    .Join(
        inner: DbSet<PayeScheme>(), 
        outerKeySelector: p => EF.Property<Nullable<int>>(p, "PAYR_fk1_PAYE_SCHEME"), 
        innerKeySelector: p0 => EF.Property<Nullable<int>>(p0, "Id"), 
        resultSelector: (o, i) => new TransparentIdentifier<Payroll, PayeScheme>(
            Outer = o, 
            Inner = i
        ))
    .Join(
        inner: DbSet<Employer>(), 
        outerKeySelector: p => EF.Property<Nullable<int>>(p.Inner, "PychFk1Employer"), 
        innerKeySelector: e => EF.Property<Nullable<int>>(e, "Id"), 
        resultSelector: (o, i) => new TransparentIdentifier<TransparentIdentifier<Payroll, PayeScheme>, Employer>(
            Outer = o, 
            Inner = i
        ))
    .Where(p => p.Inner != null && __accessiblePayrollIds_0.Contains(p.Outer.Outer.Id))
    .GroupBy(
        keySelector: p => p.Inner, 
        elementSelector: p => p.Outer.Outer)' 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/?

Upvotes: 1

Views: 177

Answers (1)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27282

If you need correct grouping, you have to specify which columns to group, not whole object.

var employers = await iqDbContext.Payrolls
    .GroupBy(p => new { p.PayeScheme.Employer.Id, p.PayeScheme.Employer.Name })
    .Select(group => new EmployerViewModel
    {
        Id = group.Key.Id,
        Name = group.Key.Name,
    })
    .ToListAsync();

Upvotes: 1

Related Questions