CODEWITHSUNDEEP
asp.net-mvccollectionslinq-to-entities
Carmax
Carmax

Reputation: 2927

Can I delete a single child entity without loading the entire collection?

I have 2 classes, like the below.

They can have very large collections - a Website may have 2,000+ WebsitePages 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 a WebsitePage from a Website. Particularly when removing a WebsitePage from mutliple 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 each website Include() 2k pages, you can imagine it takes ages to load that second line.

But if I don't Include() the WebsitePages when fetching the Websites, there is no child collection loaded for me to delete from.

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

Is there a recommended or better way to approach this?

I am working with an existing MVC site and I would rather not have to create an entity class for the join table unless absolutely necessary.

Upvotes: 4

Views: 698

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109252

No, you can't... normally.

A many-to-many relationship (with a hidden junction table) can only be affected by adding/removing items in the nested collections. And for this the collections must be loaded.

But there are some options.

Option 1.

Delete data from the junction table by raw SQL. Basically this looks like

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

(not using parameters).

Option 2.

Include the junction into the class model, i.e. map the junction table to a class WebsiteWebsitePage. Both Website and WebsitePage will now have

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

and WebsiteWebsitePage will have reference properties to both Website and WebsitePage. Now you can manipulate the junctions directly through the class model.

I consider this the best option, because everything happens the standard way of working with entities with validations and tracking and all. Also, chances are that sooner or later you will need an explicit junction class because you're going to want to add more data to it.

Option 3.

The box of tricks.

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

But this made me realize I had to make EF track a Website.WebsitePages collection with 1 item in it and then remove that item. I got this working by first building the Website item and then attaching it to a new context. I'll show the code I used (a standard Product - Category model) 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.
}

Lazy loading is disabled, otherwise the Categories would still be loaded when prd.Categories is addressed.

My interpretation of what happens here is: In the second step, EF not only starts tracking the product when you attach it, but also its associations, because it 'knows' you can't load these associations yourself in a many to many relationship. It doesn't do this, however, when you add the category in the first step.

Upvotes: 6

Related Questions