Remove parent with children in single to large relationships

Question

I have a .NET4.0 application with Entity Framework 5.0 e Sql Server CE 4.0.

I have two objects with a one to one relationship (parent / child). I configured it to cascade when the parent was deleted, but for some reason it does not work.

Here is a simplified version of my entities:

public class Account { public int AccountKey { get; set; } public string Name { get; set; } public ICollection<User> Users { get; set; } } internal class AccountMap : EntityTypeConfiguration<Account> { public AccountMap() { this.HasKey(e => e.AccountKey); this.Property(e => e.AccountKey).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); this.Property(e => e.Name).IsRequired(); } } public class User { public int UserKey { get; set; } public string Name { get; set; } public Account Account { get; set; } public int AccountKey { get; set; } } internal class UserMap : EntityTypeConfiguration<User> { public UserMap() { this.HasKey(e => e.UserKey); this.Property(e => e.UserKey).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); this.Property(e => e.Name).IsRequired(); this.HasRequired(e => e.Account) .WithMany(e => e.Users) .HasForeignKey(e => e.AccountKey); } } public class TestContext : DbContext { public TestContext() { this.Configuration.LazyLoadingEnabled = false; } public DbSet<User> Users { get; set; } public DbSet<Account> Accounts { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); modelBuilder.Conventions.Remove<StoreGeneratedIdentityKeyConvention>(); modelBuilder.LoadConfigurations(); } } 

Connection string:

  <connectionStrings> <add name="TestContext" connectionString="Data Source=|DataDirectory|\TestDb.sdf;" providerName="System.Data.SqlServerCe.4.0" /> </connectionStrings> 

And a simplified version of the application workflow:

 static void Main(string[] args) { try { Database.SetInitializer(new DropCreateDatabaseAlways<TestContext>()); using (var context = new TestContext()) context.Database.Initialize(false); Account account = null; using (var context = new TestContext()) { var account1 = new Account() { Name = "Account1^" }; var user1 = new User() { Name = "User1", Account = account1 }; context.Accounts.Add(account1); context.Users.Add(user1); context.SaveChanges(); account = account1; } using (var context = new TestContext()) { context.Entry(account).State = EntityState.Deleted; context.SaveChanges(); } } catch (Exception e) { Console.WriteLine(e.ToString()); } Console.WriteLine("\nPress any key to exit..."); Console.ReadLine(); } 

When I try to delete the parent, it throws:

The relationship cannot be changed because one or more foreign key properties are not NULL. When a relationship change occurs, the associated foreign key property is set to zero. If the foreign key does not support null values, a new relationship must be defined, another non-zero value or an object not associated with it must be assigned to the foreign key property.

I believe my relationship configuration is fine ( followed the documentation ). I also looked for recommendations for deleting individual objects .

I really cannot understand why this removal will not work. I want not to load all the children by deleting them one by one, and they delete the parent, because there must be a better solution than this.

Answer 1

Setting the state of the Deleted object and calling DbSet<T>.Remove for this object do not match.

The difference is that setting the state changes the state of the root object (the one you pass to context.Entry ) to Deleted , but not the state of the related objects, but Remove does this if the connection is configured with cascading deletion.

If you get an exception, it actually depends on which children (all or only part) are context-bound or not. This leads to the following behavior:

• If you call Remove , you will not get an exception, whether the children are loaded or not. There is still a difference:
  • If the children are context bound, EF will generate a DELETE for each attached child and then for the parent (because Remove meant everything as Deleted )
  • If the children are not context bound, EF sends only the DELETE for the parent to the database and, because cascading deletion is enabled, the database will also delete the children.
• If you set the state of the root Deleted object, you may get an exception:
  • If children are context-sensitive, their state will not be set to Deleted , and EF will complain that you are trying to remove the principal (root object) in the required relationship without deleting dependents (children) or at least not set foreign keys in another root object that is not in the Deleted state. This is the exception you had: account is the root, and user1 dependent on account , and the call is context.Entry(account).State = EntityState.Deleted; also attaches user1 in the Unchanged state to the context (or detects a change in SaveChanges I will do it, I'm not sure what it is). user1 is part of the account.Users collection because a relationship fix added it to the collection in your first context, although you did not explicitly add it to your code.
  • If no children are context bound, then the root state in Deleted will send the DELETE to the database, and again cascading in the database will also delete the children. This works without exception. Then your code will work, for example, if you set account.Users = null before setting the state to Deleted in the second context or before entering the second context.

In my opinion, using Remove ...

 using (var context = new TestContext()) { context.Accounts.Attach(account); context.Accounts.Remove(account); context.SaveChanges(); } 

... clearly the preferred method, because the behavior of Remove much more like you would expect the necessary connection to cascade deletion (which is the case in your model). The dependence of the behavior of a manual state change on the states of other objects complicates the use. I would consider it for extended use only for special occasions.

The difference is not widely known or documented. I have seen very few posts about this. The only thing I could find now is this author Zeeshan Hirani .

Answer 2

I tried a slightly different approach, and, oddly enough, it worked. If I replace this code:

 using (var context = new TestContext()) { context.Entry(account).State = EntityState.Deleted; context.SaveChanges(); } 

Under this:

 using (var context = new TestContext()) { context.Entry(account).State = EntityState.Unchanged; context.Accounts.Remove(account); context.SaveChanges(); } 

It works without any additional problems. Not sure if this is a mistake or I missed something. I would really appreciate some light on this because I was sure that the first way (EntityState.Deleted) was the recommended one.