LINQ with Lambda expression - Left Join ,GroupBy ,Count

Question

How can I "translate" this SQL query to Linq Lambda Expression:

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];

I try it, But the result is not correct:

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();

score 4 · Accepted Answer · answered Dec 15 '17 at 15:31

You are pretty close to the desired result: your LINQ makes an inner join, while your SQL has an outer join.

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();

Reference: How do you perform a left outer join using LINQ extension methods

LINQ with Lambda expression - Left Join ,GroupBy ,Count

1 Answers1