Extra joins on very simple EF query for 1-1 relationship

Question

I have this simple model on a fresh new project:

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; }
}

The mappings are:

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

I am running this query:

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

Where db.As is the DbSet of A's.

It generates the following 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)

which seems complicated to me. It even "joins" with C which I don't reference from A nor B and is not included in the query.

Now, the thing is: we have a large project with some very complex models, we are having some performance issues, when we profiled the queries we found that there were queries that needed to "include" a lot of entities and they generated SQL of more than 3k lines.

Is there something wrong in our mappings or our configuration? We have lazy loading and proxy creation disabled.

Answer 1

I have no real answer for you, but with the same specifications I would have writen:

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

which gives the same database schema as yours.

Why would I have done that: because I often think in terms of 'what schema do I want ?', and with my practice, for this schema, the code is the one above.

BTW: resulting query for ctx.Set<A>().Include(x => x.B).Where(x => x.B.Text == "10").Select(x => x) is:

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]

but still here: why extend3 ?

Because Extend2 allows to filter the A with no B: that is inner join ... extend2 is a where (really clear, no ?)