Custom RoleProvider: Can't insert record in UsersInRole Table

Question

Hi, I have implemented a LINQ to SQL based RoleProvider, when I assign role to a user following exception is thrown while AddUsersToRoles method is called. I have defined a composite primary key userid & roleId on this table, it still throwing this exception:

Can't perform Create, Update or Delete operations on 'Table(UsersInRole)' because it has no primary key.

My LinQ to SQL implementation of AddUsersToRoles method is as follows. It breaks at db.UsersInRoles.InsertOnSubmit(userInRole);

using (RussarmsDataContext db = new RussarmsDataContext())
{
  List<UsersInRole> usersInRole = new List<UsersInRole>();
  foreach (string username in usernames)
  {
    foreach (string rolename in rolenames)
    {
      UsersInRole userInRole = new UsersInRole();
      object userId = ProvidersUtility.GetUserIdByUserName(username,applicationName);
      object roleId = ProvidersUtility.GetRoleIdByRoleName(rolename,applicationName);
      if (userId != null && roleId != null)
      {
        userInRole.UserId = (Guid)userId;
        userInRole.RoleId = (Guid)roleId;
        db.UsersInRoles.InsertOnSubmit(userInRole);
      }
    }
  }
  try
  {
    //  db.UsersInRoles.InsertAllOnSubmit(usersInRole);
    db.SubmitChanges();
  }
  catch (ChangeConflictException)
  {
    db.ChangeConflicts.ResolveAll(RefreshMode.OverwriteCurrentValues);
    db.SubmitChanges();
  }
}

Any help will be appreciated. Thanks.

Answer 1

LINQ to SQL does not natively support many to many... the joining table has to have two foreign key columns PLUS 1 primary key column with the IDENTITY attribute.