CODEWITHSUNDEEP
linqentity-framework-coreef-core-3.1
Marshall Tigerus
Marshall Tigerus

Reputation: 3764

Group By and To Dictionary in EF Core 3.1

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?

Upvotes: 5

Views: 4666

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205539

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)

Upvotes: 8

Related Questions