EFCore GroupBy nested List

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?

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

This is a simulatation of you entity database

<!-- begin snippet: js hide: false console: true babel: false -->

namespace ConsoleApplication52
{
    class Program
    {
        static void Main(string[] args)
        {
            Context db = new Context();

            var results = db.CounterOwner
                .Select(x => new {id = x.Id, name = x.Name, first = x.CounterEntries.OrderBy(y => y.DateTime).First(), last = x.CounterEntries.OrderBy(y => y.DateTime).Last()})
                .Select(x => new {
                    id = x.id, 
                    name = x.name,
                    heating = x.last.Heating - x.first.Heating,
                    waterCount1 = x.last.WaterCounter1 = x.first.WaterCounter1,
                    waterCount2 = x.last.WaterCounter2 = x.first.WaterCounter2,
                    waterCount3 = x.last.WaterCounter3 = x.first.WaterCounter3,
                    waterCount4 = x.last.WaterCounter4 = x.first.WaterCounter4,
                    }).ToList();

 
        }
    }
    public class Context
    {
        public List<CounterOwner> CounterOwner { get;set;} 
        public List<CounterEntry> CounterEntry { get;set;} 
    }

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

    public class CounterEntry
    {
        public int Id { get; set; }
        public DateTime DateTime { get; set; }
        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;}
        public int CounterOwnerId { get; set; }
    }

}

<!-- end snippet -->