Group By and To Dictionary in EF Core 3.1

Question

We have updated a service from .Net Core 2.1 to 3.1, while reevaluating queries that broke or became slower during the transition, we came across this query:

_context.InboundRecords.GroupBy(x => x.State.ToString()).ToDictionary(x => x.Key, x => x.Count())

To make this "work" with the 3.1 breaking changes, we add a to list between the DBSet and the Group By

_context.InboundRecords.ToList().GroupBy(x => x.State.ToString()).ToDictionary(x => x.Key, x => x.Count())

The issue here is that this brings the entirety of the InboundRecords db set into memory before doing the grouping. This is identical to the way 2.1 worked, but there has to be a better way of doing this. Can we tweak this query to only bring back the state and the count of records in that state?

user_5202563 · Answer

Can we tweak this query to only bring back the state and the count of records in that state?

Sure we can, by (1) using server side GroupBy with intermediate projection containing only keys / aggregates, then (2) convert it client side to the desired shape:

_context.InboundRecords
    .GroupBy(x => x.State.ToString())
    .Select(g => new { g.Key, Count = g.Count() }) // (1)
    .ToDictionary(x => x.Key, x => Count); // (2)

Group By and To Dictionary in EF Core 3.1

1 Answers