C# 将我查询解释为内部联接而不是左外部联接,在什么情况下发生这种情况吗?

标签: Linq C#
发布时间: 2017/2/28 23:19:34
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我在我的 MVC 应用程序中有两个不同的查询。这两个查询结果 LINQPAD 的预期。但我查询之一不会产生左外连接的结果;相反,它产生内部联接的结果,在左表中的空记录消除产生其他记录。

我的查询如下所示

var query = from inq in Inquiries
join sp in SalesPersons on inq.AssignedToID equals (int?)sp.SalesPersonID
into inquirySalesJoin
from sp in inquirySalesJoin.DefaultIfEmpty()
where (!inq.Deleted)
select new 
{
InquiryID = inq.InquiryID,
NameFirst = inq.NameFirst,
Salutation = inq.SalutationID,
NameLast = inq.NameLast,   
AssignedToID = inq.AssignedToID,
AssignedToDescription = (inq.AssignedToID == null ? "Not Assigned" : ((sp.NameFirst == null ? "" : sp.NameFirst) + " " + (sp.NameLast == null ? "" : sp.NameLast))),
InquiryStatus = inq.InquiryStatus
}

在将转换我查询生成的 SQL 语句的字符串不包含任何外部联接方法,当它实际上执行内部联接

"SELECT 
[Project1].[InquiryID] AS [InquiryID], 
[Project1].[NameFirst] AS [NameFirst], 
[Project1].[SalutationID] AS [SalutationID], 
[Project1].[NameLast] AS [NameLast], 
[Project1].[CompanyName] AS [CompanyName], 
[Project1].[JobTitle] AS [JobTitle], 
[Project1].[TelePhone] AS [TelePhone], 
[Project1].[Email] AS [Email], 
[Project1].[InterestedProductID] AS [InterestedProductID], 
[Project1].[Others] AS [Others], 
[Project1].[ReferedBy] AS [ReferedBy], 
[Project1].[Comments] AS [Comments], 
[Project1].[AssignedToID] AS [AssignedToID], 
[Project1].[C1] AS [C1], 
[Project1].[InquiryStatus] AS [InquiryStatus]
FROM ( SELECT 
    [Extent1].[InquiryID] AS [InquiryID], 
    [Extent1].[NameLast] AS [NameLast], 
    [Extent1].[NameFirst] AS [NameFirst], 
    [Extent1].[CompanyName] AS [CompanyName], 
    [Extent1].[JobTitle] AS [JobTitle], 
    [Extent1].[TelePhone
] AS [TelePhone], 
    [Extent1].[Email] AS [Email], 
    [Extent1].[InterestedProductID] AS [InterestedProductID], 
    [Extent1].[Others] AS [Others], 
    [Extent1].[ReferedBy] AS [ReferedBy], 
    [Extent1].[Comments] AS [Comments], 
    [Extent1].[AssignedToID] AS [AssignedToID], 
    [Extent1].[InquiryStatus] AS [InquiryStatus], 
    [Extent1].[SalutationID] AS [SalutationID], 
    CASE WHEN ([Extent2].[NameFirst] IS NULL) THEN N'' ELSE [Extent2].[NameFirst] END + N' ' + CASE WHEN ([Extent2].[NameLast] IS NULL) THEN N'' ELSE [Extent2].[NameLast] END AS [C1]
    FROM  [DBO].[Inquiry] AS [Extent1]
    INNER JOIN [DBO].[SalesPerson] AS [Extent2] ON [Extent1].[AssignedToID] = [Extent2].[SalesPersonID]
    WHERE [Extent1].[Deleted] <> cast(1 as bit)
)  AS [Project1]
ORDER BY [Project1].[InquiryID] ASC"

我想知道在什么情况下发生这种情况吗?

我另一个查询我不指定可以为 null 的 int?FK id。

解决方法 1:

只是改变你的代码

var query = from inq in Inquiries
        join sp in SalesPersons on inq.AssignedToID equals (int?)sp.SalesPersonID
        into inquirySalesJoin
        from subsp in inquirySalesJoin.DefaultIfEmpty()
        where (!inq.Deleted)
        select new 
        {
        InquiryID = subsp.InquiryID,
        NameFirst = subsp.NameFirst,
        Salutation = subsp.SalutationID,
        NameLast = subsp.NameLast,   
        AssignedToID = subsp.AssignedToID,
        AssignedToDescription = (subsp.AssignedToID == null ? "Not Assigned" : ((subsp.NameFirst    == null ? "" : subsp.NameFirst) + " " + (subsp.NameLast == null ? "" : subsp.NameLast))),
        InquiryStatus = subsp.InquiryStatus
        }
官方微信
官方QQ群
31647020