entity-framework - EF Core Inner join 改为 Left

Question

我的查询生成带有连接的Includesql 。我的 FK 可以为空，所以我无法解释这种行为。使用可为空的 FK，我期望正常加入。Inner LeftLeft

我错过了什么吗？

LINQ查询：

     var projectEntity = await _context.Projects
            // few more includes were omitted
            .Include(p => p.Invoice)
            .FirstOrDefaultAsync(c => c.ProjectId == id);

课程：

[Table("InvoicedHistory")]
public class InvoiceHistory
{
    [Key]
    [Column("InvoicedHistory_ID")]
    public int InvoicedHistoryId { get; set; }
    // few properties omitted
    [Column("Project_ID")]
    public int? ProjectId { get; set; }
}

public class Project
{
    public int ProjectId { get; set; }
    
    // few properties were omitted
    
    [ForeignKey(nameof(InvoiceHistory.ProjectId))]
    public virtual InvoiceHistory Invoice { get; set; }
}

项目类也使用fluent api：

        modelBuilder.Entity<Project>(entity =>
        {
            entity.ToTable("Projects");

            entity.HasKey(e => e.ProjectId)
                .HasName("PK_Project_Project_ID_Project");

           // few statements were omitted

        });

生成的 Sql：（很难清理此查询。它包含几个连接以包含我省略的属性的数据）

    SELECT [t].[Project_ID], [t].[Project_Client], [t].[Project_IrsDate], [t].[Project_Name], [t].[Client_ID], [t].[Client_Name], [t].[InvoicedHistory_ID], [t].[DateSubmitted], [t].[Project_ID0], [t0].[Debitor_ID], [t0].[Project_ID], [t0].[Debitor_ID0], [t0].[Address_Number], [t0].[Alias], [t0].[Alpha_Name], [t0].[Co], [t0].[Country_ID], [t0].[Currency_ID], [t0].[Havi_YesOrNo]
  FROM (
      SELECT TOP(1) [p].[Project_ID], [p].[Project_Client], [p].[Project_IrsDate], [p].[Project_Name], [c].[Client_ID], [c].[Client_Name], [i].[InvoicedHistory_ID], [i].[DateSubmitted], [i].[Project_ID] AS [Project_ID0]
      FROM [Projects] AS [p]
      INNER JOIN [Clients] AS [c] ON [p].[Project_Client] = [c].[Client_ID]
      INNER **<<<<<<<<(expect LEFT)** JOIN [InvoicedHistory] AS [i] ON [p].[Project_ID] = [i].[InvoicedHistory_ID]
      WHERE [p].[Project_ID] = 19922
  ) AS [t]
  LEFT JOIN (
      SELECT [p0].[Debitor_ID], [p0].[Project_ID], [d].[Debitor_ID] AS [Debitor_ID0], [d].[Address_Number], [d].[Alias], [d].[Alpha_Name], [d].[Co], [d].[Country_ID], [d].[Currency_ID], [d].[Havi_YesOrNo]
      FROM [ProjectDebitors] AS [p0]
      INNER JOIN [Debitors] AS [d] ON [p0].[Debitor_ID] = [d].[Debitor_ID]
  ) AS [t0] ON [t].[Project_ID] = [t0].[Project_ID]
  ORDER BY [t].[Project_ID], [t].[Client_ID], [t].[InvoicedHistory_ID], [t0].[Debitor_ID], [t0].[Project_ID], [t0].[Debitor_ID0]

看看这条线——

   INNER <<<<<<<<(expect LEFT)<<<<<< JOIN [InvoicedHistory] AS [i] ON [p].[Project_ID] = [i].[InvoicedHistory_ID]

内连接使我的查询不返回任何内容，因为我没有发票信息。如果我用 join 手动替换它Left，sql 查询将返回我所有必要的数据。

标签： entity-frameworkentity-framework-corelinq-to-entities

Answer 1

我认为您可以使用 Fluent API 来获得您想要的结果：

modelBuilder.Entity<Project>()
  .HasOne(p => p.Invoice)
  .WithOne()
  .HasForeignKey(ih => ih.ProjectId);

这应该将其更改为左连接，因为我们没有指定.IsRequired()

如以下SO 答案中所述 - 等效于 .HasOptional in Entity Framework Core 1 (EF7)

您不会在 EF 7 中找到等效方法。按照惯例，CLR 类型可以包含 null 的属性将被配置为可选。因此，决定关系是否可选的是 FK 属性是否可以分别为空。

和

如果您的 FK 属性是像 int 这样的值类型，则应将其声明为可为空（int？）。

现在很可能您的注释问题是以下内容没有按照您的想法进行：

[ForeignKey(nameof(InvoiceHistory.ProjectId))]

//Does not resolve to:
[ForeignKey("InvoiceHistory.ProjectId")]

//Does resolve to:
[ForeignKey("ProjectId")]

现在，即使这是您要查找的内容，ForeignKey 检测的操作顺序也是先检查父类型，然后再检查属性类型。

public class InvoiceHistory
{
    public int? ProjectId { get; set; }
}

public class Project
{
    public int ProjectId { get; set; }
    
    // this is pointing to Project.ProjectId
    // and Project.ProjectId is not nullable
    // so the join becomes an inner join
    // and really only works because they both have the same name
    [ForeignKey(nameof(InvoiceHistory.ProjectId))]
    public virtual InvoiceHistory Invoice { get; set; }
}

如果您希望它作为指向属性类型的工作，您需要重命名InvoiceHistory名称：

public class InvoiceHistory
{
    public int? ProjectFk { get; set; }
}

public class Project
{
    public int ProjectId { get; set; }
    
    // this is pointing to InvoiceHistory.ProjectFk 
    // because there is no Project.ProjectFk 
    [ForeignKey(nameof(InvoiceHistory.ProjectFk))]
    public virtual InvoiceHistory Invoice { get; set; }
}

EntityFramework 数据注解

如果你想看到它创建了错误的 SQL，你可以这样做：

public class InvoiceHistory
{
    public int? ProjectId { get; set; }
}

public class Project
{
    public int ProjectFk { get; set; }
    
    [ForeignKey("ProjectFk")]
    public virtual InvoiceHistory Invoice { get; set; }
}

然后 EF 将创建：

 INNER JOIN [InvoicedHistory] AS [i] ON [p].[Project_ID] = [i].[ProjectFk]

并会导致 SqlException 消息类似于Invalid column name.