CODEWITHSUNDEEP
c#sql-serverlinqjoin
DaRoGa
DaRoGa

Reputation: 2404

C# Linq Lambda Left Outer Join

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.

Upvotes: 0

Views: 1171

Answers (1)

DaRoGa
DaRoGa

Reputation: 2404

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

Upvotes: 0

Related Questions