I'm trying to remove an item from the UsersVaults collection of an object of type User, but I get Exception (error text and code below). Tell me where I turned the wrong way?

Object modification method:

[HttpPost] public ActionResult Edit(int id, FormCollection form) { try { using (var db = new AccessControlSystemDatabaseModel()) { var user = db.Users.Find(id); var tempData = form.AllKeys.ToDictionary<string, string, object>(key => key, key => form[key]); user.RoleID = Convert.ToInt32(tempData["RoleID"]); user.Username = tempData["Username"].ToString(); user.Password = tempData["Password"].ToString(); user.Email = tempData["Email"].ToString(); if (!tempData["GrantAccess"].ToString().Equals("false") && user.UsersVaults.Where( d => d.VaultID == AuthenticationController.CurrentUser.UsersVaults.ToList()[0].VaultID) .ToList() .Count == 0) { user.UsersVaults.Add(new UsersVault { UserID = id, VaultID = AuthenticationController.CurrentUser.UsersVaults.ToList()[0].VaultID, }); } else { var tt = user.UsersVaults.Where( d => d.VaultID == AuthenticationController.CurrentUser.UsersVaults.ToList()[0].VaultID).ToList()[0]; user.UsersVaults.Remove(tt); } db.Entry(user).State = EntityState.Modified; db.SaveChanges(); return RedirectToAction("Index"); } } catch(Exception e) { return RedirectToAction("Index"); } }

Model:

 public partial class AccessControlSystemDatabaseModel : DbContext { public AccessControlSystemDatabaseModel() : base("name=AccessControlSystemDatabaseModel") { } public virtual DbSet<Role> Roles { get; set; } public virtual DbSet<User> Users { get; set; } public virtual DbSet<UsersVault> UsersVaults { get; set; } public virtual DbSet<Vault> Vaults { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<User>() .HasMany(e => e.UsersVaults) .WithRequired(e => e.User) .WillCascadeOnDelete(true); modelBuilder.Entity<Vault>() .HasMany(e => e.UsersVaults) .WithRequired(e => e.Vault) .WillCascadeOnDelete(true); } } public partial class Role { [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] public Role() { Users = new HashSet<User>(); } public int ID { get; set; } [Required] [StringLength(255)] public string RoleName { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<User> Users { get; set; } } public partial class User { [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] public User() { UsersVaults = new HashSet<UsersVault>(); } public int ID { get; set; } [Required] [StringLength(255)] public string Username { get; set; } [Required] [StringLength(255)] public string Password { get; set; } public int? RoleID { get; set; } [StringLength(255)] public string Email { get; set; } public virtual Role Role { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<UsersVault> UsersVaults { get; set; } } public partial class UsersVault { public int ID { get; set; } public int UserID { get; set; } public int VaultID { get; set; } public virtual User User { get; set; } public virtual Vault Vault { get; set; } } public partial class Vault { [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] public Vault() { UsersVaults = new HashSet<UsersVault>(); } public int ID { get; set; } [Required] [StringLength(255)] public string VaultName { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<UsersVault> UsersVaults { get; set; } }

DB schema

Exception text:

"The operation ended with an error. The connection could not be changed because one or more foreign key properties do not allow NULL values. When the connection changes, the corresponding foreign key property is set to NULL. If the foreign key does not support NULL values, a new link must be defined the key must be assigned a value other than NULL, or the unbound object must be deleted. "

  • You have the exception text lost - kmv
  • I apologize, corrected - Sano Litch

1 answer 1

In EF, unfortunately, Add and Remove operations for navigation collections are not always symmetrical - the first operation establishes a connection between entities and adds them to the base if necessary, while the second only breaks the connection. But it is impossible to break the link between UsersVault and User , hence the error.

Therefore, instead of removing an object from the collection, you must explicitly remove it from the database:

 var tt = user.UsersVaults.Single(...); db.Entry(tt).State = EntityState.Deleted;

or

 db.UsersVaults.Remove(tt);

To preserve the symmetry of operations, you can immediately add it to the database when you add UsersVault :

 db.UsersVaults.Add(new UsersVault { UserID = id, VaultID = ..., })

By the way, there is duplication in your current code - UserID not necessary to assign a UserID if you add an object through the navigation collection - EF will put it in by itself.

As an alternative solution, I can suggest removing the UsersVault entity UsersVault - it is not needed! EF is able to independently display many-to-many links on intermediate tables, simply create an mtm link between Users and Vaults .