Group By and To Dictionary in EF Core 3.1

ef-core-3.1 entity-framework-core linq

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?

1
1
3/26/2020 4:27:03 PM

Accepted 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)
1
3/26/2020 4:20:06 PM


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