sql-server - 额外加入非常简单的EF查询以建立1-1关系

我在一个新的新项目上有这个简单的模型:

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();

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

我为什么要这样做:因为我经常从“我想要什么模式？”的角度考虑，而根据我的实践，对于这种模式，代码就是上面的代码。

顺便说一句:对ctx.Set<A>().Include(x => x.B).Where(x => x.B.Text == "10").Select(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允许过滤不带B的A:inner join ... extend2是一个位置(真的很清楚，不？)