Entity Framework Many TO Many Relationship with Primary Key

Question

I have the following Schema:

User Table: (Primary Key)

1. UserId
2. CustomerId

Role Table: (Primary Key)

1. UserId
2. CustomerId

UserRole Table:

1. UserRoleId (UNIQUEIDENTIFIER (newsequentialid)) Primary Key
2. UserId
3. Customerid
4. RoleId

Those tables participate in many to many relationship (UserRole). I am using Entity Framework code first with mapping classes to define the database tables. So, In my mapping class for User Table, I have the following:

 this.HasMany(u => u.Roles)
      .WithMany()
      .Map(m =>
        {
          m.MapLeftKey(new string[] { "CustomerID", "UserID" });
          m.MapRightKey(new string[] {"CustomerID", "RoleID"});
          m.ToTable("UserRoles");
        }
      );

Entity framework is failing with this message: "One or more validation errors were detected during model generation: CustomerID: Name: Each property name in a type must be unique. Property name 'CustomerID' is already defined. UserRole: EntityType: EntitySet 'UserRole' is based on type 'UserRole' that has no keys defined.

is it possible to tell Code First that the Primary Key for my "UserRole" is UserRoleId?

The issue is when Entity Framework tries to create the UserRole Table, it would use all columns of MapLeftKey and MapRightKey to Create UserRole with PrimaryKey that has all those columns.

Any suggestions?

Answer 1

You need to model your classes similar to your DB, why don't you simply add the association tables? I mocked up your DB and there is no problem as long as you model all the tables.

Test it for yourself, create an EF project .edmx using code first from existing DB, I think the answer will be obvious.