none
Best way to clear a related set? RRS feed

  • Question

  • What is the best way to clear a related set of entities in LINQ to Entities and Entity Framework? I am using the DbContext model and for my context lazy loading is enabled as is proxy generation. Once I have an entity, and there is a set of related entities referenced via a foreign key relationship, what is the most efficient way to delete all the related entities? Here is a function I have:

     

    private void ResetLineCount(
     int orderID,
     int productID,
     IUnitOfWork uow)
    {
     // Now find all the counts related to this line item and reset them all
     var lines = uow.OrderProducts.Where(e => e.OrderID == orderID && e.ProductID == productID).ToList();
     foreach (var line in lines) {
      // 1. Do it this way?
      line.Counts = new List<OrderCount>();
    
      // 2. Or this way?
      line.Counts.Clear();
      
      // Reset the last modified field for this line item and the cached count value
      line.CountedQty = 0;
      line.LastModified = DateTime.Now;
     }
      
    // Save the changes
    uow.SaveChanges();
    }

     

    Should I clear the entities using the first call (1) by doing:

    line.Counts = new List<OrderCount>();
    

    or should I clear it the second way, by doing:

    line.Counts.Clear();
    
    

    Will either of these methods end up pulling all the related entities down from the database through the Lazy Loading, because I reference line.Counts? I am pretty sure if I do line.Counts.Clear(), it is going to load all the related entities and then toss them away, so if the first method works, it seems it might be more efficient because it would not actually load the entities from the database?

    Or should I eager load the related entities when I load the line item, and then just call Clear() on the result set?


    Tuesday, June 28, 2011 5:14 PM

Answers

  • Hi,

    Are you sure .Clear() deletes the related entities in MySql? In SQL Server the foreign key for the Count rows to that was previously referencing the line is set to NULL, the Count records isn't deleted from the storage.

    To mark it as deleted you need to call context.<your Count dbset>.Remove(<Count entity>);

    So, your code should be: line.Counts.ForEach(row => context.<your Count dbset>.Remove(row));

    Clumsy, but I think it is the only way...


    --Rune
    Tuesday, June 28, 2011 8:07 PM

All replies

  • Hi,

    Are you sure .Clear() deletes the related entities in MySql? In SQL Server the foreign key for the Count rows to that was previously referencing the line is set to NULL, the Count records isn't deleted from the storage.

    To mark it as deleted you need to call context.<your Count dbset>.Remove(<Count entity>);

    So, your code should be: line.Counts.ForEach(row => context.<your Count dbset>.Remove(row));

    Clumsy, but I think it is the only way...


    --Rune
    Tuesday, June 28, 2011 8:07 PM
  • You are right. Neither of my original two methods actually worked when I got around to testing them :(. Which is a real pity.

    I ended up just dropping down to SQL and doing the delete that way, rather than looping on the result set and removing each entity. And that way the actual contents of the set don't need to be loaded into memory for the delete operation to run either, making it much more efficient.

    I really hope in a future version of EF, they add support for batch update and batch deletes natively to the framework, so we can avoid this kind of thing.

    Tuesday, June 28, 2011 8:34 PM