CODEWITHSUNDEEP
c#.netentity-frameworkado.netmany-to-many
Ricky Gummadi
Ricky Gummadi

Reputation: 5222

Deleting many-to-many relationship in Entity Framework throws error

I've got the below model.

enter image description here

I am trying to delete a given productType associated with an ActivityType; But when I try to instigate a delete via my generic methods, its not only deleting the relationship but its trying to delete the ProductType as well!! with a primary key of Zero in the where clause causing the erorr

"Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries."

code to delete below;

public void DeleteEntityAndSaveChanges<T, T1>(T entity, List<T1> subEntities)
            where T : class
            where T1 : class
        {
            Set<T>().Attach(entity);
            DeleteEntitiesAndSaveChanges(subEntities);
        }

public void DeleteEntitiesAndSaveChanges<T>(List<T> entities) where T : class
        {
            foreach (var entity in entities)
            {
                Set<T>().Attach(entity);
                Set<T>().Remove(entity);
            }

            SaveChanges();
        }

Usage:

DbContext.DeleteEntityAndSaveChanges(request.ActivityType, request.ActivityType.ProductTypes);

This is the sql thats being generated:

exec sp_executesql N'delete [dbo].[ProductTypeActivityTypes]
where (([ProductType_ProductTypeId] = @0) and ([ActivityType_EntityObjectId] = @1))',N'@0 bigint,@1 bigint',@0=1,@1=20

This is the offending SQL that dont want sent out, but is being generated by EF:

exec sp_executesql N'delete [dbo].[ProductTypes] where (([ProductTypeId] = @0) and [TimeStamp] is null)',N'@0 bigint',@0=1

any ideas as to how I can get it to only delete the relationship?

Cheers.

Upvotes: 0

Views: 3344

Answers (1)

Arthur Vickers
Arthur Vickers

Reputation: 7523

When you call Remove on the DbSet, this means delete that entity from the database when SaveChanges is called. As I understand it, what you really want to do is remove the association between the two entities. In general you do this by removing one entity from the collection navigation property of the other entity. For example:

product.Activities.Remove(activity);

But there is a wrinkle here. In your sample code you are calling Attach to attach both types of entities to the context. Calling Attach will not setup or restore any relationships between the entities. This is because, for many-to-many relationships, there is no FK in the entity to provide information about how it is related to other entities. The FKs are handled by the join table, which is not exposed in an EF many-to-many mapping.

There are a couple of approaches to dealing with this. First, if possible and sensible for your architecture, you can let the EF context track the entities from the time they are queried until the time you call SaveChanges. This way EF will keep track of the relationships for you, including keeping track of deletes.

Second, if the entities need to be attached to a new context, then you will need to keep track of the relationships such that those can be restored as well. There are a few ways to restore the relationships. One way is to build the graph of related entities before calling Attach. EF will then traverse and attach the entire graph, including relationships, when Attach is called. For example:

// Restore the graph
product.Activities.Add(activity1);
product.Activities.Add(activity2);
context.Products.Attach(product);

// Delete the relationship
product.Activities.Remove(activity1);
context.SaveChanges();

(I'm not using the generic methods you have just to make the code a bit clearer. It should work the same way with generics.)

Upvotes: 3

Related Questions