c# - Entity Framework 中的左外连接一对一

Question

我在 Entity Framework 6.1.3 中遇到与一对一关系相关的问题。

我有 2 个类(class)。

public class Package
{
    public Guid Id { get; set; }
    public string PackageNo { get; set; }
    public double SendingCost{ get; set; }
    public virtual PackageAppointment PackageAppointment { get; set; }
}

public class PackageAppointment 
{
    public DateTime Date { get; set; }
    public virtual Package Package { get; set; }
}

PackageAppointment 的 map 类是:

public class PackageAppointmentMap
{
    public PackageAppointmentMap()
    {
        Property(x => x.Date).IsRequired();

        HasRequired(x => x.Package).WithOptional(x => x.PackageAppointment).Map(x => x.MapKey("PackageId"));

    }
}

一切正常。我对这段关系没有任何问题。问题是查询数据库。每次我使用上下文查询数据库中的 Package 实体时，SQL Server 探查器都会生成类似这样的内容。

LINQ 查询:

var data = await Task.Run(() => GenericService.GetAll().Select(x => x.PackageNo));

SQL Server 探查器:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[PackageNo] AS [PackageNo], 
    [Extent1].[SendingCost] AS [SendingCost],    
    [Extent2].[Id] AS [Id1]   
FROM    
    [dbo].[Package] AS [Extent1]
LEFT OUTER JOIN 
    [dbo].[PackageAppointment] AS [Extent2] ON [Extent1].[Id] = [Extent2].[PackageId]

我使用 context.Package... 进行的每个查询都没有从 PackageAppointment(或任何其他一对一关系)中检索数据， Entity Framework 生成一个左外连接.

我项目中的实际情况是Package实体有5个一对一的关系。因此性能非常低。

也许我应该改变关系或其他什么。我真的不知道该怎么做才能解决这个性能问题。

请指教。

Answer 1

您可以将 Package 和 PackageAppointment 之间的关系更改为:

public PackageAppointmentMap()
{
    Property(x => x.Date).IsRequired();

    HasOptional(x => x.Package)
        .WithOptionalPrincipal(x => x.PackageAppointment)
        .Map(x => x.MapKey("PackageAppointmentId"));
}

这将使您的包表包含 FK PackageAppontmentId，并且您的查询将没有此连接。更改与 Package.HasRequired(x=>x.PackageAppointment) 的关系也将起作用(对于可能的原因，请参见下文)

访问包之后:

var query = dbContext.Packages.ToString();

返回:

SELECT
    1 AS [C1],
    [Extent1].[Id] AS [Id],
    [Extent1].[PackageNo] AS [PackageNo],
    [Extent1].[SendingCost] AS [SendingCost],
    [Extent1].[PackageAppointmentId] AS [PackageAppointmentId]
    FROM [dbo].[Packages] AS [Extent1]

另一方面，访问 PackageAppointments:

var anotherQuery = dbContext.PackageAppointments.ToString();

将导致查询:

SELECT
    1 AS [C1],
    [Extent1].[Id] AS [Id],
    [Extent1].[Date] AS [Date],
    [Extent2].[Id] AS [Id1]
    FROM  [dbo].[PackageAppointments] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Packages] AS [Extent2] ON ([Extent2].[PackageAppointmentId] IS NOT NULL) AND ([Extent1].[Id] = [Extent2].[PackageAppointmentId])

[编辑]

为什么会这样？

这看起来主要是为了更改跟踪而完成的。

场景:

你有一个 PackageAppointment具有必需的 Package .这意味着，你的 PackageAppointment表有外键 PackageId , 所以当你加载 PackageAppointment ，更改跟踪器包含有关 PackageAppointment 的所有信息跟踪实体的所有可能更改，并更改 PackageAppointment.Package引用很容易跟踪 - 你比较 PackageAppointment.PackageId在您加载的实体中，即使您没有真正从 Package 加载记录表，与 PackageAppointment 相关联(延迟加载 = 关闭)。

另一方面，如果您加载 Package , 你有属性(property) PackageAppointment您可以更改以引用另一个 PackageAppointment。但是要跟踪此更改，您必须以某种方式获得 PackageAppointment.Id与此包相关联。自 Package表没有从自身到 PackageAppointment 的外键表，您必须进行连接才能获得 PackageAppointment.Id , 与此相关Package .换句话说，如果您不执行此连接，则此实体的更改跟踪将无效，并且会使您加载引用 PackageAppointment如果您对其进行任何更改，这可能是无效的。

要证明这一点，请尝试输入 .AsNoTracking()对于此特定查询并检查返回的查询。

所以代码

var trackingQuery = dbContext.PackageAppointments.ToString();

Console.WriteLine("==========================");
Console.WriteLine("Query with Change Tracker enabled");
Console.WriteLine(trackingQuery);
Console.WriteLine("==========================");
Console.WriteLine("\n\n");

var noTrackingQuery = dbContext.PackageAppointments.AsNoTracking().ToString();

Console.WriteLine("==========================");
Console.WriteLine("Query with Change Tracker disabled");
Console.WriteLine(noTrackingQuery);
Console.WriteLine("==========================");

结果如下输出:

==========================
Query with Change Tracker enabled
SELECT
    1 AS [C1],
    [Extent1].[Id] AS [Id],
    [Extent1].[Date] AS [Date],
    [Extent2].[Id] AS [Id1]
    FROM  [dbo].[PackageAppointments] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Packages] AS [Extent2] ON ([Extent2].[PackageAppointmentId] IS NOT NULL) AND ([Extent1].[Id] = [Extent2].[PackageAppointmentId])
==========================



==========================
Query with Change Tracker disabled
SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Date] AS [Date]
    FROM [dbo].[PackageAppointments] AS [Extent1]
==========================

可以看到，LEFT OUTER JOIN在这里神奇地消失了，证明这与Change Tracking系统有关。