Entity Framework Core, deleting items from nested collection

Question

I have two classes

 public class InvoiceRow
    {
        public int Id { get; set; }
        public int InvoiceId { get; set; }

        public int ProductId { get; set; }
        public virtual Product Product { get; set; }

        public int Amount { get; set; }
    }



   public class Invoice
    {
            public int Id { get; set; }
            private ICollection<InvoiceRow> _rows;
            public virtual ICollection<InvoiceRow> Rows => _rows ?? (_rows = new List<InvoiceRow>());
    }

I use Update method in the repository class

  public void Update(Invoice record)
  {
            dB.Invoices.Update(record);
            dB.SaveChanges();
  }

It works for updating values in the collection of rows and adding new rows as well, however it doesn't remove items, if I pass object with less rows than it has in the database. What is the best approach to do it?

score 20 · Accepted Answer · 2018-07-15T17:55:27.153

20

That is because the rows in the database are not marked for deletion.

Only new or changed items are updated. 'Missing' items from a collection are not considered to be deleted.

So what you'll need to do is mark the items for deletion yourself. Something like this:

public void Update(Invoice record)
{
    var missingRows = dB.InvoiceRows.Where(i => i.InvoiceId == record.Id)
                        .Except(record.Rows);
    dB.InvoiceRows.RemoveRange(missingRows);

    dB.Invoices.Update(record);
    dB.SaveChanges();
}

edited Jul 15 '18 at 17:55

answered Jul 15 '18 at 10:38

This is causing InvalidOperationException: The instance of entity type 'Invoice' cannot be tracked because another instance with the same key value for {'Id'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see the conflicting key values. – Oleksandr Khytrov Jul 15 '18 at 14:48
1

I found the reason of the exception, we get two instances that are tracked separately. var missingRows = dB.InvoiceRows.Where(i => i.InvoiceId == record.Id).Except(record.Rows); dB.InvoiceRows.RemoveRange(missingRows); This way it works – Oleksandr Khytrov Jul 15 '18 at 17:08
1

I've updated the answer with the information from your comment. – Jul 15 '18 at 17:56
1

`Except` will not work here since it compares object references, not values. That is, unless `InvoiceRow` implements `IEquatable`, overriding `Equals` and `GetHashcode`. Or you could also pass an `IEqualityComparer` to `Except`. Or you could replace `.Except(record.Rows)` with `.Where(ir => !record.Rows.Any(rr => rr.Id == ir.Id))`. – MarredCheese Nov 14 '21 at 20:48

Julio Schurt · Answer 2 · 2022-07-12T21:42:36.020

I'm using Entity Framework Core 6 and the following code works for me.

public void Update(Invoice invoice)
{

        //1. valid invoice rows ids
        var validInvoiceRowIds = invoice.InvoiceRows.Select(ir => ir.Id).ToList();

        //2. missing invoice rows
        var missItems = _context.InvoiceRows
            .Where(ir => ir.InvoiceId == invoice.Id && !validInvoiceRowIds.Contains(ir.Id))
            .ToList();

        _context.RemoveRange(missItems);

        _context.Update(entity);
        _context.SaveChanges();

}

score 1 · Answer 3 · answered Dec 16 '19 at 23:40

Another solution would be to declare a composite primary key InvoiceRow.Id and InvoiceRow.InvoiceId. Now it is an Identifying Relationship. As such, EF Core will indeed delete the child records when they are removed from the parent.

https://stackoverflow.com/a/17726414/7718171

https://stackoverflow.com/a/762994/7718171

remove-from-collection-does-not-mark-object-as-deleted

Entity Framework Core, deleting items from nested collection

3 Answers3

Linked