CODEWITHSUNDEEP
c#entity-frameworkentity-framework-core
Arad
Arad

Reputation: 12734

EF Core simple GroupBy throws an InvalidOperationException exception

I have 2 entities, Course and Purchase, with a one-to-many relationship between them.

I want to get the number of purchases a given course has had on each day in the last 10 days, here's what I've done, nothing too complicated:

dbContext.Courses.Where(c => c.Id == [some-id]).Select(c => new
{
    PurchasesCountInLastTenDays = c.Purchases
        .GroupBy(p => p.PurchasedAt.Date)
        .Select(g => new
        {
            Date = g.Key,
            Count = g.Count(),
        })
        .ToList(),
}).Single();

EF Core (v5.0.9) throws the following exception:

System.InvalidOperationException: 'Unable to translate collection subquery in projection since it uses 'Distinct' or 'Group By' operations and doesn't project key columns of all of it's tables which are required to generate results on client side. Missing column: p.Id. Either add column(s) to the projection or rewrite query to not use 'GroupBy'/'Distinct' operation.'

I googled this error message but couldn't find any relevant information. I have no idea what it means, or what it's telling me I should do.

I've tried changing the column based on which grouping happens, same error. I've even removed the Select(g => ... but it still didn't work.

I'd appreciate your help.

Upvotes: 2

Views: 3890

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205619

It's just one of the (many) current EF Core query limitations. And the suggestion at the and of the error message is a bit funny, since obviously you cannot include key columns in a group by query grouped by something else. Rewriting the query w/o GroupBy / Distinct sometimes is possible, but not with queries which group / distinct by non PK / FK values.

For the sake of correctness, the requested query shape is not that easy to produce as it seems. Because SQL does not support "collection" members of the returned result set - it is flat sequence of uniform data records. You can try doing it by hand using raw ADO.NET and you'll see.

Since the main issue (as indicated by the error message) is the nested collection inside the resulting object(s), the solution in this particular case (if the collection is the only member of the result object) is to flatten the source set before grouping. In other words, returning PurchasesCountInLastTenDays list directly rather than as collection member of anonymous projection. This has direct SQL equivalent and EF Core will be happy to translate and materialize it for you.

i.e.

var coursePurchasesCountInLastTenDays = dbContext.Courses
    .Where(c => c.Id == [some-id]) // filter
    .SelectMany(c => c.Purchases) // flatten
    .GroupBy(p => p.PurchasedAt.Date) // group
    // aggregate
    .Select(g => new
    {
        Date = g.Key,
        Count = g.Count(),
    })
    .ToList();

Upvotes: 4

Related Questions