Linq To Entities Group By with multiple tables in key

Question

With a table structure along the lines of:

TableB <---1 TableA 1---> TableC

I need to run a query in Linq to EF that does this:

SELECT SUM(TableC.SomeColumn)
FROM TableA a
  INNER JOIN TableB b
    ON a.Id = b.fkAId
  INNER JOIN TableC c
    ON a.Id = c.fkAId

WHERE <some_conditions>

GROUP BY c.Col2, c.Col3, b.Col5

I have this so far, but I can't work out how to specify the table b column in the group by. It needs to be fluent rather than LINQ SQL style expression.

var res = TableCs.Where(c =>
c.TableA.TableBs.Any(b => b.Col5 == "SomeValue") &&
.
.
.
)

.GroupBy(c => new { c.Col2, c.Col3, b.<***how to I get to b column here***>})
.Select(gr => new 
{
.
.
.
})

I'm able to pick columns from A as there's a single A navigation property, but I can't visualise how to get to the B column as I'm navigating to a collection on the last leg.

Does anyone know how to go these TableB columns for a GroupBy key??

Answer 1

Before grouping, you need to use the Queryable.SelectMany overload that allows projection, like this

var query =
    TableCs.SelectMany(c => c.TableA.TableBs
        .Where(b => b.Col5 == "SomeValue"
            // && ...
        ),
        (c, b) => new { c, b }
    )
    .GroupBy(r => new { r.c.Col2, r.c.Col3, r.b.Col5 })
    .Select(g => new
    {
        g.Key.Col2,
        g.Key.Col3,
        g.Key.Col5,
        SomeColumnC = g.Sum(r => r.c.SomeColumn),
        SomeColumnB = g.Sum(r => r.b.SomeColumn)
        // ...
    });