C#Linq Lambda左外部联接 [英] C# Linq Lambda Left Outer Join

问题描述

我需要使用linq lambda语法创建一个LEFT OUTER JOIN.我正在尝试创建与linq等价的SQL:

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.

SQL查询返回41条记录，其中1条记录的供应商为空. linq查询返回40条记录，显然没有返回带有空供应商的一条记录.我曾尝试使用GroupJoin()和DefaultIfEmpty()，但无法正常工作.

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.

任何帮助将不胜感激.

推荐答案

从user2321864的注释和链接中，我设法使其工作如下:

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左外部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！