InvalidOperationException with add\remove op. in EntitySet

[fmarakasov]

Hey!
I have a problem with LinqConnect when try to add and remove rows in the EntitySet. My Oracle database contains three tables (let's say User-UserRole-Role):
R(User)={ID(PK), Name}
R(Role) = {ID(PK), Name}
R(UserRole) = {UserID(PK/FK), RoleID(PK/FK), Description}

I have a WPF app, where i'm trying to give a user UI for editig UserRole data through the DataGrid control. For that purpose a have two commands: for add new row to UserRoles collection and remove one. In the very simplify way:

Code: Select all

public class ViewModel
{
    User User {get;set;}
    
    public AddUserRole(Role role)
    {
          if (user.UserRoles.Any(x=>x.Role == role)) return;  
          var newItem = new  UserRole(){Role = role};
          UserRoles.Add(newItem);
    }
    public RemoveUserRole(UserRole userRole)
    {
          if (!UserRoles.Contains(userRole)) return;
          UserRoles.Remove(userRole);
    } 

    IBindingList _userRoles;
    public IBindingList UserRoles
    {
         get
         {
              if (_userRoles  == null) _userRoles = user.UserRoles.GetNewBindingList();
              return _userRoles;  
         }         
    }
}

Then i wrote a simple test:

Code: Select all

[TestMethod()]
void TestInsertRemove()
{ 
     using (var db = new MyDataContext())
     {
           ViewModel vm = new ViewModel();
           vm.User = db.Users.First();
           Role role = db.Roles.First();

           // Assume UserRoles table is empty - the user has no any roles
           Assert.AreEqual(0, vm.User.UserRoles.Count); // OK
           vm.AddUserRole(role); 
           Assert.AreEqual(1, vm.User.UserRoles.Count); // OK
           var  lastAdded = vm.User.UserRoles.First(); 
           vm.RemoveUserRole(lastAdded);
           Assert.AreEqual(0, vm.User.UserRoles.Count); // OK            

           // Try to add the role again
           vm.AddUserRole(role);// Failed with System.InvalidOperationException: Can not insert entity with the same key if key is not database generated.
     }
}

When i attampted to run this code on SQL Server + MS LINQ to SQL i got an exception only on SumbitChanges(). I've resolved the issue, added db.InsertOnSubmit(newItem) and db.DeleteOnSubmitChanges(userRole) in the methods:

Code: Select all

public AddUserRole(Role role)
    {
          if (user.UserRoles.Any(x=>x.Role == role)) return;  
          var newItem = new  UserRole(){Role = role};
          UserRoles.Add(newItem);
          db.InsertOnSubmit(newItem);  // Assume db is accesible from ViewModel
    }
    public RemoveUserRole(UserRole userRole)
    {
          if (!UserRoles.Contains(userRole)) return;
          db.DeleteOnSubmit(userRole); // Assume db is accesible from ViewModel
          UserRoles.Remove(userRole);
    } 

In the LinqConnect this didn't help me. Help me, please!!!

[StanislavK]

Thank you for your report, we've reproduced the problem. We will investigate it and inform you about the results as soon as possible.

[StanislavK]

We have fixed the problem with attaching to DataContext entities with the keys used before. However, at the moment it is necessary to specify the key of this entity explicitly, like

Code: Select all

public static void AddUserRole(User user, Role role) {
  if (user.UserRoles.Any(x => x.Role == role))
    return;
  var newItem = new UserRole();

  newItem.RoleId = role.Id;
  newItem.UserId = user.Id;

  newItem.Role = role;
  user.UserRoles.Add(newItem);
  db.UserRoles.InsertOnSubmit(newItem);
}

We are analyzing possibility of changing this behaviour; we will post here when our investigation is completed.

The fix is available in the new 2.20.12 version of LinqConnect. The new build can be downloaded from
http://www.devart.com/linqconnect/download.html
(the trial and free versions) and from Registered Users' Area (for users with active subscriptions only):
http://secure.devart.com/

For the detailed information about the fixes and improvements available in LinqConnect 2.20.12, please refer to
http://www.devart.com/forums/viewtopic.php?t=20231

[StanislavK]

We have analyzed this behaviour, the 'link' entity should always be created with the explicitly set primary key. Otherwise, an 'interim' entity with only a part of the key (i.e., either user id or role id) may be created. When creating UserRole with the same primary key again, an attempt to create the identical 'interim' entity will be performed, thus generating an exception.