Including sub-collection for derived type returns repeating items

[gterdem]

Including sub-collection causes repeated entity results, based on added sub-collection item.

Code below is psudo-kind, working sample link is here

public class Plane/Automobile/Yact : VehicleBase (abstract)
{
    public Guid Id { get; private set; }
    public ICollection<Driver> Drivers { get; private set; }
}

public class Driver
    {
        public Guid Id { get; private set; }
        public Guid VehicleId { get; set; } //Same id for all derived classes.
    }

When trying to query like var vehiclesWithDrivers = await _context.Vehicles.AsNoTracking().Include("Drivers").ToListAsync(); result has repeating entities based on sub-collection members they have.

DbContext

public class AppDbContext : DbContext
    {
        public DbSet<VehicleBase> Vehicles { get; set; }
        public DbSet<Driver> Drivers { get; set; }
        public DbSet<Automobile> Automobiles { get; set; }
        public DbSet<Plane> Planes { get; set; }
        public DbSet<Yact> Yacts { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<VehicleBase>(vb =>
            {
                vb.ToTable("Vehicles");
                vb.HasDiscriminator<VehicleTypes>("Type")
                    .HasValue<Drone>(VehicleTypes.Drone)
                    .HasValue<Automobile>(VehicleTypes.Automobile)
                    .HasValue<Plane>(VehicleTypes.Plane)
                    .HasValue<Yact>(VehicleTypes.Yact);
            });

            modelBuilder.Entity<Automobile>(a =>
            {
                a.HasMany(v => v.Drivers)
                    .WithOne()
                    .HasForeignKey(q => q.VehicleId)
                    .OnDelete(DeleteBehavior.ClientCascade)
                    .IsRequired();
            });

            modelBuilder.Entity<Plane>(a =>
            {
                a.HasMany(v => v.Drivers)
                    .WithOne()
                    .HasForeignKey(q => q.VehicleId)
                    .OnDelete(DeleteBehavior.ClientCascade)
                    .IsRequired();
            });

            modelBuilder.Entity<Yact>(a =>
            {
                a.HasMany(v => v.Drivers)
                    .WithOne()
                    .HasForeignKey(q => q.VehicleId)
                    .OnDelete(DeleteBehavior.ClientCascade)
                    .IsRequired();
            });
        }

Steps to reproduce

I have created a sample project here at https://github.com/gterdem/EfCore-Inheritance.

Further technical details

EF Core version: 3.1.6 Database provider: Microsoft.EntityFrameworkCore.SqlServer and SqLite Target framework: .NET Core 3.1.301 Operating system: Windows 10

[ajcvickers]

Note for triage: reproduces on latest daily, but I haven't dug into the model at all.

Query:

DbSet<VehicleBase>()
    .AsNoTracking()
    .Include("Drivers")

SQL

SELECT [v].[Id], [v].[Name], [v].[Type], [d].[Id], [d].[HasLicense], [d].[Title], [d].[VehicleId], [d0].[Id], [d0].[HasLicense], [d0].[Title], [d0].[VehicleId], [d1].[Id], [d1].[HasLicense], [d1].[Title], [d1].[VehicleId]
FROM [Vehicles] AS [v]
LEFT JOIN [Drivers] AS [d] ON [v].[Id] = [d].[VehicleId]
LEFT JOIN [Drivers] AS [d0] ON [v].[Id] = [d0].[VehicleId]
LEFT JOIN [Drivers] AS [d1] ON [v].[Id] = [d1].[VehicleId]
ORDER BY [v].[Id], [d].[Id], [d0].[Id], [d1].[Id]

[smitpatel]

Data repeats or just the tables in SQL? SQL will have repetition since they are 3 different navigations - 1 in each derived type.

[ajcvickers]

@smitpatel

[smitpatel]

Related dotnet/efcore#20335

Issue here, the FK is shared across all derived type. So when generating joins, we don't account for discriminator, which generates extra rows in SQL result. When generating collections out of it in client side everything falls apart.

A proper SQL accounting for Discriminator would be

SELECT [v].[Id], [v].[Name], [v].[Type], [d].[Id], [d].[HasLicense], [d].[Title], [d].[VehicleId], [d0].[Id], [d0].[HasLicense], [d0].[Title], [d0].[VehicleId], [d1].[Id], [d1].[HasLicense], [d1].[Title], [d1].[VehicleId]
FROM [Vehicles] AS [v]
LEFT JOIN [Drivers] AS [d] ON [v].[Id] = [d].[VehicleId] AND [v].[Type] = 1
LEFT JOIN [Drivers] AS [d0] ON [v].[Id] = [d0].[VehicleId] AND [v].[Type] = 2
LEFT JOIN [Drivers] AS [d1] ON [v].[Id] = [d1].[VehicleId] AND [v].[Type] = 3
ORDER BY [v].[Id], [d].[Id], [d0].[Id], [d1].[Id]

[ajcvickers]

Note from triage: we should look at whether it is possible/desirable to do anything automatically here, or rather if we should document including the discriminator in the FK manually for cases like this.

[ajcvickers]

Note from triage: putting this in the 7.0 milestone to make sure we consider the design in 7.0. The outcome of that will determine together with prioritization against other work will determine whether or not this is implemented in 7.0.

[ajcvickers]

Note from triage: doc this.