RemoveAll() and Remove() not working on one to many relationship

Question

I have a one to many relationship between HouseHolds and Invites. One HouseHold to many Invites. I want to delete an invite from the table after 7 days from when the invite was sent. I'm not getting an error but the expired invites are still in the table. I tried RemoveAll() earlier with no luck. I'm new to this and don't know another method to try, any suggestions?

var dt = DateTime.Now;
var hh = db.HouseHolds.Find(id);
var invList = hh.Invites.ToList();
foreach (var i in invList)
{
    if (DateTime.Compare(i.InviteDate.Date.AddDays(7).Date, dt) < 0)
        hh.Invites.Remove(i);
}

Did you call `SaveChanges` method from your context after that? — ocuenca, Jun 16 '16 at 14:13
But you need to call that method at the end if you want to persist those changes on your DB — ocuenca, Jun 16 '16 at 14:15
The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable — newbieDeveloper, Jun 16 '16 at 14:16
OK, that is another thing, now the problem is you are going to let those invites as orphans, so, you have two options: delete those invites from your database or you asign a different FK value to them, oh as a third option you can change that FK as nullable, but I don't know if that is possible in your model — ocuenca, Jun 16 '16 at 14:19
@cSimms I believe you have to [set the entity state to `Deleted` as well](http://stackoverflow.com/questions/9620422) — D Stanley, Jun 16 '16 at 14:21
@octavioccl I can't change the FK as nullable, each invite needs a HouseHoldId. I definitely want to delete the unused/expired invites from the invite table — newbieDeveloper, Jun 16 '16 at 14:28

score 1 · Accepted Answer · answered Jun 16 '16 at 14:31

Due to the FK property is non-nullable in your Invite table you can't just remove the relationship calling Remove method from Invites navigation property.The problem is those entities will be orphans after that operation. One option to solve this problem is overriding your SaveChanges method:

public override int SaveChanges()
{
     Invites
        .Local
        .Where(r => r.HouseHold== null)
        .ToList()
        .ForEach(r => Invites.Remove(r));

    return base.SaveChanges();
}

Or you can also change the state of those entities as Deleted in your cycle:

foreach (var i in invList)
{
  if (DateTime.Compare(i.InviteDate.Date.AddDays(7).Date, dt) < 0)
  {
     hh.Invites.Remove(i);
     db.Entry(i).State = EntityState.Deleted;
  }
}

That worked! Thanks so much @octavioccl and @D Stanley – newbieDeveloper Jun 16 '16 at 14:48 — newbieDeveloper, Jun 16 '16 at 14:48

RemoveAll() and Remove() not working on one to many relationship

1 Answers1