EntityFramework Delete happening before Update when Inserting also

Question

I am getting a foreign key error when trying (probably to do too much) within a single SaveChanges.

Basically I have following 3 poco entities like so.

    public class ClinicianAvailability
    {
        public int ClinicianAvailabilityId { get; set; }
    }

 public class SurgicalBooking
    {
        public int SurgicalBookingId
        public int? ClinicianAvailabilityId { get; set; }
        public bool IsAdhoc { get; set; }

        public virtual TheatreBooking TheatreBooking { get; set; }
        public virtual ClinicianAvailability ClinicianAvailability { get; set; }
    }

    public class TheatreBooking
    {
        public int TheatreBookingId {get;set;}

        public virtual SurgicalBooking SurgicalBooking { get; set; }
        public virtual ClinicianAvailability ClinicianAvailability { get; private set; }
    }

I am basically trying to delete a ClinicianAvailability which has a Foreign Key on the SurgicalBooking. But at the sametime trying to set a new TheatreBooking on the SurgicalBooking.

Like this:

var entity = _clinicianAvailabilityRepository.Find(resourceAvailabilityId);
    var surgStub = surgicalBookingRepository.CreateStub(bookingData.ScheduleId);
    surgStub.IsAdhoc = true;
    surgStub.ClinicianAvailabilityId = null;
surgicalBookingRepository.SetModified(surgStub, new Expression<Func<SurgicalBooking, object>>[] { x => x.IsAdhoc, x => x.ClinicianAvailabilityId });

    theatreBookingRepository.Add( new TheatreBooking
    {
                                    TheatreBooking Id = theatreBookingRepository.GetNewTempKey(),
                                    TheatreId = associatedTheatreId.Value,                           
                                    SurgicalBooking = surgStub
                                });
                                theatreBookingRepository.Add(TheatreBooking);
_clinicianAvailabilityRepository.Remove(entity);
_clinicianAvailabilityRepository.UnitOfWork.SaveChanges();

So basically after doing this I get a foreign key error on the SurgicalBooking foreign key of ClinicianAvailabilityId. If I take out the adding of the TheatreBooking, it goes in the right order by updating Surgical Booking and then deleting. But with Adding the Theatre Booking it tries to do the delete first and then fails. Any help with this? I've tried to simplify this as much as possible but it's a bit complicated. I'm trying to not do multiple save changes or put it all inside a single transaction because it would be a lot of rework to change all this code.

I have looked in the ChangeTracker and all the items seem to be there in the right order, but it doesn't do it in that order.

Check the order of deletion, first delete the table referencing the foreign key, then only the main table data, check if there is more foreign key references — Arun Prasad E S, Jun 06 '16 at 09:46
If the children are attached to the context, EF will generate a DELETE statement for every attached child, then for the parent (because Remove did mark them all as Deleted) You will get detailed explanation here http://stackoverflow.com/a/16576815/1876572 — Eldho, Jun 06 '16 at 09:46

score 0 · Accepted Answer · answered Jun 06 '16 at 11:40

I tried to replicate the error with following code, but it completed successfully:

    static void CreateAndSeedDatabase()
    {
        Context context = new Context();
        ClinicianAvailability cAvail = new ClinicianAvailability() { };
        SurgicalBooking sBooking = new SurgicalBooking() { IsAdhoc = true, ClinicianAvailability = cAvail };
        context.SurgicalBookings.Add(sBooking);
        context.SaveChanges();
    }

    static void DeleteUpdateInsert()
    {
        Context context = new Context();
        ClinicianAvailability cAvail = context.ClinicianAvailabilitys.Find(1);
        SurgicalBooking sBooking = context.SurgicalBookings.Find(1);
        sBooking.IsAdhoc = false;
        sBooking.ClinicianAvailability = null;
        TheatreBooking tBooking = new TheatreBooking(){SurgicalBooking = sBooking};
        context.TheatreBookings.Add(tBooking);
        context.ClinicianAvailabilitys.Remove(cAvail);
        context.SaveChanges();
    }

Context class:

public class Context : DbContext
{
    public Context()
    {
        Database.SetInitializer<Context>(new CreateDatabaseIfNotExists<Context>());
        Database.Initialize(true);
    }

    public DbSet<ClinicianAvailability> ClinicianAvailabilitys { get; set; }
    public DbSet<SurgicalBooking> SurgicalBookings { get; set; }
    public DbSet<TheatreBooking> TheatreBookings { get; set; }
}

Modified POCO classes:

public class ClinicianAvailability
{
    public int ClinicianAvailabilityId { get; set; }
}

public class SurgicalBooking
{
    public int SurgicalBookingId { get; set; }
    public bool IsAdhoc { get; set; }
    public virtual ClinicianAvailability ClinicianAvailability { get; set; }
}

public class TheatreBooking
{
    public int TheatreBookingId { get; set; }

    public virtual SurgicalBooking SurgicalBooking { get; set; }
    public virtual ClinicianAvailability ClinicianAvailability { get; private set; }
}

Thanks you were correct it should work. I found the problem was down to a SetModified method I was using, which was overriding what it was supposed to be doing, because I couldn't do this on the TheatreBooking complex entity. I was trying to do this to avoid more queries, I have since changed it to do a proper find and everything is tracked fine. Thanks for your help. — james.baker, Jun 08 '16 at 08:21

EntityFramework Delete happening before Update when Inserting also

1 Answers1