Is it possible to delete one child without loading the entire collection?

Question

I have 2 classes as shown below.

They can have very large collections - there can be another 2000 + WebsitePages on the website and vice versa.

class WebsitePage { public int ID {get;set;} public string Title {get;set;} public List<Website> Websites {get;set;} } class Website { public int ID {get;set;} public string Title {get;set;} public List<WebsitePage> WebsitePages {get;set;} } 

I am having trouble removing WebsitePage from a website. In particular, when deleting a website from multiple websites.

For example, I might have code like this:

 var pageToRemove = db.WebsitePages.FirstOrDefault(); var websites = db.Websites.Include(i => i.WebsitePages).ToList(); foreach(var website in websites) { website.WebsitePages.Remove(pageToRemove) } 

If every website Include() 2k pages, you can imagine that it takes age to load the second line.

But if I don't Include() WebsitePages when fetching websites, for me there is no child collection that I have to delete.

I tried just Include() pages that I need to delete, but of course, when saving this gives me an empty collection.

Is there a recommended or best way to approach this?

I am working with an existing MVC site and do not want to create an entity class for the connection table , if absolutely necessary.

Answer 1

No, you cannot ... okay.

Many-to-many relationships (with a hidden conversion table) can only be affected by adding / removing items in nested collections. And for this collection must be uploaded.

But there are some options.

Option 1.

Delete data from the connection table using raw SQL. It basically looks like

 context.Database.ExecuteSqlCommand( "DELETE FROM WebsiteWebsitePage WHERE WebsiteID = x AND WebsitePageID = y")); 

(not using parameters).

Option 2

Include the connection in the class model, i.e. map the connection table to the WebsiteWebsitePage class. Both Website and WebsitePage will now have

 public ICollection<WebsiteWebsitePage> WebsiteWebsitePages { get; set; } 

and WebsiteWebsitePage will have WebsiteWebsitePage properties for both Website and WebsitePage . Now you can manipulate the connections directly through the class model.

I think this is the best option, because everything happens in the standard way of working with objects with checks and tracking and all. In addition, it is likely that sooner or later you will need an explicit connection class, because you want to add additional data to it.

Option 3

Box of tricks.

I tried to do this by removing the stub from the collection. In your case: create a WebsitePage object with a valid primary key value and delete it from Website.WebsitePages without loading the collection. But EF does not notice the change because it does not track Website.WebsitePages , and the item is not in the collection to begin with.

But it made me realize that I had to make an EF track in the Website.WebsitePages collection with 1 element in it, and then delete that element. I got this working by first creating a Website element and then linking it to a new context. I will show the code I used (standard Product model - Category ) to prevent typos.

 Product prd; // Step 1: build an object with 1 item in its collection Category cat = new Category { Id = 3 }; // Stub entity using(var db = new ProdCatContext()) { db.Configuration.LazyLoadingEnabled = false; prd = db.Products.First(); prd.Categories.Add(cat); } // Step 2: attach to a new context and remove the category. using(var db = new ProdCatContext()) { db.Configuration.LazyLoadingEnabled = false; db.Products.Attach(prd); prd.Categories.Remove(cat); db.SaveChanges(); // Deletes the junction record. } 

False loading is disabled, otherwise Categories will be loaded when prd.Categories .

My interpretation of what is happening here: In the second stage, EF not only starts tracking the product when you attach it, but also its associations, because it “knows”, you cannot download these associations yourself in many many ways. However, this does not when you add a category in the first step.