How to delete all many-to-many relations without loading related entities in Entity Framework?

Question

I have db scheme where Product table have many to many relation to Color table. I'm using EF and create POCO objects:

public class Product
{ 
    public Guid Id {get;set;}
    public ICollection<Color> Colors {get;set;}
}

public class Color
{
    public Guid Id {get;set;}
    public ICollection<Product> Products {get;set;}
}

In many situations it is necessary to delete all colors related to product and set new colors. So i want to delete all many to many relations whitout exactly knowing id of related colors. Is it possible to delete them without additional queries to db? I know i can just write stored procedure which will delete all relation with colors for specified product, but it will be better to find general approach through entity framework.

just follow this link : [http://stackoverflow.com/questions/937140/delete-an-object-and-all-of-its-related-entities-in-entity-framework][1] cheers! — Behnam Esmaili, Jun 15 '12 at 07:13
I don't want to delete product or colors itself, i just want to delete all relations between specific product and all the colors. So i can't use cascade delete because there is no delete of related entities, only delete of relation. — HoberMellow, Jun 15 '12 at 07:18
you can store your entity(product or color) in a temporary variable and after deleting , insert it with different id.i think you cant find a way more efficient than this. — Behnam Esmaili, Jun 15 '12 at 07:21
In my question there is only simple example. In real model i have many relations on products and on colors too. So if i delete color or product i delete all relations. So i must store all product relations, then delete product, then insert product with all relations without color relation. It is very inefficient and in this case i must load all other product relations. — HoberMellow, Jun 15 '12 at 07:27

score 2 · Accepted Answer · answered Jun 15 '12 at 07:34

If you don't know keys of colors you cannot delete them without loading them first - EF deletes records one by one so it needs to know which record to delete.

The straight forward option is executing SQL DELETE directly:

dbContext.Database
         .ExecuteSqlCommand("DELETE FROM dbo.ProductColors WHERE ProductId = @Id", product.Id);

How to delete all many-to-many relations without loading related entities in Entity Framework?

1 Answers1