EF Core 3.1 - The LINQ expression could not be translated (left joins with group by)

Question

var query = await (from funnel in _readerBaseQuery
                        join lead in _readerContext.Leads on funnel.Id equals lead.SalesFunnelId into leadFunnelGroup
                        from leadFunnel in leadFunnelGroup.DefaultIfEmpty()
                        join leadProduct in _readerContext.LeadProducts on leadFunnel.Id equals leadProduct.LeadId into leadProductGroup
                        from product in leadProductGroup.DefaultIfEmpty()
                        group new { LeadId = leadFunnel.Id, TotalFirst = product.TotalFirst } by leadFunnel.SalesFunnelStageId into newGroup
                        select new { 
                            SalesFunnelStageId = (long)newGroup.Key, 
                            LeadsCount = newGroup.Select(x => x.LeadId).Count(),
                            LeadsValue = newGroup.Select(x => x.TotalFirst).Sum()
                        })
                        .ToListAsync();

I'm trying to a simple group by operation in my API/repository layer using EF Core and LINQ and it throws the error like below:

The LINQ expression '(GroupByShaperExpression:
KeySelector: (l.SalesFunnelStageId), 
ElementSelector:new { 
    LeadId = (ProjectionBindingExpression: LeadId), 
    TotalFirst = (ProjectionBindingExpression: TotalFirst)
 }
)
.Select(x => x.LeadId)' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

The SQL query is like below which I tried in MySQL and it works fine Query executed in MySQL

 select l.SalesFunnelStageId, count(distinct l.Id) as LeadsCount, sum(lp.TotalFirst) as LeadsTotal
    from salesfunnels f
    left join leads l on l.SalesFunnelId = f.Id
    left join leadproducts lp on lp.LeadId = l.id
    group by l.SalesFunnelStageId

Is there something wrong with the way the LINQ query or how the group by is written in C#?

user_10646316 · Answer

You have to change grouping projection to be translatable. Select with grouped items is not supported. Count by not nullable filed also not supported till EF Core 5.x and can be emulated by Sum.

var query =
    from funnel in _readerBaseQuery
    join lead in _readerContext.Leads on funnel.Id equals lead.SalesFunnelId into leadFunnelGroup
    from leadFunnel in leadFunnelGroup.DefaultIfEmpty()
    join leadProduct in _readerContext.LeadProducts on leadFunnel.Id equals leadProduct.LeadId into leadProductGroup
    from product in leadProductGroup.DefaultIfEmpty()
    group new { leadFunnel, product } by leadFunnel.SalesFunnelStageId into newGroup
    select new { 
        SalesFunnelStageId = (long)newGroup.Key, 
        LeadsCount = newGroup.Sum(x => (int?)x.leadFunnel.LeadId != null : 1 : 0),
        LeadsValue = newGroup.Sum(x => x.product.TotalFirst)
    };

EF Core 3.1 - The LINQ expression could not be translated (left joins with group by)

1 Answers