EF6 M:M Without Bridge/Junction Class, Unconventional FKs

Question

Trying to wrap my head around M:M with EF6. I have an existing database and am manually handling the models in code, using the fluent API on model creating.

As I've read and as makes sense to me, when a db schema contains a junction table without payload (extra fields), a representative class need not be part of the EF6 model. Rather, each end of the M:M has a collection of it's opposite end, like so:

CREATE TABLE dbo.Personnel (
    ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1, 1),
    CompanyID INT NOT NULL REFERENCES dbo.Company (ID) ON UPDATE CASCADE ON DELETE CASCADE,
    PersonFirstName NVARCHAR(50) NOT NULL
    -- etc etc
);

CREATE TABLE dbo.Roles (
    ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1, 1),
    CompanyID INT NOT NULL REFERENCES dbo.Company (ID) ON UPDATE CASCADE ON DELETE CASCADE,
    RoleName NVARCHAR(50) NOT NULL
    -- etc etc
);

CREATE TABLE dbo.PersonnelRoles (
    ID INT NOT NULL PRIMARY KEY CLUSTERED IDENTITY(1, 1),
    RoleID INT NOT NULL REFERENCES dbo.Roles (ID) -- blah blah
    PersonnelID INT NOT NULL REFERENCES dbo.Personnel(ID) -- blah blah
);

I envision my models as so:

public class Person() 
{
    public int ID {get;set;}
    public int CompanyID {get;set;}
    public string FirstName {get;set;}

    public virtual Company Company {get;set;}
    public virtual ICollection<Role> Roles {get;set;}
}

public class Role() 
{
    public int ID {get;set;}
    public int CompanyID {get;set;}
    public string Name {get;set;}

    public virtual Company Company {get;set;}
    public virtual ICollection<Person> Personnel {get;set;}
}

Question 1

The database junction table has a surrogate primary key of ID. Is this enough to throw off the notion that the EF models shouldn't require a bridge class? Does this "ID" count as an added field which requires that a bridge class be modeled in code? (I was really hoping to avoid that, but external conventions mean I have to use the database schema as-is).

If I'm already doing it wrong, we can skip question 2 and I'll try again with the bridge class.

Question 2

Assuming that I do not in fact need the class bridge and that my two models as shown are correct, how can I use the fluent API to set up the correct FKs? When I try to run insert code now, I get the following:

An error occurred while saving entities that do not expose foreign key properties for their relationship...

The inner exception as advised by the top exception is:

Invalid object name 'dbo.RolePersons'

My DbContext looks like this:

public DbSet<Person> Personnel { get; set; }
public DbSet<Role> Roles { get; set; }

and in OnModelCreating...

        modelBuilder.Entity<Person>().ToTable("Personnel");
        modelBuilder.Entity<Person>().Property(e => e.FirstName).HasColumnName("PersonFirstName");
        modelBuilder.Entity<Person>().Property(e => e.LastName).HasColumnName("PersonLastName");
        modelBuilder.Entity<Person>().Property(e => e.Gender).HasColumnName("PersonGender");

        modelBuilder.Entity<Role>().Property(e => e.Name).HasColumnName("RoleName");
        modelBuilder.Entity<Role>().Property(e => e.Code).HasColumnName("RoleCode");
        modelBuilder.Entity<Role>().Property(e => e.Description).HasColumnName("RoleDescription");

I've looked at related questions and answers and have looked into HasRequired and HasKey, but I'm unsure how to apply them here: neither table requires that roles/persons are in the junction, and either of the two tables references a key to the other...

Can I model this without needing a bridge class, and if so, how do I instruct EF to do so? Thanks.

Answer 1

I believe you have everything set up correctly. The error you are getting is because EF expects the name of the join table to be RolePersons, not PersonnelRoles.

To fix this, you have to specify the relationship in OnModelCreating. You can do this by adding:

modelBuilder.Entity<Role>()
    .HasMany(p => p.Personnel)
    .WithMany(r => r.Roles)
    .Map(r=> { 
        r.ToTable("PersonnelRoles");
        r.MapLeftKey("RoleID");
        r.MapRightKey("PersonnelID");
     });