Referencing Base Type and Sub Type in same entity results in Ambiguous FK exception

[jamesfera]

When referencing hierarchical relationships, EF Core does not take into account the fact that a sub-type has the same FK as its base-type.

Given that Tiger is guaranteed to have the same ZooId as Animal, it seems like this case should be allowed (unless I'm missing something).

Given this model:

public abstract class Animal
{
    public int Id { get; set; }
    public int ZooId { get; set; }
    public Zoo Zoo { get; set; }
}

public class Tiger : Animal
{

}

This works:

public class Zoo
{
    public int Id { get; set; }
    public IEnumerable<Animal> Animals { get; set; }
}

This works:

public class Zoo
{
    public int Id { get; set; }
    public IEnumerable<Tiger> Tigers { get; set; }
}

This does NOT work:

public class Zoo
{
    public int Id { get; set; }
    public IEnumerable<Animal> Animals { get; set; }
    public IEnumerable<Tiger> Tigers { get; set; }
}

Test:

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

using var sqliteConnection = new SqliteConnection("Data Source=:memory:");
sqliteConnection.Open();

var dbContextOptions = new DbContextOptionsBuilder<TestContext>()
    .UseSqlite(sqliteConnection)
    .EnableDetailedErrors()
    .EnableSensitiveDataLogging()
    .LogTo(Console.WriteLine)
    .Options;

using var context = new TestContext(dbContextOptions);

try
{
    context.Database.EnsureCreated();
}
catch (Exception ex)
{
    Console.WriteLine(ex);
}

Console.ReadKey();

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

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

        modelBuilder.Entity<Zoo>();
        modelBuilder.Entity<Animal>();
        modelBuilder.Entity<Tiger>();
    }
}

public class Zoo
{
    public int Id { get; set; }
    public IEnumerable<Animal> Animals { get; set; }
    public IEnumerable<Tiger> Tigers { get; set; }
}

public abstract class Animal
{
    public int Id { get; set; }
    public int ZooId { get; set; }
    public Zoo Zoo { get; set; }
}

public class Tiger : Animal
{

}

