How to save the values in an Eager Loaded class?

Question

To add a role into my user and save it:

var user = DataContext.Users
    .Include("Roles")
    .Where(u => u.UserName.ToUpper() == username.ToUpper())
    .FirstOrDefault();
foreach (string rolename in roleNames)
{
    var role = FindRoleByName(rolename);
    user.Roles.Add(role);
    DataContext.Entry(user).State = EntityState.Modified; // probably not needed
    DataContext.SaveChanges();
}

But it throws the exception

[System.Data.Entity.Infrastructure.DbUpdateException]   
{"Unable to update the EntitySet 'UserRoles' because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation."} System.Data.Entity.Infrastructure.DbUpdateException

What is the correct way to write this update?

------Update--------

If I remove the <DefiningQuery> section in the edmx model, the eager loading will fail at .include("Roles") part in the LINQ statement.

Also, I found some error comment in the edmx (in xml editor). I guess that is where the problem is. It says:

<!--Errors Found During Generation:
warning 6002: The table/view 'DNR.dbo.UserRoles' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.
-->

So, do I have to add a primary key in the joining table along with my UserId_FK and RoleId_FK?

score 2 · Answer 1 · answered Aug 07 '12 at 14:09

2

This sounds like a mapping problem. It happens for example if the entities are mapped from a database view and/or the primary key is missing.

You can find a few possible solutions at the MSDN Forum: Unable to update the EntitySet

answered Aug 07 '12 at 14:09

Dennis Traub

50,557
7
93
108

Thanks. It looks like pointing to the right direction. But simply removing the `` causes breakup of all eager loading. I am looking at more articles on this topic. – Blaise Aug 07 '12 at 14:35
In the backend database, the joining table contains `UserId_FK`, `RoleId_FK`. Is it required to have a primary key like `UserRoleId_PK`? – Blaise Aug 07 '12 at 14:36
@Blaise: No, you don't need a separate PK column. Just define a composite PK consisting of `UserId_FK` **and** `RoleId_FK`. (In SSMS click the PK button twice - once for each column.) – Slauma Aug 07 '12 at 22:58

How to save the values in an Eager Loaded class?

1 Answers1