How to select many with multiple many-to-many relationships and group by in EFCore-CodePudding

Say we have models A, B and C. A has a many-to-many relationship with B and with C. I want to create following aggregation:

 -------- ---------------- -------------- --------- 
|  a_id  |  b_value_sum   | c_value_sum  | c_count |
 -------- ---------------- -------------- --------- 
| 1      |  20            | 10           | 40      | 
 -------- ---------------- -------------- ---------

So for every A, I want to sum the value property of every b and c, as well as count all c's.

First I tried with:

dbContext.A
    .Include(x => x.B)
    .Include(x => x.C)
    .GroupBy(x => x.Id)
    .Select(g => new
    {
        Id = g.Key.ToString(),
        ..
    });

But that does not allow me to select any of the values of B or C.

I also tried:

context.A
    .SelectMany(y => y.B)
    .SelectMany(z => z.C)
    .Select(v => new { Id = y.Id, ValueB = z.Value, ValueC = v.Value})
    .GroupBy(x => x.Id)
    .Select(g => new
    {
        Id = g.Key.ToString(),
        ValueBSum = g.Sum(y => y.ValueB),
        ValueCSum = g.Sum(y => y.ValueC),
        ValueCCount = g.Count()
    });

But that does not work, because z is of typ B and not A.

Any ideas how to achieve the desired result?

CodePudding user response：

Use Query Syntax for such queries. It is more readable and easy to maintain.

var query = 
    from a in context.A
    from b in a.B
    from c in b.C.DefaultIfEmpty() // LEFT JOIN
    group new { b, c } by a.Id into g
    select new
    {
        Id = g.Key.ToString(),
        ValueBSum = g.Sum(y => y.b.Value),
        ValueCSum = g.Sum(y => y.c.Value),
        ValueCCount = g.Count()
    };