none
Automatic deletion of associated entities when removed from association collection RRS feed

  • Question

  • Hi!

    Say I have a one-to-many relationship with required main entity. Like this:

    modelBuilder.Entity<Main>().HasMany(m => m.Related).WithRequired();
    
    


    What do I have to do to make the entities in Related be deleted when I remove them from the collection? I have not exposed the DbSet for the related entities, and would like not to. :)

    Like this:

    Main item = context.MainSet.First();
    item.Related.RemoveAt(0);
    context.SaveChanges();
    // related should now be deleted, instead of giving me System.Data.UpdateException

    Lars-Erik


    Lars-Erik MCPD ASP.NET 2.0
    Monday, November 28, 2011 3:52 PM

Answers

  • According to

    http://blogs.msdn.com/b/dsimmons/archive/2010/01/31/deleting-foreign-key-relationships-in-ef4.aspx

    EF4 requires the PK of the related entity to include the FK to the main entity for it to automatically delete it when it's removed from the main entity.

    I actually didn't tell EF that my Id prop was PK at all on the related entity (which didn't help much eighter), but when I added modelBuilder.Entity<Related>.HasKey(r => new{r.Id, r.MainId}).HasForeignKey(r => r.MainId) it worked as a dream.

    No biggie then, although it's not clear as day. :P

    I guess the team thought this good enough since you'll probably always use the foreign key to find the related entities anyway. In that case, it'd be nice to be able to make the generated table cluster by FK instead of the id. :)

     


    Lars-Erik MCPD ASP.NET 2.0
    Monday, November 28, 2011 6:47 PM

