Having inheritance creates excessive query

[RamunasAdamonis]

I had a simple class with some value objects, but decided to extend it by using inheritance and having discriminator. It started to generate some excessive queries. I replicated this with sample below:

using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;


public class JustARegularClass
{
    [Key]
    public int Id { get; set; }
    public ValueObject VO1 { get; set; }
    public ValueObject VO2 { get; set; }
    public ValueObject VO3 { get; set; }
    public ValueObject VO4 { get; set; }
    public ValueObject VO5 { get; set; }
}

public abstract class BaseClass
{
    [Key]
    public int Id { get; set; }
    public ValueObject VO1 { get; set; }
    public ValueObject VO2 { get; set; }
    public ValueObject VO3 { get; set; }
    public ValueObject VO4 { get; set; }
    public ValueObject VO5 { get; set; }
}

public class ChildClass : BaseClass
{
    public int MyColA { get; set; }
}

public class ValueObject
{
    public ValueObject(int myInt, string myString)
    {
        MyInt = myInt;
        MyString = myString;
    }

    public int MyInt { get; private set; }
    public string MyString { get; private set; }
}

public enum MyType
{
    Type0 = 0,
    Type1 = 1
}

public class MyContext : DbContext
{
    public MyContext(DbContextOptions options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<JustARegularClass>(e =>
        {
            e.OwnsOne(nav => nav.VO1);
            e.OwnsOne(nav => nav.VO2);
            e.OwnsOne(nav => nav.VO3);
            e.OwnsOne(nav => nav.VO4);
            e.OwnsOne(nav => nav.VO5);
        });

        modelBuilder.Entity<BaseClass>(e =>
        {
            e.OwnsOne(nav => nav.VO1);
            e.OwnsOne(nav => nav.VO2);
            e.OwnsOne(nav => nav.VO3);
            e.OwnsOne(nav => nav.VO4);
            e.OwnsOne(nav => nav.VO5);
        });

        modelBuilder.Entity<BaseClass>().HasDiscriminator<MyType>(nameof(MyType))
            .HasValue<BaseClass>(MyType.Type0)
            .HasValue<ChildClass>(MyType.Type1);
    }

    public DbSet<JustARegularClass> RegularClasses { get; set; }
    public DbSet<ChildClass> ChildClasses { get; set; }
    public DbSet<BaseClass> BaseClasses { get; set; }
}

using Microsoft.AspNetCore.Mvc;

namespace ef.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ValuesController : ControllerBase
    {
        private readonly MyContext _context;
        public ValuesController(MyContext context)
        {
            _context = context;
        }

        [HttpGet("RegularClass")]
        public ActionResult<JustARegularClass> GetRegular()
        {
            var result = _context.RegularClasses.Find(1);
            return Ok(result);
        }

        [HttpGet("ChildClass")]
        public ActionResult<ChildClass> GetChild()
        {
            var result = _context.ChildClasses.Find(1);
            return Ok(result);
        }
    }
}

Calling api/values/RegularClass generates normal query:

exec sp_executesql N'SELECT TOP(1) [e].[Id], [e].[Id], [e].[VO5_MyInt], [e].[VO5_MyString], [e].[Id], [e].[VO4_MyInt], [e].[VO4_MyString], [e].[Id], [e].[VO3_MyInt], [e].[VO3_MyString], [e].[Id], [e].[VO2_MyInt], [e].[VO2_MyString], [e].[Id], [e].[VO1_MyInt], [e].[VO1_MyString]
FROM [RegularClasses] AS [e]
WHERE [e].[Id] = @__get_Item_0',N'@__get_Item_0 int',@__get_Item_0=1

while calling api/values/ChildClass generates this monster query:

exec sp_executesql N'SELECT TOP(1) [e].[Id], [e].[MyType], [e].[MyColA], [t].[Id], [t].[VO5_MyInt], [t].[VO5_MyString], [t0].[Id], [t0].[VO4_MyInt], [t0].[VO4_MyString], [t1].[Id], [t1].[VO3_MyInt], [t1].[VO3_MyString], [t2].[Id], [t2].[VO2_MyInt], [t2].[VO2_MyString], [t3].[Id], [t3].[VO1_MyInt], [t3].[VO1_MyString]
FROM [BaseClasses] AS [e]
LEFT JOIN (
    SELECT [e.VO5].*
    FROM [BaseClasses] AS [e.VO5]
    WHERE [e.VO5].[MyType] = 1
) AS [t] ON [e].[Id] = [t].[Id]
LEFT JOIN (
    SELECT [e.VO4].*
    FROM [BaseClasses] AS [e.VO4]
    WHERE [e.VO4].[MyType] = 1
) AS [t0] ON [e].[Id] = [t0].[Id]
LEFT JOIN (
    SELECT [e.VO3].*
    FROM [BaseClasses] AS [e.VO3]
    WHERE [e.VO3].[MyType] = 1
) AS [t1] ON [e].[Id] = [t1].[Id]
LEFT JOIN (
    SELECT [e.VO2].*
    FROM [BaseClasses] AS [e.VO2]
    WHERE [e.VO2].[MyType] = 1
) AS [t2] ON [e].[Id] = [t2].[Id]
LEFT JOIN (
    SELECT [e.VO1].*
    FROM [BaseClasses] AS [e.VO1]
    WHERE [e.VO1].[MyType] = 1
) AS [t3] ON [e].[Id] = [t3].[Id]
WHERE ([e].[MyType] = 1) AND ([e].[Id] = @__get_Item_0)',N'@__get_Item_0 int',@__get_Item_0=1

If I compare the execution plans I get this:

My query now runs 6 times slower because of what seems to be simple design upgrade. Any thoughts on this?

Further technical details

EF Core version: 2.2.3
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio Code 1.33.1

[ajcvickers]

@smitpatel is this a duplicate?

[smitpatel]

No.

[divega]

Note from triage: We will see if we can optimize this in 3.0 or otherwise will punt.

[smitpatel]

In new pipeline, we will at least not cause subqueries. We still generated joins which we can optimize easier.

[AndriySvyryd]

@smitpatel Are you sure this is not a duplicate of #3419?

[smitpatel]

This and #3419 both are them are not capturing the join elimination we are going to do. That is remove join for the cases when the table is same in database. I would suggest filing new issue and close both of them.

[smitpatel]

Using 5.0 preview7
Generated SQLs

      SELECT TOP(1) [r].[Id], [r].[VO1_MyInt], [r].[VO1_MyString], [r].[VO2_MyInt], [r].[VO2_MyString], [r].[VO3_MyInt], [r].[VO3_MyString], [r].[VO4_MyInt], [r].[VO4_MyString], [r].[VO5_MyInt], [r].[VO5_MyString]
      FROM [RegularClasses] AS [r]
      WHERE [r].[Id] = @__p_0

      SELECT TOP(1) [b].[Id], [b].[MyType], [b].[MyColA], [b].[VO1_MyInt], [b].[VO1_MyString], [b].[VO2_MyInt], [b].[VO2_MyString], [b].[VO3_MyInt], [b].[VO3_MyString], [b].[VO4_MyInt], [b].[VO4_MyString], [b].[VO5_MyInt], [b].[VO5_MyString]
      FROM [BaseClasses] AS [b]
      WHERE [b].[Id] = @__p_0

[RamunasAdamonis]

Great news, thank you!