带有lambda表达式的LINQ - 左JOIN，GroupBy，Count

Question

我如何"翻译"此SQL查询到linq lambda表达式：

Select SC.[Description], 
       COUNT(C.[StatusID]) as Amount
From [StatusCandidate] SC 
Left Join  
       (Select * 
        From [Candidate] AS c 
        Where c.RequestID = 1) AS C
ON C.StatusID = SC.StatusCandidateID
Group By SC.[Description];

我尝试了，但是结果不正确：

dbContext.StatusCandidates
    .GroupJoin(
        dbContext.Candidates.Where(u => u.RequestID == requestId),
         x => x.StatusCandidateID,
         y => y.StatusID,
         (x, y) => new {x, y})
    .GroupBy(g => new {g.x.Description})
    .Select(z => new AmountStatus{
         StatusName = z.Key.Description,
         Amount = z.Count()
    }).ToList();

Answer 1

您非常接近所需的结果：您的linq成为内部连接，而您的SQL具有外部连接。

dbContext.StatusCandidates
    .GroupJoin(
        dbContext.Candidates.Where(u => u.RequestID == requestId)
    ,    x => x.StatusCandidateID
    ,    y => y.StatusID
    ,    (x, y) => new { StatusCandidate = x, StatusGroup = y }
    )
    .SelectMany(
        x => x.StatusGroup.DefaultIfEmpty()
    ,   (x, y) => new { x.StatusCandidate, Status = y}
    )
    .GroupBy(g => new { g.StatusCandidate.Description })
    .Select(z => new AmountStatus{
         StatusName = z.Key.Description
    ,    Amount = z.Count()
    }).ToList();

参考：您如何使用linq扩展方法执行左外连接