CODEWITHSUNDEEP
c#linqentity-framework-coreef-core-3.0
Alireza
Alireza

Reputation: 5503

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

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?

Upvotes: 3

Views: 2196

Answers (1)

Alireza
Alireza

Reputation: 5503

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.

Upvotes: 3

Related Questions