cSimms cSimms - 5 months ago 9
SQL Question

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

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);
}

Answer

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;
  }
}