EFCore GroupBy nested List

March 20, 2023

I have the following Entities:

public class CounterOwner
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public ICollection<CounterEntry> CounterEntries { get; set; } = new List<CounterEntry>();
}

public class CounterEntry
{
    public int Id { get; set; }
    public DateTime DateTime { get; set; } = DateTime.Now;
    public double Heating { get; set; }
    public double WaterCounter1 { get; set; }
    public double WaterCounter2 { get; set; }
    public double WaterCounter3 { get; set; }
    public double WaterCounter4 { get; set; }
    public CounterOwner CounterOwner { get; set; } = null!;
    public int CounterOwnerId { get; set; }
}

And the following in my ApplicationDbContext

...
 public DbSet<CounterOwner> CounterOwners => Set<CounterOwner>();
...

I want to filter by CounterOwner.Name and then group CounterEntry by day and calculate the difference between the last and first entry in the group to get the ‘daily’ amount. I could just load everything into memory, but I want the database to do the work. In SQL this is achievable.

Basically I want some code that results possibly in an anonymous object where the equivalent SQL would be a combination of GroupBy and an aggregate like MAX()

How do I do it with EFCore?

>Solution :

You can use SelectMany to flatten the one to many relationship:

var result = context.CounterOwners
    .Where(o => o.Name == "Doe")
    .SelectMany(o => o.CounterEntries)
    .GroupBy(e => e.DateTime.Date)
    .Select(g => new {
        Day = g.Key,
        Amount1 = g.Max(e => e.WaterCounter1) - g.Min(e.WaterCounter1),
        Amount2 = g.Max(e => e.WaterCounter2) - g.Min(e.WaterCounter2),
        Amount3 = g.Max(e => e.WaterCounter3) - g.Min(e.WaterCounter3),
        Amount4 = g.Max(e => e.WaterCounter4) - g.Min(e.WaterCounter4)
    });