ADO.NET Entity: Insert Update and Delete with Relationship

  • Article

Few days back I had written an article on Insert/Update/Delete for simple standalone tables at ADO.NET Entity: Insert Update and Delete. Now after that many of you had requested me to put article on how it works with relationship.

Here I will use a database created by me. There will be two tables connected with each other.

image

Now I will create TestDB.edmx out of this database.

 

image 

Insert

using (TestDBEntities ctx = new TestDBEntities())

{

    //Create new Department

    Dept d = new Dept() { DeptName = "ADO Entity" };

    //Create new Employee 1

    EmpDept ed1 = new EmpDept() { EmpName = "ADO Employee 1" };

    //Create new Employee 2

    EmpDept ed2 = new EmpDept() { EmpName = "ADO Employee 2" };

    //Add employee to the Dept *OBJECT*

    d.EmpDept.Add(ed1);

    d.EmpDept.Add(ed2);

    //Updating the context

    ctx.AddToDept(d);

    //Save to Database

    ctx.SaveChanges();

}

Update

using (TestDBEntities ctx = new TestDBEntities())

{

    //Get an existing Department

    Dept dep = (from d in ctx.Dept

                where d.DeptId == 22

                select d).First();

    //Set new Department name

    dep.DeptName = "ADO.NET 3.0";

    //Create new Employee 2

    EmpDept ed2 = new EmpDept() { EmpName = "ADO 2" };

    //Add *new* employee to the Dept *OBJECT*

    dep.EmpDept.Add(ed2);

    //Save to Database

    ctx.SaveChanges();

}

Delete

using (TestDBEntities ctx = new TestDBEntities())

{

    //Get an existing Department

    Dept dep = (from d in ctx.Dept.Include("EmpDept")

                where d.DeptId == 22

                select d).First();

    /*

     Needd to do ToList() becuase once you delete

     a record then iteration will not be possible.

    */

    foreach (EmpDept ed in dep.EmpDept.ToList())

    {

        //This removes relationship from Context

        dep.EmpDept.Remove(ed);

        //Delete it from context

        ctx.DeleteObject(ed);

    }

    //Delete the master table

    ctx.DeleteObject(dep);

       

    //Save to Database

    ctx.SaveChanges();

}

Note: during delete you first need to remove the relationship from entity and then delete the object from entity. So you need to keep the child data offline and then do operation. Then delete the main object.

In my next post I will write about “how to select with Relationship”.

Namoskar!!!