EF: how to delete many-to-many relationship without loading more than is needed

Question

Given the entities: Topics and Subscriptions with many-to-many relationship and the corresponding tables: Topics, Subscriptions, TopicsSubscriptions, I want to delete only one row from TopicsSubscriptions using EF, but without loading any extra data (except of maybe only 1 topic and 1 subscription).

In the end I want EF to generate similar SQL to this:

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

I have LazyLoading set to false.

I thought I could take the answer from here: How to delete many-to-many relationship in Entity Framework without loading all of the data.

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

// !!! This line does not work for me (Attach to ICollection<> ?!?) !!!
topic.Subscriptions.Attach(subscription); // Attach it (theObjectContext now 'thinks' it belongs to the topic)

topic.Subscriptions.Remove(subscription); // Remove it
db.SaveChanges(); // Flush changes

But then I realized there is no Attach method belonging to ICollection<>... unless I am missing something.

Idea of attaching only one subscription to a topic sounds good, but I cannot understand how to achieve it.

I am using DbContext instead of ObjectContext and Entity Framework 6 but I guess it should not matter.

EDIT: Would be good to find a solution without stored procedures, or direct sql, if possible, since I have to support many database back-ends in my app.

In case I was not clear enough, I don't need to delete the entities, I just need to remove the association between them.

Answer 1

As Slauma was also saying in his comments, the solution was to attach entities as "dannie.f" did in his answer :

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

Answer 2

I would suggest letting entity framework handle the deletion by declaring cascade on delete in the fluent api like this;

modelBuilder.Entity<Product>()
            .HasOptional(p => p.Category)
            .WithMany()
            .WillCascadeOnDelete(true);

the full solution is outlined here.

Answer 3

Assuming you've mapped it so that you can't directly reference TopicSubscriptions, and only Topics and Subscriptions, the following applies.

// Add a subscription to a topic
var subscription = dbContect.Subscriptions.Find(2);
var topic = dbContext.Topics.Find(1);

// Recommend checking here, but omitted for example

// Make the association
topic.Subscriptions.Add(subscription);

// Update
dbContext.SaveChanges();

To remove a subscription from a Topic

// Remove
topic.Subscriptions.Remove(subscription);

// Update
dbContext.SaveChanges();

If you know the Ids however and want to remove it directly, I recommend a simple StoredProcedure on the database accepting @topicId and @subscriptionId and removing the entry in TSQL.

@topicId INT,
@subscriptionId INT

DELETE FROM [dbo].[TopicSubscriptions] WHERE TopicId = @topicId AND SubscriptionId = @subscriptionId;

You can then map the StoredProcedure to EF6 and call it from your context.

using (DbContext dbContext = new DbContext())
{
dbContext.DeleteTopicSubscriptionStoredProcedure(topicId, subscriptionId);
}

EDIT Given only the SP option works (apologies for missing Lazy-Loading false), you can alternatively execute SQL directly from dbContext.

using (var dbContext = new DbContext())
{
string sql = @"DELETE FROM [dbo].[TopicSubscriptions] WHERE TopicId = @p0 AND SubscriptionId = @p1";
context.Database.ExecuteSqlCommand(sql, topicId, subscriptionId);
}