EF Core 5 Daily - HasData / ToTable give - The skip navigation 'xxxxxxx' doesn't have a foreign key associated with it. Every skip navigation must have a configured foreign key.

[kccsf]

Allowing EF Core to create many to many relationship. Seeding data or renaming the automatically created join table as follows:

   modelBuilder.Entity("CommunityPerson").HasData(new { MembersId = 1, MembershipsId = 1 });
   modelBuilder.Entity("CommunityPerson").ToTable("CommunityPersonTest");

Both throw:

System.InvalidOperationException: The skip navigation 'Community.Members' doesn't have a foreign key associated with it. Every skip navigation must have a configured foreign key.
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.ValidateRelationships(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Sqlite.Internal.SqliteModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.ValidatingConvention.ProcessModelFinalized(IModel model)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnModelFinalized(IModel model)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.OnModelFinalized(IModel model)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.Model.FinalizeModel()
   at Microsoft.EntityFrameworkCore.ModelBuilder.FinalizeModel()
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder, ModelDependencies modelDependencies)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, IConventionSetBuilder conventionSetBuilder, ModelDependencies modelDependencies)
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
   at Microsoft.EntityFrameworkCore.Infrastructure.EntityFrameworkServicesBuilder.<>c.<TryAddCoreServices>b__7_3(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 singletonCallSite, 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 singletonCallSite, 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__DisplayClass1_0.<RealizeService>b__0(ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngine.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_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.Design.Internal.DbContextOperations.CreateContext(Func`1 factory)
   at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.CreateContext(String contextType)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.AddMigration(String name, String outputDir, String contextType, String namespace)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigrationImpl(String name, String outputDir, String contextType, String namespace)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigration.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)

If I configure the many to many relationship manually as follows, they work:

            modelBuilder.Entity<Community>()
                .HasMany(x => x.Members)
                .WithMany(x => x.Memberships)
                .UsingEntity<Dictionary<string, object>>(
                    "CommunityPerson",
                    x => x.HasOne<Person>().WithMany(),
                    x => x.HasOne<Community>().WithMany());

Steps to reproduce

namespace EFTest
{
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using Microsoft.EntityFrameworkCore;

    public class CommunitiesContext : DbContext
    {
        public DbSet<Community> Communities { get; set; }

        public DbSet<Person> People { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
            => options.UseSqlite("Data Source=test.db");

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // modelBuilder.Entity<Community>()
            //     .HasMany(x => x.Members)
            //     .WithMany(x => x.Memberships)
            //     .UsingEntity<Dictionary<string, object>>(
            //         "CommunityPerson",
            //         x => x.HasOne<Person>().WithMany(),
            //         x => x.HasOne<Community>().WithMany());

            modelBuilder.Entity<Community>().HasData(new Community { Id = 1, Name = ".Net Foundation" });
            modelBuilder.Entity<Person>().HasData(new Person { Id = 1, Name = "Geoff Smith" });

            // The following both fail without the manual configuration commented above
            modelBuilder.Entity("CommunityPerson").HasData(new { MembersId = 1, MembershipsId = 1 });
            modelBuilder.Entity("CommunityPerson").ToTable("CommunityPersonTest");
        }
    }

    public class Community
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public ICollection<Person> Members { get; } = new List<Person>();
    }

    public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public ICollection<Community> Memberships { get; } = new List<Community>();
    }
    
    class Program
    {
        static void Main(string[] args)
        {
            using var context = new CommunitiesContext();
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();
            var communities = context.Communities.Include(x => x.Members).ToList();
            foreach (var member in communities.SelectMany(x => x.Members))
            {
                    Console.Write($"{member.Name}");
            }
        }
    }
}

Further technical details

EF Core version: 6.0.0-alpha.1.20462.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer / Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1 / .NET Core 5.0
Operating system: Windows 10
IDE: Visual Studio 2019 Preview 16.8.0 Preview 2.1 /

[smitpatel]

You should use SharedEntity method to refer to the join entity as it is a shared type entity type. Using Entity here is making that entity type to be removed from skip navigations. Though it should not throw exception.

[kccsf]

Thanks Smit - not sure if you meant using Entity shouldn't throw an exception - for the avoidance of doubt, using SharedTypeEntity as per below throws also:

   modelBuilder.SharedTypeEntity<Dictionary<string, object>>("CommunityPerson").HasData(new { MembersId = 1, MembershipsId = 1 });
   modelBuilder.SharedTypeEntity<Dictionary<string, object>>("CommunityPerson").ToTable("CommunityPersonTest");

[ajcvickers]

@AndriySvyryd @smitpatel I get the same exception even when using SharedTypeEntity. I can only make it work when the relationship is specified explicitly.

Works:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Community>()
        .HasMany(x => x.Members)
        .WithMany(x => x.Memberships)
        .UsingEntity<Dictionary<string, object>>(
            "CommunityPerson",
            x => x.HasOne<Person>().WithMany(),
            x => x.HasOne<Community>().WithMany());

    modelBuilder.Entity<Community>().HasData(new Community { Id = 1, Name = ".Net Foundation" });
    modelBuilder.Entity<Person>().HasData(new Person { Id = 1, Name = "Geoff Smith" });

    // The following both fail without the manual configuration commented above
    modelBuilder.SharedTypeEntity<Dictionary<string, object>>("CommunityPerson").HasData(new { MembersId = 1, MembershipsId = 1 });
    modelBuilder.SharedTypeEntity<Dictionary<string, object>>("CommunityPerson").ToTable("CommunityPersonTest");
}

Stack trace when many-to-many is built by convention:

/usr/share/dotnet/dotnet /home/ajcvickers/AllTogetherNow/Daily/bin/Debug/netcoreapp3.1/Daily.dll
Unhandled exception. System.InvalidOperationException: The skip navigation 'Community.Members' doesn't have a foreign key associated with it. Every skip navigation must have a configured foreign key.
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.ValidateRelationships(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Infrastructure.RelationalModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Sqlite.Internal.SqliteModelValidator.Validate(IModel model, IDiagnosticsLogger`1 logger)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.ValidatingConvention.ProcessModelFinalized(IModel model)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnModelFinalized(IModel model)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.OnModelFinalized(IModel model)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.Model.FinalizeModel()
   at Microsoft.EntityFrameworkCore.ModelBuilder.FinalizeModel()
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder, ModelDependencies modelDependencies)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, IConventionSetBuilder conventionSetBuilder, ModelDependencies modelDependencies)
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
   at Microsoft.EntityFrameworkCore.Infrastructure.EntityFrameworkServicesBuilder.<>c.<TryAddCoreServices>b__7_3(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 singletonCallSite, 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 singletonCallSite, 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__DisplayClass1_0.<RealizeService>b__0(ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngine.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_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.EnsureDeleted()
   at Program.Main(String[] args) in /home/ajcvickers/AllTogetherNow/Daily/Daily.cs:line 63