EFCore 3 GroupBy+ToDictionary raises client side GroupBy is not supported

c# ef-core-3.0 entity-framework-core linq

Question

The following GroupBy query cannot be translated and will raise an error: client side GroupBy is not supported

IEnumerable<int> ids = new List<int> { 1, 2, 3 };
var q = db.Comments.Where(x => ids.Contains(x.Identifier))
    .GroupBy(x => x.Identifier);
var map = await q.ToDictionaryAsync(x => x.Key, x => x.Count());

How should the query be rearranged to be able to run on the dbms?

1
0
12/14/2019 2:45:11 AM

Accepted Answer

After any group by, there should be a Select statement which uses only the Group Key (and it's properties) and aggregates. This is similar to the limitation in Sql languages. As an optimization, the EF core team could possibly support calling ToDictionary with the same limitation, but they've not, so we need to do it manually:

IEnumerable<int> ids = new List<int> { 1, 2, 3 };
var q = db.Comments.Where(x => ids.Contains(x.Identifier))
    .GroupBy(x => x.Identifier)
    .Select(x => new { x.Key, Count = x.Count()});
var map = await q.ToDictionaryAsync(x => x.Key, x => x.Count);

This will be translated successfully.

0
12/14/2019 2:45:59 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow