CODEWITHSUNDEEP
entity-frameworkado.netmany-to-many
Emil
Emil

Reputation: 492

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

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.

Upvotes: 19

Views: 20471

Answers (4)

dannie.f
dannie.f

Reputation: 2425

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();

Upvotes: 4

Jlo
Jlo

Reputation:

Here is my example ...where i know the foreign keys and i don't want to do a db round trip.
I hope this helps someone...

Given:
[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();

Upvotes: 1

marc_s
marc_s

Reputation: 754518

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; then just call it from your DataContext.

Upvotes: 2

Steve Willcock
Steve Willcock

Reputation: 26849

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.

Upvotes: 27

Related Questions