简单的1-*关系会导致奇怪的SQL查询

本文关键字:查询 SQL 关系 简单 | 更新日期: 2023-09-27 18:13:06

我试图调试EF 6.1.1生成的SQL查询,我已经能够用这少量的代码重现问题。这是一个简单的一对多单向关系,我正在尝试加载:

模型如下:

public class Subscription
{
    public Guid Id { get; set; }
    public IList<Recipient> RecipientHistory { get; set; }
    public IList<Payer> PayerHistory { get; set; }
}
public class Recipient
{
    public Guid Id { get; set; }
}
public class Payer
{
    public Guid Id { get; set; }
}

和EF映射类:

public class SubscriptionMapping : EntityTypeConfiguration<Subscription>
{
    public SubscriptionMapping()
    {
        ToTable("Subscriptions");
        HasKey(p => p.Id);
        Property(p => p.Id).HasColumnName("subscription_id");
        HasMany(p => p.RecipientHistory)
            .WithRequired()
            .Map(m => m.MapKey("subscription_id"));
        HasMany(p => p.PayerHistory)
            .WithRequired()
            .Map(m => m.MapKey("subscription_id"));
    }
}
public class RecipientMapping : EntityTypeConfiguration<Recipient>
{
    public RecipientMapping()
    {
        ToTable("Subscription_recipients");
        HasKey(p => p.Id);
        Property(p => p.Id).HasColumnName("subscription_recipient_id");
    }
}
public class PayerMapping : EntityTypeConfiguration<Payer>
{
    public PayerMapping()
    {
        ToTable("Subscription_payers");
        HasKey(p => p.Id);
        Property(p => p.Id).HasColumnName("subscription_payer_id");
    }
}

当我对商店执行以下查询时:

db.Set<Subscription>()
    .Include(s => s.RecipientHistory)
    .Include(s => s.PayerHistory)
    .ToList();
结果SQL为:
SELECT 
    [UnionAll1].[C2] AS [C1], 
    [UnionAll1].[subscription_id] AS [C2], 
    [UnionAll1].[C1] AS [C3], 
    [UnionAll1].[C3] AS [C4], 
    [UnionAll1].[subscription_recipient_id] AS [C5], 
    [UnionAll1].[subscription_id1] AS [C6], 
    [UnionAll1].[C4] AS [C7], 
    [UnionAll1].[C5] AS [C8], 
    [UnionAll1].[C6] AS [C9]
    FROM  (SELECT 
        CASE WHEN ([Extent2].[subscription_recipient_id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 
        1 AS [C2], 
        [Extent1].[subscription_id] AS [subscription_id], 
        CASE WHEN ([Extent2].[subscription_recipient_id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C3], 
        [Extent2].[subscription_recipient_id] AS [subscription_recipient_id], 
        [Extent2].[subscription_id] AS [subscription_id1], 
        CAST(NULL AS int) AS [C4], 
        CAST(NULL AS uniqueidentifier) AS [C5], 
        CAST(NULL AS uniqueidentifier) AS [C6]
        FROM  [dbo].[Subscriptions] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Subscription_recipients] AS [Extent2] ON [Extent1].[subscription_id] = [Extent2].[subscription_id]
    UNION ALL
        SELECT 
        2 AS [C1], 
        2 AS [C2], 
        [Extent3].[subscription_id] AS [subscription_id], 
        CAST(NULL AS int) AS [C3], 
        CAST(NULL AS uniqueidentifier) AS [C4], 
        CAST(NULL AS uniqueidentifier) AS [C5], 
        2 AS [C6], 
        [Extent4].[subscription_payer_id] AS [subscription_payer_id], 
        [Extent4].[subscription_id] AS [subscription_id1]
        FROM  [dbo].[Subscriptions] AS [Extent3]
        INNER JOIN [dbo].[Subscription_payers] AS [Extent4] ON [Extent3].[subscription_id] = [Extent4].[subscription_id]) AS [UnionAll1]
    ORDER BY [UnionAll1].[subscription_id] ASC, [UnionAll1].[C1] ASC

对于这样一个简单的查询,这对我来说似乎是相当不必要的。我是否在映射中遗漏了一些东西,或者这是有意的?

简单的1-*关系会导致奇怪的SQL查询

急于加载两个不同的依赖关系是相当棘手的。这是一种安全的方法,同时使用尽可能少的空间—注意,对于不是"包含"部分的所有行,结果是null

基本问题是,对于两个1:*关联,您将难以将第一个关联中的包含实体与第二个关联中的包含实体分开。当您处理1:(0-)1时,不会出现同样的问题,因为结果将只是一条记录。

问题是,你认为这是"一个简单的1对多关系"。如果您曾经尝试过用SQL编写类似的查询,那么您必须知道1:*实际上非常复杂,特别是当您需要在一个查询中组合许多不同的1:*时。现在,有替代方案(例如,使用xml字段在结果集中),但这可能是一个让服务器做尽可能多的优化,它可以,开销实际上是相当小的在实践中-再次,请注意,你只得到每一块数据一次-其余的是null s -这就是为什么第一个查询做一个left join(它必须返回父即使没有关系),而第二个则可以自由地执行inner join(并且不需要从父键中选择除主键外的任何列)。如果有一种方法不返回第一个子查询的每一行中的父数据,这仍然可以得到改进,但这说起来容易做起来难(并且可能最终会慢得多)。

总而言之,这是一个有点雄辩的查询,但它做了它必须做的事情。你是遇到了性能问题,还是只是好奇?