Entity Framework 7 Many-To-Many (intersect table) "skip navigation" problem-CodePudding

I am migrating a project from NHibernate to EF and I have just begun using EF7. Since it's new there don't seem to be many posts on this specifically. I've found several on older EF versions, but they don't apply/work. I'm having a tough time getting Many-To-Many relations configured using intersect tables I'm using this example basically verbatim and I get a runtime error saying that my object's property does not have a foreign key.

My objects are as follows:

public partial class Casino
    {
        // other properties
        private IList<Country> _prohibitedCountries;
        public IList<Country> ProhibitedCountries
        {
            get { return _prohibitedCountries; }
            protected set { _prohibitedCountries = value; }
        }
    }

public partial class Country
{
        // other properties
        private IList<Casino> _prohibitedCasinos;
        public IList<Casino> ProhibitedCasinos
        {
            get { return _prohibitedCasinos; }
            protected set { _prohibitedCasinos = value; }
        }
}

So a casino can have many prohibited countries and vice versa.

My DB Context is defined as follows:

    public class DsDbContext : DbContext
    {
        public DbSet<Casino> Casinos { get; set; }
        public DbSet<Country> Countries { get; set; }
        // Other collections...

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                IConfigurationRoot configuration = new ConfigurationBuilder()
                   .SetBasePath(Directory.GetCurrentDirectory())
                   .AddJsonFile("appsettings.json")
                   .Build();
                var connectionString = configuration.GetConnectionString("DataStoreDatabase");
                optionsBuilder.UseSqlServer(connectionString);
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder
                .Entity<Casino>()
                .HasMany(a => a.ProhibitedCountries)
                .WithMany(b => b.ProhibitedCasinos)
                .UsingEntity<Dictionary<string, object>>(
                    "Casino_Prohibited_Country",
                    j => j
                        .HasOne<Country>()
                        .WithMany()
                        .HasForeignKey("CountryId")
                        .HasConstraintName("FK_CasinoProhibitedCountry_Country")
                        .OnDelete(DeleteBehavior.Cascade) ,
                     j => j
                         .HasOne<Casino>()
                         .WithMany()
                         .HasForeignKey("CasinoId")
                         .HasConstraintName("FK_CasinoProhibitedCountry_Casino")
                         .OnDelete(DeleteBehavior.Cascade)
                );

            modelBuilder
                .Entity<Country>()
                .HasMany(c => c.ProhibitedCasinos)
                .WithMany(c => c.ProhibitedCountries)
                .UsingEntity<Dictionary<string, object>>(
                    "Casino_Prohibited_Country",
                    j => j
                        .HasOne<Casino>()
                        .WithMany()
                        .HasForeignKey("CasinoId")
                        .HasConstraintName("FK_CasinoProhibitedCountry_Casino")
                        .OnDelete(DeleteBehavior.Cascade),
                     j => j
                         .HasOne<Country>()
                         .WithMany()
                         .HasForeignKey("CountryId")
                         .HasConstraintName("FK_CasinoProhibitedCountry_Country")
                         .OnDelete(DeleteBehavior.Cascade)
                );

}

and here is the script for the intersect table showing the constraints and FKs. I'd prefer not to have an entity that corresponds to each intersect table like this. I did try that configuration and got exactly the same runtime error

CREATE TABLE [dbo].[Casino_Prohibited_Country](
    [CasinoId] [int] NOT NULL,
    [CountryId] [int] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Casino_Prohibited_Country]  WITH NOCHECK ADD  CONSTRAINT [FK_CasinoProhibitedCountry_Casino] FOREIGN KEY([CasinoId])
REFERENCES [dbo].[Casino] ([Id])
GO

ALTER TABLE [dbo].[Casino_Prohibited_Country] CHECK CONSTRAINT [FK_CasinoProhibitedCountry_Casino]
GO

ALTER TABLE [dbo].[Casino_Prohibited_Country]  WITH NOCHECK ADD  CONSTRAINT [FK_CasinoProhibitedCountry_Country] FOREIGN KEY([CountryId])
REFERENCES [dbo].[Country] ([Id])
GO

ALTER TABLE [dbo].[Casino_Prohibited_Country] CHECK CONSTRAINT [FK_CasinoProhibitedCountry_Country]
GO

When I execute my unit tests which just does some basic CRUD stuff, I get a runtime error in the constructor of my base repository

public BaseRepository(DsDbContext context)
{
    _context = context;
    _dbSet = context.Set<TEntity>(); // The error below is thrown on this line
}

System.InvalidOperationException: 'The skip navigation 'Country.ProhibitedCasinos' doesn't have a foreign key associated with it. Every skip navigation must have a configured foreign key.'

I don't understand why EF is not finding the foreign key when it is explicitly stated in the config. EF is able to access the DB and read records for an entity that has no relationships

CodePudding user response：

I found a (highly counter-intuitive) solution from a Microsoft engineer on the EFCore github repo

Essentially, I needed to do was remove this: .HasConstraintName("FK_CasinoProhibitedCountry_Country")

which makes zero sense when the error message was:

System.InvalidOperationException: 'The skip navigation 'Country.ProhibitedCasinos' doesn't have a foreign key associated with it. Every skip navigation must have a configured foreign key.'

The other change was in line with what Ivan Stoev suggested in his comment: remove the second definition.

Below is the new/working config. Note the missing HasConstraintName setting which is present in the MS documentation. Also, note that this is configured only on the Casino entity and not the reverse config on the Country entity as well.

    modelBuilder.Entity<Casino>()
        .HasMany(e => e.ProhibitedCountries)
        .WithMany(e => e.ProhibitedCasinos)
        .UsingEntity<Dictionary<string, object>>(
            "Casino_Prohibited_Country",
            e => e
            .HasOne<Country>()
            .WithMany()
            .HasForeignKey("CountryId"),
            e => e
            .HasOne<Casino>()
            .WithMany()
            .HasForeignKey("CasinoId")
            );

The MS Engineer on git suggested making a compound primary key from the two columns of the Casino_Prohibited_Country intersect table, but I did not do that and it still worked, so this does not appear to be necessary