sql-server – 针对1-1关系的非常简单的EF查询的额外连接

class A
{
    public int Id { get; set; }
    public B B { get; set; }
}

class B
{
    public int Id { get; set; }
    public string Text { get; set; }
}

class C
{
    public int Id { get; set; }
    public B B { get; set; }
}

映射是：

modelBuilder.Entity<A>().HasOptional(x => x.B).WithOptionalDependent();
modelBuilder.Entity<C>().HasOptional(x => x.B).WithOptionalDependent();

我正在运行此查询：

db.As.Include(a => a.B).Where(a => a.B.Text == "10").FirstOrDefault();

db.As是A的DbSet.

它生成以下sql

SELECT TOP (1)
    [Extent1].[Id] AS [Id],[Join8].[Id1] AS [Id1],[Join8].[Text] AS [Text],[Join11].[Id2] AS [Id2],[Join14].[Id3] AS [Id3]
    FROM      [dbo].[A] AS [Extent1]
    INNER JOIN  (SELECT [Extent2].[Text] AS [Text],[Extent3].[Id] AS [Id4]
        FROM   [dbo].[B] AS [Extent2]
        LEFT OUTER JOIN [dbo].[A] AS [Extent3] ON ([Extent3].[B_Id] IS NOT NULL) AND ([Extent2].[Id] = [Extent3].[B_Id])
        LEFT OUTER JOIN [dbo].[C] AS [Extent4] ON ([Extent4].[B_Id] IS NOT NULL) AND ([Extent2].[Id] = [Extent4].[B_Id]) ) AS [Join2] ON [Extent1].[Id] = [Join2].[Id4]
    INNER JOIN  (SELECT [Extent5].[Text] AS [Text],[Extent6].[Id] AS [Id5]
        FROM   [dbo].[B] AS [Extent5]
        LEFT OUTER JOIN [dbo].[A] AS [Extent6] ON ([Extent6].[B_Id] IS NOT NULL) AND ([Extent5].[Id] = [Extent6].[B_Id])
        LEFT OUTER JOIN [dbo].[C] AS [Extent7] ON ([Extent7].[B_Id] IS NOT NULL) AND ([Extent5].[Id] = [Extent7].[B_Id]) ) AS [Join5] ON [Extent1].[Id] = [Join5].[Id5]
    LEFT OUTER JOIN  (SELECT [Extent8].[Id] AS [Id1],[Extent8].[Text] AS [Text],[Extent9].[Id] AS [Id6]
        FROM   [dbo].[B] AS [Extent8]
        LEFT OUTER JOIN [dbo].[A] AS [Extent9] ON ([Extent9].[B_Id] IS NOT NULL) AND ([Extent8].[Id] = [Extent9].[B_Id])
        LEFT OUTER JOIN [dbo].[C] AS [Extent10] ON ([Extent10].[B_Id] IS NOT NULL) AND ([Extent8].[Id] = [Extent10].[B_Id]) ) AS [Join8] ON [Extent1].[Id] = [Join8].[Id6]
    LEFT OUTER JOIN  (SELECT [Extent12].[Id] AS [Id2]
        FROM   [dbo].[B] AS [Extent11]
        LEFT OUTER JOIN [dbo].[A] AS [Extent12] ON ([Extent12].[B_Id] IS NOT NULL) AND ([Extent11].[Id] = [Extent12].[B_Id])
        LEFT OUTER JOIN [dbo].[C] AS [Extent13] ON ([Extent13].[B_Id] IS NOT NULL) AND ([Extent11].[Id] = [Extent13].[B_Id]) ) AS [Join11] ON [Extent1].[Id] = [Join11].[Id2]
    LEFT OUTER JOIN  (SELECT [Extent15].[Id] AS [Id7],[Extent16].[Id] AS [Id3]
        FROM   [dbo].[B] AS [Extent14]
        LEFT OUTER JOIN [dbo].[A] AS [Extent15] ON ([Extent15].[B_Id] IS NOT NULL) AND ([Extent14].[Id] = [Extent15].[B_Id])
        LEFT OUTER JOIN [dbo].[C] AS [Extent16] ON ([Extent16].[B_Id] IS NOT NULL) AND ([Extent14].[Id] = [Extent16].[B_Id]) ) AS [Join14] ON [Extent1].[Id] = [Join14].[Id7]
    WHERE (N'10' = [Join2].[Text]) AND ([Join5].[Text] IS NOT NULL)

这对我来说似乎很复杂.它甚至与C“连接”,我没有从A和B引用它,并且不包括在查询中.

现在,事情是：我们有一个包含一些非常复杂的模型的大型项目,我们遇到了一些性能问题,当我们查询查询时,我们发现有些查询需要“包含”很多实体并且它们生成了SQL的超过3k行.

我们的映射或配置中有什么问题吗？我们禁用了延迟加载和代理创建.

解决方法

modelBuilder.Entity<A>().HasOptional(x => x.B).WithMany();
modelBuilder.Entity<C>().HasOptional(x => x.B).WithMany();

它提供与您相同的数据库架构.

为什么我会这样做：因为我经常考虑“我想要什么样的架构？”,并且通过我的实践,对于这个架构,代码就是上面的代码.

BTW：查询ctx.Set< A>().包括(x => x.B).Where(x => x.B.Text ==“10”).选择(x => x)是：

SELECT
    [Extent1].[Id] AS [Id],[Extent3].[Id] AS [Id1],[Extent3].[Text] AS [Text]
FROM   
    [dbo].[A] AS [Extent1]
    INNER JOIN [dbo].[B] AS [Extent2] ON [Extent1].[B_Id] = [Extent2].[Id]
    LEFT OUTER JOIN [dbo].[B] AS [Extent3] ON [Extent1].[B_Id] = [Extent3].[Id]
WHERE 
    N'10' = [Extent2].[Text]

但仍然在这里：为什么extend3？

因为Extend2允许过滤A而没有B：这是内连接… extend2是一个地方(真的很清楚,没有？)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!