System.InvalidOperationException: Both relationships between 'Animal.Zoo' and 'Zoo.Animals' and between 'Tiger' and 'Zoo.Tigers' could use {'ZooId'} as the foreign key. To resolve this, configure the foreign key properties explicitly in 'OnModelCreating' on at least one of the relationships.
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.ForeignKeyPropertyDiscoveryConvention.ProcessModelFinalizing(IConventionModelBuilder modelBuilder, IConventionContext`1 context)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnModelFinalizing(IConventionModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.OnModelFinalizing(IConventionModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.Model.FinalizeModel()
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelRuntimeInitializer.Initialize(IModel model, Boolean designTime, IDiagnosticsLogger`1 validationLogger)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, ModelCreationDependencies modelCreationDependencies, Boolean designTime)
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel(Boolean designTime)
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
   at Microsoft.EntityFrameworkCore.Infrastructure.EntityFrameworkServicesBuilder.<>c.<TryAddCoreServices>b__8_4(IServiceProvider p)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitFactory(FactoryCallSite factoryCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite callSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.Resolve(ServiceCallSite callSite, ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.DynamicServiceProviderEngine.<>c__DisplayClass2_0.<RealizeService>b__0(ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceProvider.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.get_ContextServices()
   at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
   at Microsoft.EntityFrameworkCore.DbContext.Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure<System.IServiceProvider>.get_Instance()
   at Microsoft.EntityFrameworkCore.Infrastructure.Internal.InfrastructureExtensions.GetService[TService](IInfrastructure`1 accessor)
   at Microsoft.EntityFrameworkCore.Infrastructure.AccessorExtensions.GetService[TService](IInfrastructure`1 accessor)
   at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.get_Dependencies()
   at Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade.EnsureCreated()

EF Core version: 6.0.0

[ajcvickers]

@jamesfera This is allowed, but using the same FK property for multiple relationships requires explicit configuration. For example:

modelBuilder.Entity<Zoo>(b =>
{
    b.HasMany(e => e.Animals).WithOne(e => e.Zoo).HasForeignKey(e => e.ZooId);
    b.HasMany(e => e.Tigers).WithOne(e => e.Zoo).HasForeignKey(e => e.ZooId);
});

[jamesfera]

Thanks @ajcvickers. I tried configuring each one separately but not both at the same time. The error message led me astray...

To resolve this, configure the foreign key properties explicitly in 'OnModelCreating' on at least one of the relationships.

[vlardn]

Unfortunately, the suggested workaround does not work. After adding:

modelBuilder.Entity<Zoo>(b =>
{
    b.HasMany(e => e.Animals).WithOne(e => e.Zoo).HasForeignKey(e => e.ZooId);
    b.HasMany(e => e.Tigers).WithOne(e => e.Zoo).HasForeignKey(e => e.ZooId);
});

to OnModelCreating method in the code above - the next exception appears:

System.InvalidOperationException: Cannot create a relationship between 'Zoo.Tigers' and 'Tiger.Zoo' because a relationship already exists between 'Zoo.Animals' and 'Animal.Zoo'. Navigations can only participate in a single relationship. If you want to override an existing relationship call 'Ignore' on the navigation 'Tiger.Zoo' first in 'OnModelCreating'.
   at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalForeignKeyBuilder.ThrowForConflictingNavigation(IReadOnlyForeignKey foreignKey, IReadOnlyEntityType principalEntityType, IReadOnlyEntityType dependentEntityType, String navigationToDependent, String navigationToPrincipal)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalForeignKeyBuilder.GetOrCreateRelationshipBuilder(EntityType principalEntityType, EntityType dependentEntityType, Nullable`1 navigationToPrincipal, Nullable`1 navigationToDependent, IReadOnlyList`1 dependentProperties, IReadOnlyList`1 oldNameDependentProperties, IReadOnlyList`1 principalProperties, Nullable`1 isRequired, Boolean removeCurrent, Nullable`1 principalEndConfigurationSource, Nullable`1 configurationSource, Nullable`1& existingRelationshipInverted)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalForeignKeyBuilder.ReplaceForeignKey(InternalEntityTypeBuilder principalEntityTypeBuilder, InternalEntityTypeBuilder dependentEntityTypeBuilder, Nullable`1 navigationToPrincipal, Nullable`1 navigationToDependent, IReadOnlyList`1 dependentProperties, IReadOnlyList`1 oldNameDependentProperties, IReadOnlyList`1 principalProperties, Nullable`1 isUnique, Nullable`1 isRequired, Nullable`1 isRequiredDependent, Nullable`1 isOwnership, Nullable`1 deleteBehavior, Boolean removeCurrent, Boolean oldRelationshipInverted, Nullable`1 principalEndConfigurationSource, ConfigurationSource configurationSource)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalForeignKeyBuilder.ReplaceForeignKey(ConfigurationSource configurationSource, InternalEntityTypeBuilder principalEntityTypeBuilder, InternalEntityTypeBuilder dependentEntityTypeBuilder, Nullable`1 navigationToPrincipal, Nullable`1 navigationToDependent, IReadOnlyList`1 dependentProperties, IReadOnlyList`1 oldNameDependentProperties, IReadOnlyList`1 principalProperties, Nullable`1 isUnique, Nullable`1 isRequired, Nullable`1 isRequiredDependent, Nullable`1 isOwnership, Nullable`1 deleteBehavior, Boolean removeCurrent, Nullable`1 principalEndConfigurationSource, Boolean oldRelationshipInverted)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalForeignKeyBuilder.HasNavigations(Nullable`1 navigationToPrincipal, Nullable`1 navigationToDependent, EntityType principalEntityType, EntityType dependentEntityType, ConfigurationSource configurationSource)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.InternalForeignKeyBuilder.HasNavigations(MemberInfo navigationToPrincipal, MemberInfo navigationToDependent, EntityType principalEntityType, EntityType dependentEntityType, ConfigurationSource configurationSource)
   at Microsoft.EntityFrameworkCore.Metadata.Builders.CollectionNavigationBuilder.WithOneBuilder(MemberIdentity reference)
...

Tested on latest EF Core 7.0.13 (and also on above mentioned 6.0.0)

[vlardn]

I found one working workaround (which doesn't use explicit FK configuration) but it looks more like a limited hack:

public class Zoo
{
    public int Id { get; set; }
    public IEnumerable<Animal> Animals { get; set; }
    // public IEnumerable<Tiger> Tigers { get; set; } <= THIS DOESN'T WORK!
    [NotMapped] public IEnumerable<Tiger> Tigers => Animals.OfType<Tiger>();
}

The loading of Zoo entity with Tigers property only becomes more complex with this workaround:

var zoo = context.Zoos.Include(x => x.Animals.Where(y => y is Tiger)).First();

and the loading of nested properties like Zoo.Tigers.Sponsors with Include or nested querying like Zoos.Where(z => z.Tigers.Any(t => t.Name == "Ava")) is not possible at all here.

So I still think that navigation for sub-types (Zoo.Tigers in this case) could and should be implemented natively by EF (for all inheritance types, i.e.: TPH, TPT, and TPC).

@ajcvickers please reopen this issue.

[vlardn]

@roji Hi MS team, should I create a new issue or you can reopen this one?