时间戳

首页 > 解决方案 > 左外连接不被尊重

c# - 左外连接不被尊重

问题描述

我们有以下 EF 代码：

    var qry =
            from c in db.Contacts

            join comp in db.Companies on c.CompanyId equals comp.CompanyId
                into compLeft
            from cj in compLeft.DefaultIfEmpty()

            select new CompleteUserDlModel
            {
                CompanyName = cj.Company1,
                CompanyId = c.CompanyId
            };

生成此 SQL

SELECT 
    [Extent1].[CompanyId] AS [CompanyId], 
    [Extent2].[Company] AS [Company]
    FROM  [dbo].[Contacts] AS [Extent1]
    INNER JOIN [dbo].[Company] AS [Extent2] ON [Extent1].[CompanyId] = [Extent2].[CompanyId]

但我们实际上想要

SELECT 
    [Extent1].[CompanyId] AS [CompanyId], 
    [Extent2].[Company] AS [Company]
    FROM  [dbo].[Contacts] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Company] AS [Extent2] ON [Extent1].[CompanyId] = [Extent2].[CompanyId]

有人能指出我们做错了什么吗？

C# EF 中左外连接的所有引用（即LINQ 中的 LEFT OUTER JOIN）都指向我们正在使用的语法。显然，我们遗漏了一些东西。

标签： c#entity-framework

解决方案

Could someone point out what we've done wrong, please?

Probably you have Contact.CompanyId a typed as int instead of int?, making it a required property, and so EF assumes you have referential integrity when generating a query.

But, as always, left join in LINQ has bad code smell, and can almost always be replaced by just querying your target entity and traversing its Navigation Properties. EG:

 from c in db.Contacts
 select new
 {
     CompanyName = c.CompanyId.HasValue?c.Company.CompanyName : null,
     CompanyId = c.CompanyId
 };

推荐阅读