How does Entity framework implement deleting parent and a child record on the ame SaveChanges()

Question

I have the following two entity sets representing Dept & Emp:-

public partial class Dept
{
    public Dept()
    {
        this.Emps = new HashSet<Emp>();
    }

    public int DeptID { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }

    public virtual ICollection<Emp> Emps { get; set; }
}


public partial class Emp
    {
        public int EmpID { get; set; }
        public string Fname { get; set; }
        public string Lname { get; set; }
        public int DeptID { get; set; }

        public virtual Dept Dept { get; set; }
    }

Now I wrote this test action method , which will try to delete a dept which have one Emp assigned to it, as follow:-

public ActionResult Test()
{
            Dept d = db.Depts.SingleOrDefault(a=>a.id ==1);
            Emp e = db.Emps.SingleOrDefault(a => a.id == 100);
            db.Entry(d).State = EntityState.Deleted;


            db.Emps.Remove(e);
            db.SaveChanges();
            return Content("done");
}

I thought that an exception will be raised when calling this action method, since the Dept with id=1 already has one emp with id=100. But what happened is that EF has removed the emp first, then the dept. As a final result the above action method, deleted both the dept with id=1 and emp with id =100.. so can you advice on this please? Bearing in mind that if I try to delete the Dept only as follows:

public ActionResult Test()
{
                Dept d = db.Depts.SingleOrDefault(a=>a.id ==1);
                //Emp e = db.Emps.SingleOrDefault(a => a.id == 100);
                db.Entry(d).State = EntityState.Deleted;

                //db.Emps.Remove(e);
                db.SaveChanges();
                return Content("done");
}

I will get the following exception:-

The DELETE statement conflicted with the REFERENCE constraint \"FK_Emp_ToTable\". The conflict occurred in database \"C:\USERS\...\DOCUMENTS\VISUAL STUDIO 2013\PROJECTS\WEBAPPLICATION19\WEBAPPLICATION19\APP_DATA\DATABASE1.MDF\", table \"dbo.Emp\", column 'DeptID'.\r\nThe statement has been terminated."}

So can anyone advice on how EF is implementing these scenarios?

EDIT

i check the sql profiler for my action method and i noted the following 2 delete sql statments:-

exec sp_executesql N'delete [dbo].[Emp]
where ([EmpID] = @0)',N'@0 int',@0=100

exec sp_executesql N'delete [dbo].[Dept]
where ([DeptID] = @0)',N'@0 int',@0=1

so it have deleted the emp first then the dept,, so how EF determine the order , you mentioned it is smart enough to know ,, but what govern this behavior ?

Answer 1

You tried to delete a Dept which has a collection of Emp assigned to it.

The following exception occurred

The DELETE statement conflicted with the REFERENCE constraint

That means that there's a constraint to the Dept - Emp relationship. My guess is that it's an one - to many relationship, with one Dept being optional to Emp.

I can tell it's optional because the foreign key DeptID is a Nullable<int>.

When you try to delete a Dept, you get an exception because dept is referenced in Emp.

In the first action method you deleted by primary key

    Dept d = db.Depts.SingleOrDefault(a=>a.id ==1);
    Emp e = db.Emps.SingleOrDefault(a => a.id == 100);

And then used db.Emps.Remove(e); to mark the relationship between as deleted. If the relationship was optional, Emp would be set to Null using only an SQL Update.

In your case i see two SQL Delete statements were called The relationship is therefor identifying.

When a primary key of the principal entity is also part of the primary key of the dependent entity, the relationship is an identifying relationship. In an identifying relationship the dependent entity cannot exist without the principal entity. This constraint causes the following behaviors in an identifying relationship:

Deleting the principal object also deletes the dependent object. This is the same behavior as specifying in the model for the relationship.

Removing the relationship deletes the dependent object. Calling the Remove method on the EntityCollection marks both the relationship and the dependent object for deletion.