How to delete many-to-many relationship in Entity Framework without loading all of the data

Question

Does anyone know how to delete many-to-many relationship in ADO.NET Entity Framework without having to load all of the data? In my case I have an entity Topic that has a property Subscriptions and I need to remove a single subscription. The code myTopic.Subscriptions.Remove(...) works but I need to load all subscriptions first (e.g. myTopic.Subscriptions.Load()) and I don't want to do that because there are lots (and I mean lots) of subscriptions.

Answer 1

You can Attach() a subscription then Remove() it - note, we're not using Add() here, just Attach, so effectively we're telling EF that we know the object is attached in the store, and asking it to behave as if that were true.

var db = new TopicDBEntities();
var topic = db.Topics.FirstOrDefault(x => x.TopicId == 1);

// Get the subscription you want to delete
var subscription = db.Subscriptions.FirstOrDefault(x => x.SubscriptionId == 2);
topic.Subscriptions.Attach(subscription); // Attach it (the ObjectContext now 'thinks' it belongs to the topic)
topic.Subscriptions.Remove(subscription); // Remove it
db.SaveChanges(); // Flush changes

This whole exchange, including getting the original topic from the database sends these 3 queries to the database:

SELECT TOP (1) 
[Extent1].[TopicId] AS [TopicId], 
[Extent1].[Description] AS [Description]
FROM [dbo].[Topic] AS [Extent1]
WHERE 1 = [Extent1].[TopicId]


SELECT TOP (1) 
[Extent1].[SubscriptionId] AS [SubscriptionId], 
[Extent1].[Description] AS [Description]
FROM [dbo].[Subscription] AS [Extent1]
WHERE 2 = [Extent1].[SubscriptionId]


exec sp_executesql N'delete [dbo].[TopicSubscriptions]
where (([TopicId] = @0) and ([SubscriptionId] = @1))',N'@0 int,@1 int',@0=1,@1=2

so it's not pulling all the subscriptions at any point.

Answer 2

This is how to delete without first loading any data. This works in EF5. Not sure about earlier versions.

var db = new TopicDBEntities();

var topic = new Topic { TopicId = 1 };
var subscription = new Subscription { SubscriptionId = 2};
topic.Subscriptions.Add(subscription);

// Attach the topic and subscription as unchanged 
// so that they will not be added to the db   
// but start tracking changes to the entities
db.Topics.Attach(topic);

// Remove the subscription
// EF will know that the subscription should be removed from the topic
topic.subscriptions.Remove(subscription);

// commit the changes
db.SaveChanges();