1

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.

Improve this question
5
  • if you don't want any of my solutions below then you need to add a Payload column to the table so that it gets mapped in EF and can be referenced as dbContext.TopicSubscriptions. However, that means you've unnecessary data on the table using unnecessary space. Other than that, I don't believe you have an alternative. Commented Feb 20, 2014 at 11:09
  • I should note to the above, it will affect your existing code. TO what extent, only you will know that. Commented Feb 20, 2014 at 11:11
  • maybe my question was not clear enough, I could load only one particular topic and only one particular subscription from db, although I guess these could be attached to the DbSet<> as well. Commented Feb 20, 2014 at 11:12
  • 1
    In the question you have linked dannie.f's answer is a working solution: stackoverflow.com/a/15294368/270591 Commented Feb 20, 2014 at 15:17
  • Exactly what I just tried now as well and worked like a charm... Thanks Commented Feb 20, 2014 at 15:19

3 Answers 3

Reset to default
2

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();
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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); }
Improve this answer

2 Comments

Well, your code to delete relationship will not work, since inner collections are not loaded (LazyLoading set to false). Stored procedure would work indeed, but there must be a way with EF as well.
@Cristi - OK. I missed that part (yes, despite it being highlighted)! Edited (in two mins time) to add a third option, but I still feel the Stored Procedure is a better way.
0

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.

Improve this answer

2 Comments

I don't need to delete the entities, I just need to remove the association between them
Well that wouldn't work then haha Surely you would end up with a lot of redundant data?!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.