c#-4.0 - Entity Framework 和强制内连接

Question

我的 Table1 具有以下关系(它们不是强制的，它们只是为导航属性创建关系)

Table1 (*)->(1) Table2Table1 (*)->(1) Table3Table1 (*)->(1) Table4Table1 (*)->(1) Table5

Using eager loading code looks like

IQueryable<Table1> query = context.Table1s;

query = query.Include(Table1 => Table1.Table2);
query = query.Include(Table1 => Table1.Table3);
query = query.Include(Table1 => Table1.Table4);
query = query.Include(Table1 => Table1.Table5);

query = query.Where(row => row.Table1Id == table1Id);

query.Single();

我尝试组织 Include() 语句的每一种方式，包含的第一个表在其生成的 TSQL 中都有一个 Inner Join，其余的都是 Left Outer Join(我希望所有这些表都是 Left Outer Join)。我不是实体拆分，它们只是带有 FK 的普通表。

如果 DefaultIfEmpty() 是唯一的解决方案，有人可以解释为什么除了第一个表之外的所有表都提供了预期的 SQL 吗？

我的理解是，导航属性的默认行为是 LEFT OUTER，但我无法获取所有属性来生成默认值。

任何帮助将不胜感激。

提前谢谢您!

----- 创建了 TSQL(为简洁起见进行了修改，但结构相同)--------

(@p__linq__0 int)SELECT [Limit1].[Table1Id] AS [Table1Id], [Limit1].[OtherData] AS [OtherData]FROM ( SELECT TOP (2)     [Extent1].[Table1Id] AS [Table1Id],     [Extent1].[OtherData] As [OtherData]    FROM       [dbo].[Table1] AS [Extent1]    INNER JOIN [dbo].[Table2] AS [Extent2] ON [Extent1].[Table2Id] = [Extent2].[Table2Id]    LEFT OUTER JOIN [dbo].[Table3] AS [Extent3] ON [Extent1].[Table3Id] = [Extent3].[Table3Id]    LEFT OUTER JOIN [dbo].[Table4] AS [Extent4] ON [Extent1].[Table4Id] = [Extent4].[Table4Id]    LEFT OUTER JOIN [dbo].[Table5] AS [Extent5] ON [Extent1].[Table5Id] = [Extent5].[Table5Id]    WHERE [Extent1].[Table1Id] = @p__linq__0)  AS [Limit1]

Answer 1

EF 似乎使用 INNER JOIN 来包含必需，使用 LEFT OUTER JOIN 包含可选导航属性(property)。示例:

public class Order
{
    public int Id { get; set; }
    public string Details { get; set; }
    public Customer Customer { get; set; }
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
}

如果我将 Customer 定义为 Order 上的必需属性...

public class MyContext : DbContext
{
    public DbSet<Order> Orders { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>()
            .HasRequired(o => o.Customer)
            .WithMany();
    }
}

...并发出此查询...

using (var ctx = new MyContext())
{
    var result = ctx.Orders
        .Include(o => o.Customer)
        .Where(o => o.Details == "Peanuts")
        .FirstOrDefault();
}

...我得到这个 SQL:

SELECT TOP (1) 
[Extent1].[Id] AS [Id], 
[Extent1].[Details] AS [Details], 
[Extent2].[Id] AS [Id1], 
[Extent2].[Name] AS [Name]
FROM  [dbo].[Orders] AS [Extent1]
INNER JOIN [dbo].[Customers] AS [Extent2] 
    ON [Extent1].[Customer_Id] = [Extent2].[Id]
WHERE N'Peanuts' = [Extent1].[Details]

如果我将模型配置 .HasRequired(o => o.Customer) 更改为...

.HasOptional(o => o.Customer)

...我得到完全相同的查询，除了 INNER JOIN [dbo].[Customers] AS [Extent2] 被替换为:

LEFT OUTER JOIN [dbo].[Customers] AS [Extent2]

从模型的角度来看，这是有道理的，因为如果您将关系定义为必需客户就永远不会有订单 >。如果您通过删除数据库中的强制措施来规避此要求，并且您实际上有没有客户的订单，那么您就违反了自己的模型定义。

如果您遇到这种情况，唯一的解决方案可能是使这种关系成为可选。我认为无法控制使用 Include 时创建的 SQL。