InvalidOperationException with add\remove op. in EntitySet

InvalidOperationException with add\remove op. in EntitySet

Postby fmarakasov » Fri 21 Jan 2011 17:22

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!!!
fmarakasov
 
Posts: 5
Joined: Fri 21 Jan 2011 16:12
Location: RU

Postby StanislavK » Tue 25 Jan 2011 17:06

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
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby StanislavK » Thu 10 Feb 2011 17:42

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
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby StanislavK » Fri 18 Feb 2011 14:43

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.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to LinqConnect (LINQ to SQL support)