SQL to LINQ - Multiple Group By Statements and a Join

Question

Working to develop a LINQ query to return the same results as this SQL query:

SELECT 
  x.departmentid, 
  Count(x.transactionid) 
FROM 
  (
    SELECT 
      t.id AS transactionid, 
      tp.departmentid AS departmentid 
    FROM 
      history.transactions t 
      JOIN history.transactionproducts tp ON t.id = tp.transactionid 
    GROUP BY 
      t.id, 
      tp.departmentid
  ) AS x 
GROUP BY 
  x.departmentid

I'm struggling to figure out how I can group by the departmentid multiple times in LINQ.

Here's what I've tried so far:

var deptTransactionsCount = from transaction in transactions
                                        join product in transactionProducts on transaction.Id equals product.TransactionId
                                        group transaction by new { product.TransactionId, product.DepartmentId }
                                        into groupedbyTransAndDept
                                        select new
                                                   {
                                                       DepartmentID = groupedbyTransAndDept.Key.DepartmentId,
                                                       TotalTransactionsCount = groupedbyTransAndDept.Count()
                                                   };

Again, I'm not sure how to account for the multiple group by statements.

Here's the results from the SQL query: results

Answer 1

You don't have to do anything special to account for multiple groupings. You have a derived table, which is one grouping. That's one query. From that, you do another grouping. That's another query.

var deptTransactionsCount = (
    from dept in (
        from t in transactions
        join tp in transactionProducts on t.Id equals tp.TransactionId
        group tp by new { tp.TransactionId, tp.DepartmentId } into groupedbyTransAndDept
        from transAndDept in groupedbyTransAndDept
        select new
        {
            transAndDept.TransactionId,
            transAndDept.DepartmentId
        })
    group dept by dept.DepartmentId into groupedByDept
    select new
    {
        DepartmentId = groupedByDept.Key,
        Count = groupedByDept.Count()
    }).ToList();

And because it's LINQ, you can factor out the inner query if that makes it easier to understand what it's doing.

var innerQuery =
    from t in transactions
    join tp in transactionProducts on t.Id equals tp.TransactionId
    group tp by new { tp.TransactionId, tp.DepartmentId } into groupedbyTransAndDept
    from transAndDept in groupedbyTransAndDept
    select new
    {
        transAndDept.TransactionId,
        transAndDept.DepartmentId
    };

var deptTransactionsCount = (
    from dept in innerQuery
    group dept by dept.DepartmentId into groupedByDept
    select new
    {
        DepartmentId = groupedByDept.Key,
        Count = groupedByDept.Count()
    }).ToList();