Replicating nested group query (subquery) using Lambda in C# / DOTNET Core

user_3242154 0
Viewed 76

Lately I am migrating some of my Dapper queries to EF Core / Lambda queries. I am struggling though with getting a nested grouping result.

After reading several posts about groupings I can't seem to get the desired result. Sadly all the examples are in LINQ which I have done very little with, so I am trying to get the result with LAMBDA.

So how would I write the following SQL query in LAMBDA:

SELECT 
    sub.ClientId,
    sub.ClientName,
    ProjectCount = COUNT(sub.ProjectId)
FROM
(
    SELECT mt.ClientId, mt.ClientName, mt.ProjectId
    FROM MyTable mt
    WHERE mt.AccountId = 'XXXX' 
    GROUP BY mt.ClientId, mt.ClientName, mt.ProjectId
)sub
GROUP BY
    sub.ClientId,
    sub.ClientName
lambda sql c#
1 Answers

Found a way to get the desired result, though I am not sure this is the 'best' way.

var clients = await _dbContext.MyTable 
    .Where(c => c.AccountId == accountId)
    .GroupBy(c => new { c.ClientId, c.ClientName })
    .Select(g => new
    {
        ClientId = g.Key.ClientId,
        ClientName = g.Key.ClientName,
        ProjectCount = g.Where(l => l.ClientId == g.Key.ClientId).Select(p => p.ProjectId).Distinct().Count()
    }).ToListAsync();
user_32421540
Related