How do I include attribute in Entity Framework Group By result

Question

Let's say I have a table of locations with location ID and location name. And let's say I want to get the revenues for each location (in this simple scenario I might not even need GroupBy - but please assume that I do!)

var revenues = await _context.SaleTransaction.GroupBy(s => s.LocationId)
    .Select(x => new LocationDTO {
        LocationId = x.Key,
        LocationName = ??? 
        Revenues = x.Sum(i => i.Amount)
    }).ToListAsync();

I tried to cheat

LocationName = x.Select(i => i.Location.LocationName).First()

since all location names for this ID are the same. But EF can't translate First() unless I use AsEnumerable() and bring the whole sales table into application memory.

Or I can traverse the result the second time:

foreach(var revenue in revenues) {
    revenue.LocationName = _context.Location.Find(revenue.LocationId).LocationName;
}

Given that the number of locations is fixed (and relatively small), it may be the best approach. Still, neither going to DB for every location O(n) nor pulling the whole location list into memory doesn't sit well. Maybe there is a way to assign LocationName (and some other attributes) as part of GroupBy statement.

I am using EF Core 5; or if something is coming in EF Core 6 - that would work as well.

I think it's better to start the query from the location table instead of SaleTransaction — Sina Riani, Sep 13 '21 at 06:14
Please let me know, what is the relationship type between Location and SaleTransaction? "one to one" or "one to many"? — Sina Riani, Sep 13 '21 at 06:31
the relationship type between Location and SaleTransaction? Seriously? of course one-to-many — Felix, Sep 13 '21 at 06:36
What might solve your problem is to group by id AND namesee also https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns — Mat, Sep 13 '21 at 06:49

score 1 · Answer 1 · answered Sep 13 '21 at 06:57

1

From what I can briefly see is that you need a linq join query in order to join the searches. With EF linq query it means those won't be loaded into memory until they are used so it would solve the problem with loading the whole table.

You could write something like:

var revenues = await _context.SaleTransactions.Join(_context.Locations, s => s.LocationId, l => l.Id, (s, l) => new {LocationId = s.LocationId, LocationName = l.LocationName, Revenues = s.Sum(i => i.Amount)});

I will link the whole fiddle with the mock of your possible model https://dotnetfiddle.net/BGJmjj

answered Sep 13 '21 at 06:57

RapidRed

39
2

No, join is definitely not the way to go. Navigation properties can (and should) be used. – Gert Arnold Sep 13 '21 at 07:17
1

I like it! I am skeptical to Join in EF- it frequently reflects bad design (I *have* navigation properties from SaleTransaction to Location); but in this case it might be the right approach. Let me play with it a little – Felix Sep 13 '21 at 07:19
@GertArnold In EF Core it is easy to include navigation properties (Especially in .net 5), you can include one more property of type Location and you will anyway have to hold its Id, either in one to may relation so i think it is not that much of an issue – RapidRed Sep 13 '21 at 08:23
What is not an issue? Using join while a nav property is available is an issue to me. – Gert Arnold Sep 13 '21 at 08:32

score 1 · Answer 2 · answered Sep 13 '21 at 07:02

1

You can group by more than one value. eg;

var revenues = await _context.SaleTransaction
    .GroupBy(s => new {
        s.LocationId, 
        s.Location.Name 
    })
    .Select(x => new LocationDTO {
        LocationId = x.Key.LocationId,
        LocationName = x.Key.Name,
        Revenues = x.Sum(i => i.Amount)
    }).ToListAsync();

Though it seems like you are calculating a total per location, in which case you can build your query around locations instead.

var revenues = await _context.Location
    .Select(x => new LocationDTO {
        LocationId = x.Id,
        LocationName = x.Name,
        Revenues = x.SaleTransactions.Sum(i => i.Amount)
    }).ToListAsync();

answered Sep 13 '21 at 07:02

Jeremy Lakeman

9,515
25
29

My LINQ statement is much more complex; as I said in OP - I realize that with simplifications that I did, I didn't even need `GroupBy` - but trust me... i do ;) The first statement - imagine that there are a dozen attributes for location; some may be quite complex (e.g., address, manager, etc.). So, while I realize I can put all the "cabbage" into GroupBy statement - then I'd rather traverse the resultant list – Felix Sep 13 '21 at 07:14
You can select (or group by) an entire entity eg `.Select(x => new { x.Location, ... })`. Though you might need to explicitly load owned types. Perhaps you should ensure your example is more realistic then. – Jeremy Lakeman Sep 13 '21 at 07:17

score 0 · Answer 3 · answered Sep 13 '21 at 07:09

0

var revenues = await _context.Location
.Select(x => new LocationDTO {
    LocationId = x.Id,
    LocationName = x.Name,
    Revenues = x.SaleTransactions.Sum(i => i.Amount)
}).ToListAsync();

there is example: .NetFiddle

answered Sep 13 '21 at 07:09

Sina Riani

325
4
17

Thanks. As I said, in this simple scenario I might not even need GroupBy - but please assume that I do! – Felix Sep 13 '21 at 07:17
try something like `LocationName = x.First().Location.LocationId` – Sina Riani Sep 13 '21 at 08:21

How do I include attribute in Entity Framework Group By result

3 Answers3