How do I include attribute in Entity Framework Group By result-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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();

CodePudding user response：

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