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

One way would be to have a stored proc that will delete your child records directly on the DB and include it in your EF model and just call it from your DataContext.

Marc

Answer 2

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 3

Here is my example ...where i klnow the foreign keys and i dont want to do a db roundtrip. I hope this helps someone...

code explanantion [client] --- many --to--many ---[Medication]

Client objClient = new Client() { pkClientID = pkClientID }; EntityKey entityKey = _commonContext.CreateEntityKey("Client", objClient); objClient.EntityKey = entityKey; _commonContext.Attach(objClient); //just load entity key ...no db round trip

         Medication objMed = new Medication() { pkMedicationID = pkMedicationID };
                EntityKey entityKeyMed = _commonContext.CreateEntityKey("Medication", objMed);
                objMed.EntityKey = entityKeyMed;
                _commonContext.Attach(objMed);


        objClient.Medication.Attach(objMed);
        objClient.Medication.Remove(objMed); //this deletes
        _commonContext.SaveChanges();