EF Core Linq-to-Sql GroupBy SelectMany not working with SQL Server

Question

I am trying the following Linq with LinqPad connecting to SQL Server with EF Core:

MyTable.GroupBy(x => x.SomeField)
       .OrderBy(x => x.Key)
       .Take(5)
       .SelectMany(x => x)

I get this error:

The LINQ expression 'x => x' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explic...

However, this works:

MyTable.AsEnumerable()
       .GroupBy(x => x.SomeField)
       .OrderBy(x => x.Key)
       .Take(5)
       .SelectMany(x => x)

I was under the impression that EF Core should be able to translate such an expression.

Am I doing anything wrong?

Steve Py · Answer 1 · 2021-09-13T05:13:00.447

That exception message is an EF Core message, not EF6.

In EF 6 your expression should work, though with something like a ToList() on the end. I suspect the error you are encountering is that you may be trying to do something more prior to materializing the collection, and that is conflicting with the group by SelectMany evaluation.

For instance, something like this EF might take exception to:

var results = MyTable
    .GroupBy(x => x.SomeField)
    .OrderBy(x => x.Key)
    .Take(5)
    .SelectMany(x => x)
    .Select(x => new ViewModel { Id = x.Id, Name = x.Name} )
    .ToList();

where something like this should work:

var results = MyTable
    .GroupBy(x => x.SomeField)
    .OrderBy(x => x.Key)
    .Take(5)
    .SelectMany(x => x.Select(y => new ViewModel { Id = y.Id, Name = y.Name} ))
    .ToList();

You don't want to use:

MyTable.AsEnumerable(). ...

As this is materializing your entire table into memory, which might be ok if the table is guaranteed to remain relatively small, but if the production system grows significantly it forms a cascading performance decline over time.

Edit: Did a bit of digging, credit to this post as it does look like another limitation in EF Core's parser. (No idea how something that works in EF6 cannot be successfully integrated into EF Core... Reinventing wheels I guess)

This should work:

var results = MyTable
    .GroupBy(x => x.SomeField)
    .OrderBy(x => x.Key)
    .Take(5)
    .Select(x => x.Key)
    .SelectMany(x => _context.MyTable.Where(y => y.Key == x))
    .ToList();

So for example where I had a Parent and Child table where I wanted to group by ParentId, take the top 5 parents and select all of their children:

var results = context.Children
    .GroupBy(x => x.ParentId)
    .OrderBy(x => x.Key) // ParentId
    .Take(5)
    .Select(x => x.Key) // Select the top 5 parent ID
    .SelectMany(x => context.Children.Where(c => c.ParentId == x)).ToList();

EF pieces this back together by doing a SelectMany back on the DbSet against the selected group IDs.

Credit to the discussions here: How to select top N rows for each group in a Entity Framework GroupBy with EF 3.1

Edit 2: The more I look at this, the more hacky it feels. Another alternative would be to look at breaking it up into two simpler queries:

var keys = MyTable.OrderBy(x => x.SomeField)
    .Select(x => x.SomeField)
    .Take(5)
    .ToList();

var results = MyTable.Where(x => keys.Contains(x.SomeField))
    .ToList();

I think that translates your original example, but the gist is to select the applicable ID/Discriminating keys first, then query for the desired data using those keys. So in the case of my All children from the first 5 parents that have children:

 var parentIds = context.Children
      .Select(x => x.ParentId)
      .OrderBy(x => x)
      .Take(5)
      .ToList();
 var children = context.Children
    .Where(x => parentIds.Contains(x.ParentId))
    .ToList();

Yes you are correct - EF Core .. does that mean this query will not translate no matter what ? (yes I don't want .AsEnumerable() I just used it to show my syntax is otherwise ok) — kofifus, Sep 13 '21 at 03:41
Can you post the complete query? (I.e. up to the point you call `ToList()` or equivalent. — Steve Py, Sep 13 '21 at 03:57
Added an update with something that should work and a link to further discussions on the limitations in EF Core. — Steve Py, Sep 13 '21 at 04:19
Hmm, this might require injecting a `Distinct()` after the `.Select(x => x.Key)` if somehow the query isn't fully honouring the Group By.. All-in-all this feels like a huge hack to work around something EF6 was able to work out. :) — Steve Py, Sep 13 '21 at 05:04
Added another alternative example to the answer to do it with two simpler queries rather than hacking at EF Core's limitations. — Steve Py, Sep 13 '21 at 05:13
aren't you missing `Distinct()` before `OrderBy` in your new code ? — kofifus, Sep 13 '21 at 05:33
and I am sure how to even put that `Distinct` .. kind of need `DistinctBy` .. also even without it the GroupBy version is many times faster — kofifus, Sep 13 '21 at 05:58
Also one more fix, above `.SelectMany(x => _context.MyTable.Where(y => y.Key == x))` should be `.SelectMany(x => _context.MyTable.Where(y => y.SomeField == x))` — kofifus, Sep 13 '21 at 07:18

score 0 · Answer 2 · answered Sep 13 '21 at 09:20

EF Core has limitation for such query, which is fixed in EF Core 6. This is SQL limitation and there is no direct translation to SQL for such GroupBy.

EF Core 6 is creating the following query when translating this GroupBy.

var results = var results = _context.MyTable
    .Select(x => new { x.SomeField })
    .Distinct()
    .OrderBy(x => x.SomeField)
    .Take(5)
    .SelectMany(x => _context.MyTable.Where(y => y.SomeField == x.SomeField))
    .ToList();

It is not most optimal query for such task, because in SQL it can be expressed by Window Function ROW_NUMBER() with PARTITION on SomeField and additional JOIN can be omitted.

Also check this function, which makes such query automatically.

_context.MyTable.TakeDistinct(5, x => x.SomeField);

EF Core Linq-to-Sql GroupBy SelectMany not working with SQL Server

2 Answers2