EF Fluent API for many with different field id names

Question

In this question: Ef Many To Many , there was an answer on how to manually specify a link table. But I have a slightly unique situation (which, I'm sure, is not unique).

My two tables have an Id field. EG: [dbo].[Account].[Id] and [dbo].[Person].[Id] . Each of these tables in my Code-First has the following OnModelCreating:

 modelBuilder.Entity<Account>.HasKey(x => x.Id); modelBuilder.Entity<Person>.HasKey(x => x.Id); 

But my table is [dbo].[AccountsToPersons]... has EG fields: [AccountId] and [PersonId]

The AccountsToPersons table is not represented by a class in code.

I obviously already have an existing model, but we use the EF Code-First Fluent API instead of updating the model from the database.

So, how do I change this code to work with displaying different identifier column names?

 public DbSet<Person> Persons { get; set; } public DbSet<Account> Accounts { get; set; } . . . modelBuilder.Entity<Account>() .HasMany(a => a.Persons) .WithMany() .Map(x => { x.MapLeftKey("AccountId"); // <-- Account.Id to AccountsToPersons.AccountId?? x.MapRightKey("PersonId"); // <-- Person.Id to AccountsToPersons.PersonId?? x.ToTable("AccountsToPersons"); }); 

When starting a basic Linq To EF query (from x in context.Accounts select x).ToList(); The request fails with the following error:

• "Invalid column name Person_Id."

But when you run Query (from x in context.Persons select x).ToList(); I do not get errors.

Besides the basic typed columns, my models added to them:

 // In my Account Model, I also have this property: public IList<Person> Persons { get; set; } // In my Person Model, I also have this property: public IList<Account> Accounts { get; set; } // <-- in the Person model 

And note that although my Accounts request passes and contains information about the field, the Persons field is always null, although I'm sure there are links in my AccountsToPersons table.

Answer 1

Try adding p => p.Accounts to your WithMany proposal:

 modelBuilder.Entity<Account>() .HasMany(a => a.Persons) .WithMany(p => p.Accounts) // <-- I think this should fix it .Map(x => { x.MapLeftKey("AccountId"); // <-- Account.Id to AccountsToPersons.AccountId?? x.MapRightKey("PersonId"); // <-- Person.Id to AccountsToPersons.PersonId?? x.ToTable("AccountsToPersons"); }); 

Answer 2

I just created a test solution for your problem, and for me it looks like it works.

One thing that I see, you did differently than I did:

 public IList<Person> Persons { get; set; } // <-- in the Account model public IList<Account> Accounts { get; set; } // <-- in the Person model 

Try changing this:

 public DbSet<Person> Persons { get; set; } public DbSet<Account> Accounts { get; set; } 

If this does not work, I will send my entire installation.

My structure looks like this and works for displayed queries:

 public class Person { public int ID { get; set; } public string Name { get; set; } public IList<Account> Accounts { get; set; } } public class Account { public int ID { get; set; } public string Name { get; set; } public IList<Person> Persons { get; set; } } public class ApplicationDbContext : IdentityDbContext<ApplicationUser> { public DbSet<Account> AccountSet { get; set; } public DbSet<Person> PersonSet { get; set; } public ApplicationDbContext() : base("DefaultConnection") { this.Database.Log = (msg) => { Debug.Write(msg); }; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); modelBuilder.Entity<Account>().HasKey(x => x.ID); modelBuilder.Entity<Person>().HasKey(x => x.ID); modelBuilder.Entity<Account>() .HasMany(a => a.Persons) .WithMany() .Map(w => { w.MapLeftKey("AccountId"); w.MapRightKey("PersonId"); w.ToTable("AccountsToPersons"); }); } } 

Answer 3

Have you tried slightly modifying the display of AccountsToPersons:

  modelBuilder.Entity<Account>() .HasMany(a => a.Persons) .WithMany(p => p.Accounts) <-- Change .Map(x => { x.MapLeftKey("AccountId"); // <-- Account.Id to AccountsToPersons.AccountId?? x.MapRightKey("PersonId"); // <-- Person.Id to AccountsToPersons.PersonId?? x.ToTable("AccountsToPersons"); });