ASP.NET MVC w/ Entity Framework - UpdateException

Question

Hi All,

I am encountering a problem when I go to update an object in my database using Entity Framework.

Firstly, the concept is:

A user can be a member of a group, hence a Group contains Users.

To model this in the database, I have the following structure:

Users: ID Name etc.

Groups: ID Name

GroupMembers: GroupID UserID

Both fields in GroupMembers are foreign keys relating back to the User ID and Group ID. When I load this into Entity Framework, it is modelled correctly in that a Group object has a list of User objects, and each User object has a list of Group objects.

However, when I go to add a User to a Group, I get the following problem:

var group = DAO.GetGroup(GroupID);
var user = DAO.GetUser(UserID);
group.Users.Add(user);
conn.SaveChanges();

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

I have seen various pieces online advising to add an ID field to the GroupMembers table - however if I do that, I get a mapping error in EF that complains about the ID not being mapped to anything.

Would anybody be able to lend some other advice, or a workaround?

Thanks,

Chris

Answer 1

Hi Craig,

By making both fields in GroupMembers the primary key, I can now add to the table.

Thanks for your help :-)

Chris

Answer 2

Remove the ID column from GroupMembers. Create a PK for GroupMembers consisting of both GroupID and UserID. Now right-click your model and update it from the DB.

The EF designer needs to know that the pair of GroupID and UserID is unique in order to get the cardinality right.