如何在不加载所有数据的情况下删除Entity Framework中的多对多关系

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.

有没有人知道如何删除ADO.NET实体框架中的多对多关系,而无需加载所有数据?在我的情况下,我有一个实体主题有一个属性订阅,我需要删除一个订阅。代码myTopic.Subscriptions.Remove(...)有效,但我需要先加载所有订阅(例如myTopic.Subscriptions.Load()),我不想这样做,因为有很多(我的意思是很多)订阅

5 个解决方案

#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.

您可以Attach()订阅然后删除()它 - 注意,我们这里没有使用Add(),只是Attach,所以我们告诉EF我们知道对象已经附加在商店中,并要求它表现得好像是真的。

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:

整个交换,包括从数据库获取原始主题,将这3个查询发送到数据库:

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.

因此,它不会在任何时候提取所有订阅。

#2

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

这是在不先加载任何数据的情况下删除的方法。这适用于EF5。对早期版本不确定。

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

#3

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.

一种方法是使用存储过程直接删除数据库中的子记录并将其包含在EF模型中;然后从DataContext中调用它。

#4

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...

这是我的例子......我知道外键,我不想做db往返。我希望这可以帮助别人...

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

#5

If the foreign keys are set, referential integrity should do automatically via the DBMS itself when deleting the parent entities.

如果设置了外键,则在删除父实体时,应通过DBMS自动执行引用完整性。

If you use code first, as far as I learned in a MVA tutorial, ON DELETE CASCADE is the default behavior set by EF6. If running DB first, you should alter your childtable(s)...

如果您首先使用代码,据我在MVA教程中学到的,ON DELETE CASCADE是EF6设置的默认行为。如果首先运行DB,您应该更改您的子表...

Here is the link: https://mva.microsoft.com/en-US/training-courses/implementing-entity-framework-with-mvc-8931?l=pjxcgEC3_7104984382 In the Video it's mentioned at 20:00 upwards and in the slide presentation it is said on page 14.

这是链接:https://mva.microsoft.com/en-US/training-courses/implementing-entity-framework-with-mvc-8931?l=pjxcgEC3_7104984382在视频中它提到20:00以上和幻灯片演示文稿在第14页说。

Cheers

#1