All replies

  • Hi again :)

    Sorry, misread your post... The code you are presenting should indeed delete the Related entity at position 0 when you call SaveChanges. The problem must be something else.

    Can you post the InnerException of the UpdateException (if any) or the actual message of the UpdateException you get? Is there any other foreign keys to Related that may cause problems for you?


    --Rune

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful" if the post helped you to a solution of your problem.
    Monday, November 28, 2011 5:30 PM
  • I thought so too.

    The exception indicates that I need to expose foreign keys, so I tried exposing the main entity as a navigation property on the related, but still no success.

    The detailed exceptions:

    System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details. ---> System.Data.UpdateException: A relationship from the 'Main_Related' AssociationSet is in the 'Deleted' state. Given multiplicity constraints, a corresponding 'Main_Related_Target' must also in the 'Deleted' state.

    I think it means that the related entity itself has not been marked as deleted.
    I also tried adding a DbSet for Related on my context, but it didn't help any.

    The related entity does not have any other foreign keys than to the main entity, and the FK is non-nullable.
    If I have WithOptional (nullable), it just sets the FK on related to null, which will generate a lot of orphans.

    I forgot to add the .Map() config I do on the relationship in OP, but I just also tried adding a public FK property on the related entity and used .HasForeignKey(). That gives the following exception indicating that this scenario actually isn't possible at all. (Which I doubt)

    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.

    (if any reader is about to suggest i do context.Related.Delete(related), don't - that's what I try to achieve not to have to)


    Lars-Erik MCPD ASP.NET 2.0
    Monday, November 28, 2011 6:24 PM
  • According to

    http://blogs.msdn.com/b/dsimmons/archive/2010/01/31/deleting-foreign-key-relationships-in-ef4.aspx

    EF4 requires the PK of the related entity to include the FK to the main entity for it to automatically delete it when it's removed from the main entity.

    I actually didn't tell EF that my Id prop was PK at all on the related entity (which didn't help much eighter), but when I added modelBuilder.Entity<Related>.HasKey(r => new{r.Id, r.MainId}).HasForeignKey(r => r.MainId) it worked as a dream.

    No biggie then, although it's not clear as day. :P

    I guess the team thought this good enough since you'll probably always use the foreign key to find the related entities anyway. In that case, it'd be nice to be able to make the generated table cluster by FK instead of the id. :)

     


    Lars-Erik MCPD ASP.NET 2.0
    Monday, November 28, 2011 6:47 PM
  • Good it work, however the HasKey statement you have put creates a PK based on both Id an MainId, are you sure you want that?

    If that is undesired and you still want some help, please post your class definition of the Related and Main class in addition to the OnModelCreating definition of these two classes.


    --Rune

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful" if the post helped you to a solution of your problem.
    Monday, November 28, 2011 7:48 PM
  • Yes, the dual PK is actually what is required to make the related entity be deleted when the association is deleted. It's by design to make other scenarios work where other behaviors are expected. (For instance, move a related entity to another "main" entity)

    I actually remembered when doing it that we used to do that with EF 3.5 too, but it had slipped my mind, and I was sure it was changed in 4.1.

    Thanks for your help though. Read through the post I mentioned, it's not too long and very illuminating. :)

    (I still believe the ado.net team could've solved this in some easier way, but I'll live with it)

     


    Lars-Erik MCPD ASP.NET 2.0
    Monday, November 28, 2011 8:14 PM
  • If you're still interested, here's a quick test showing the different approaches.

    Swap lines 62 with 63, 66 with 67 and uncomment 85, then it works.

    using System;
    using System.Data.Entity;
    using System.Collections.Generic;
    using System.Linq;
    using Microsoft.VisualStudio.TestTools.UnitTesting;
    
    namespace EFDeleteRelatedFromCollection
    {
    	[TestClass]
    	public class TestDeleteRelatedThroughCollection
    	{
    		[TestInitialize]
    		public void TestInitialize()
    		{
    			Database.SetInitializer(new DropCreateDatabaseAlways<Context>());		
    		}
    
    		[TestMethod]
    		public void CanDeleteRelated()
    		{
    			using (var context = new Context())
    			{
    				var entity = new Main { Id = Guid.NewGuid() };
    				context.Mains.Add(entity);
    				entity.Related.Add(new Related { Id = Guid.NewGuid(), Name = "A" });
    				entity.Related.Add(new Related { Id = Guid.NewGuid(), Name = "B" });
    				context.SaveChanges();
    			}
    
    			using (var context = new Context())
    			{
    				var entity = context.Mains.Include("Related").First();
    				Assert.AreEqual(2, entity.Related.Count);
    				entity.Related.RemoveAt(0);
    				context.SaveChanges();
    			}
    
    			using (var context = new Context())
    			{
    				var entity = context.Mains.Include("Related").First();
    				Assert.AreEqual(1, entity.Related.Count);
    			}
    		}
    	}
    
    	public class Context : DbContext
    	{
    		public DbSet<Main> Mains { get; set; }
    
    		public Context()
    			: base("Data Source=.;Initial Catalog=TestDeleteRelatedThroughCollection;Integrated Security=SSPI")
    		{
    		}
    
    		protected override void OnModelCreating(DbModelBuilder modelBuilder)
    		{
    			base.OnModelCreating(modelBuilder);
    
    			modelBuilder.Entity<Main>().HasKey(m => m.Id);
    			modelBuilder.Entity<Main>().HasMany(m => m.Related)
    				.WithRequired()
    				//.HasForeignKey(r => r.MainId)
    				.Map(c => c.MapKey("MainId"))
    				;
    
    			modelBuilder.Entity<Related>().HasKey(r => r.Id);
    			//modelBuilder.Entity<Related>().HasKey(r => new{r.Id, r.MainId});
    		}
    	}
    
    	public class Main
    	{
    		public Guid Id { get; set; }
    		public List<Related> Related { get; set; }
    
    		public Main()
    		{
    			Related = new List<Related>();
    		}
    	}
    
    	public class Related
    	{
    		public Guid Id { get; set; }
    		//public Guid MainId { get; set; }
    		public string Name { get; set; }
    	}
    }
    
    


    Lars-Erik MCPD ASP.NET 2.0
    Tuesday, November 29, 2011 9:29 AM