C# Linq Lambda Left Outer Join

Question

I need to create a LEFT OUTER JOIN in linq lambda syntax. The SQL I am trying to create a linq equivalent of is:

SELECT DISTINCT 
    p.PartNum AS PartNum, p.ShortChar01 AS SkuType, 
    vv.VendorID AS VendorCode, 
    p.PartDescription AS Description, p.Company AS Company
FROM 
    Part p WITH (NOLOCK) 
INNER JOIN
    PartPlant pp ON p.Company = pp.Company AND p.PartNum = pp.PartNum 
LEFT OUTER JOIN
    Vendor vv On pp.VendorNum = vv.VendorNum 
WHERE 
    p.RefCategory = @refCategory

So as you can see its a fairly simple query joining a few tables. The issue is that it could happen that there is no vendor but we still want the rest of the information hence the left outer join.

My current attempt to recreate this is:

_uow.PartService
    .Get()
    .Where(p => p.RefCategory.Equals(level2))
    .Join(_uow.PartPlantService.Get(),
          p => new { p.PartNum, p.Company },
          pp => new { pp.PartNum, pp.Company },
          (p, pp) => new { Part = p, PartPlant = pp })
    .GroupJoin(_uow.VendorService.Get(),
               pprc => pprc.PartPlant.VendorNum,
               v => v.VendorNum,
               (pprc, v) => new { PPRC = pprc, V = v });

I am aware that the select isn't returning the same fields at the moment. I have ignored that for now as I am trying to ensure i am getting the correct values first.

The SQL query returns 41 records with 1 record having a null vendor. The linq query returns 40 records obviously not returning the one with the null vendor. I have tried using GroupJoin() and DefaultIfEmpty() but I cannot get it to work.

Any help would be greatly appreciated.

https://code.msdn.microsoft.com/LINQ-Join-Operators-dabef4e9#leftouterjoin — joachim, Aug 22 '17 at 08:57
That doesnt really provide much info. From researching I have found that the correct way to do it is using `GroupJoin()` hence why I have taken that approach. Could you explain what you expect me to gain from that rather than just providing a link — DaRoGa, Aug 22 '17 at 08:59
https://stackoverflow.com/questions/3404975/left-outer-join-in-linq?rq=1 — Mixxiphoid, Aug 22 '17 at 09:05
For left outer join, you need to call `DefaultIfEmpty()` on the group. See https://stackoverflow.com/questions/584820/how-do-you-perform-a-left-outer-join-using-linq-extension-methods and https://stackoverflow.com/questions/12075905/left-outer-join-in-lambda-method-syntax-in-linq — user2321864, Aug 22 '17 at 09:26

score 0 · Accepted Answer · answered Aug 22 '17 at 09:37

From the comment and links from user2321864, I managed to get it working as follows:

_uow.PartService.Get().Where(p => p.RefCategory.Equals(level2))
                                .Join(_uow.PartPlantService.Get(),
                                        p => new { p.PartNum, p.Company },
                                        pp => new { pp.PartNum, pp.Company },
                                        (p, pp) => new { Part = p, PartPlant = pp })
                                .GroupJoin(_uow.VendorService.Get(),
                                        pprc => pprc.PartPlant.VendorNum,
                                        v => v.VendorNum,
                                        (pprc, v) => new { PPRC = pprc, V = v })
                                .SelectMany(y => y.V.DefaultIfEmpty(),
                                            (x, y) => new { PPRC = x.PPRC, Vendor = y })
                                .Select(r => new Level2Parts()
                                {
                                    CompanyCode = r.PPRC.Part.Company,
                                    Description = r.PPRC.Part.PartDescription,
                                    PartNum = r.PPRC.Part.PartNum,
                                    SkuType = r.PPRC.Part.ShortChar01,
                                    VendorCode = r.Vendor.VendorID
                                })
                                .Distinct();

C# Linq Lambda Left Outer Join

1 Answers1

Linked