Using Entity Framework, how do I reflect a many to many relationship and add entites that exist to a new entity being created?

Question

I'm new to Entity Framework and am trying to figure things out. I have a database created that's not very complicated. There's about 7 tables and 3 of those are mapping tables to associate one table record w/ another. The example I'm using here is this:

• Table User

  • UserId
  • UserName

• Table Role

  • RoleId
  • RoleName

• Table: UserRole

  • UserId
  • RoleId

The foreign keys are mapped in my database. When I create a new Entity Model inside of VS 2008, the diagram seems to have the relationships correct, but does not create a table for the UserRole table. The relationship is mapped as a Many to Many between User & Role.

My problem is that I can create new users and I can create new roles, but I cannot figure out how to create a new user with an existing role. Furthermore, the UserRole table is probably not mapped correctly within the model to begin with. Here's my code so far:

ObjectQuery<Role> roles = context.Roles;
Role role = context.Roles.Where(c => c.RoleName == "Subscriber").First();

 User user = new User
 { 
  DisplayName = "TestCreate2",
  Email = "[email protected]",
  Password = "test"
 };            
 context.AttachTo("Roles", role);
 user.Roles.Add(role);            
 context.AddToUsers(user);
 context.SaveChanges();

Here's the error I am getting:

Unable to update the EntitySet 'UserRoles' because it has a DefiningQuery and no element exists in the element to support the current operation.

Here is the xml that pertains to the UserRole table:

<EntitySet Name="UserRoles" EntityType="RememberTheJourneyModel.Store.UserRoles" store:Type="Tables" store:Schema="dbo" store:Name="UserRoles">
        <DefiningQuery>SELECT 
  [UserRoles].[Role_id] AS [Role_id], 
  [UserRoles].[User_id] AS [User_id]
  FROM [dbo].[UserRoles] AS [UserRoles]</DefiningQuery>
      </EntitySet>

It was pulling teeth just to figure out how to query the context such that it gave me an actual Role entity. I'm sure the issue is something to do with how UserRole is mapped, but I'm just getting started here and haven't any idea where that might have gone wrong.

And I really have searched google & this site, but I suppose I haven't come up with the right search parameters to find a question that helps me fix this. I found one question that said EF has issues with this, but if you're mapping table makes both columns a primary key, it works itself out. I'm not sure how to do that. Is this done in the database (using SQL SERVER 2005 EXPRESS) or in the mapping? This is a personal project so I can post more details about the code or xml if needed. Any and all help will be appreciated.

Answer 1

I remember running into this issue before as well... and I believe I fixed it by making both columns keys in my association table.

You can make both columns the primary key in SQL Server Management Studio table designer. Just highlight both the User_Id and Role_Id at the same time, right click on the left and select Primary Key... this will make it so that no record can have the same combination of User_Id and Role_Id.. making each entry unique.

Then you can update your data model in the Entity Framework designer.. and hopefully be good to go.

I believe that if a table does not have any keys defined to ensure records are unique, then EF would not be able to update records properly, and so the framework guards against that case.