Delete parent with children in one to many relationship

Question

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

I have two entities with a one to many (parent/child) relationship. I've configured it to cascade delete on parent removal, but for some reason it doesn't seem to 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();
        }

    }

The connection string:

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

And a simplified version of my app's 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 entity, it throws:

The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

I believe my relationship configuration is ok (followed the documentation). I also searched for guidelines on deleting detached entities.

I really cannot understand why that delete won't work. I want to avoid loading all the children, deleting them one by one and them deleting the parent, because there must be a better solution than that.

Answer 1

Setting the state of an entity to Deleted and calling DbSet<T>.Remove for this entity are not the same.

The difference is that setting the state only changes the state of the root entity (the one you pass into context.Entry) to Deleted but not the state of related entities while Remove does this if the relationship is configured with cascading delete.

If you get an exception actually depends on the children (all or only a part) being attached to the context or not. This leads to a behaviour which is somewhat difficult to follow:

• If you call Remove you don't get an exception, no matter if children are loaded or not. There is still a difference:
  • If the children are attached to the context, EF will generate a DELETE statement for every attached child, then for the parent (because Remove did mark them all as Deleted)
  • If the children are not attached to the context EF will only send a DELETE statement for the parent to the database and because cascading delete is enabled the database will delete the children as well.
• If you set the state of the root entity to Deleted you can possibly get an exception:
  • If children are attached to the context their state won't be set to Deleted and EF will complain that you are trying to delete a principal (the root entity) in a required relationship without deleting the dependents (the children) or at least without setting their foreign keys to another root entity that is not in Deleted state. That's the exception you had: account is the root and user1 is a dependent of account and calling context.Entry(account).State = EntityState.Deleted; will also attach user1 in state Unchanged to the context (or change detection in SaveChanges will do it, I'm not sure abut that). user1 is part of the account.Users collection because relationship fixup added it to the collection in your first context although you didn't add it explicitly in your code.
  • If no children are attached to the context setting the state of the root to Deleted will send a DELETE statement to the database and again cascading delete in the database will delete the children as well. This works without exception. Your code would then 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();
}

...is clearly the prefered way because the behaviour of Remove is much more like you would expect for a required relationship with cascading delete (which is the case in your model). The dependency of the behaviour of a manual state change on states of other entities makes it more difficult to use. I would consider it as advanced usage only for special cases.

The difference is not widely known or documented. I've seen very few posts about it. The only one that I could find right now again, is this one by Zeeshan Hirani.