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.

Community
  • 1
  • 1
Learner
  • 3,297
  • 4
  • 37
  • 62
  • 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. – Jon Bellamy Feb 20 '14 at 11:09
  • I should note to the above, it will affect your existing code. TO what extent, only you will know that. – Jon Bellamy Feb 20 '14 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. – Learner Feb 20 '14 at 11:12
  • 1
    In the question you have linked dannie.f's answer is a working solution: http://stackoverflow.com/a/15294368/270591 – Slauma Feb 20 '14 at 15:17
  • Exactly what I just tried now as well and worked like a charm... Thanks – Learner Feb 20 '14 at 15:19

3 Answers3

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();
Community
  • 1
  • 1
Learner
  • 3,297
  • 4
  • 37
  • 62
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);
}
Jon Bellamy
  • 3,333
  • 20
  • 23
  • 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. – Learner Feb 20 '14 at 10:42
  • @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. – Jon Bellamy Feb 20 '14 at 10:52
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.

Community
  • 1
  • 1
Heberda
  • 830
  • 7
  • 29