ADO.NET Entity: Insert Update and Delete with Relationship

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.

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

 

 

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!!!

Comments

• Anonymous
  November 27, 2008
  Great, super straight forward.  I've been looking for this basic clean cut example and your other (without relationship) post all morning!   Thank you!

• Anonymous
  December 05, 2008
  Great post! Thanks for the example and I look forward to your next post!

• Anonymous
  December 08, 2008
  I have been working with the ADO.NET Entities Data Model for ORM and I am very impressed. I'd been

• Anonymous
  December 23, 2008
  Great Example. Simple, but explain complex things. Finally I solve my problems with your post. Thanks Again. Dinesh.

• Anonymous
  February 18, 2009
  Good post? Thanks! But, I have question!! In this post ( part 'insert') inserting row to table "Dept" and table "EmpDept". But, I having full list in table "Dept". I dont want add new rows. User show to  field "Name" in table  "Dept"  to insert in table "EmpDept" in interface. I want add row only in table "EmpDept" for showed dept !! Please, help me. mirralla@gmail.com

• Anonymous
  February 22, 2009
  Good post and i have a question just like Mirra what about select the related field from a list ? how could i do that thnx!

• Anonymous
  February 23, 2009
  For Mirra and Sam If I have understood you correctly, you want to add child (EmpDept) against a master (dept). If you just want to add Child then follow (Update) where I am adding another new EmpDept

• Anonymous
  March 29, 2009
  I use ADO Data Services with Silverlight. But I've a problem - I can't add a new entity to relationship table (EmpDept). What's my problem?

• Anonymous
  June 05, 2009
  Hi, the tutorial is very good, but I have some questions. How can I manage the N to N relationships ? How can I map a db table with 2 entity respect to a boolena value? Thanks

• Anonymous
  July 10, 2009
  I have another problem with your example. When i create an EDM out of these two tabels, DeptId disapeares out of EmpDept. I did make a relation between those two tabels What is going on? Ans. WG Everything is write

• Anonymous
  July 28, 2009
  I want to insert a Dept first, then i insert a EmpDept with his DeptId, is it possible? PD: {"Infracción de la restricción PRIMARY KEY 'PK_cliente'. No se puede insertar una clave duplicada en el objeto 'dbo.cliente'.rnSe terminó la instrucción."}

• Anonymous
  July 31, 2009
  @Wilson, Please visit http://blogs.msdn.com/wriju/archive/2008/10/16/ado-net-entity-insert-update-and-delete-with-relationship.aspx

• Anonymous
  August 28, 2009
  Hi wriju Can you give me your Email? I have some question from you? Thanks

• Anonymous
  February 22, 2010
  I want to use distinct i entitydatamodel but it's not allowing me to do my code is like the following. var test = (from t in getdetails()            where t.id == id            select t.name,             t.dateofbirth,t.number ).distinct(); But it's not allowing me to do. it's giving me build errors. Can any body help me as soon as possible please. Thanks in advance.

• Anonymous
  April 08, 2010
  Great post!  This just made my day so much easier.  Thanks wriju.

• Anonymous
  February 16, 2012
  Hi, nice, clear, and simple ... great reference ... thanks man.

• Anonymous
  March 06, 2012
  Is it posible to update more than one entities through the same entity ? something like this ? var d = (from data in context.data        from otherDB in context.otherDB        where data.id = someID && otherDB.someText = "SomeText"        select data).FirstOrDefault(); d.Entity1 = "sometext"; d.entity2 = "someOtherText or number" ; d.SomeValue = 20; context.SaveChanges();

• Anonymous
  January 05, 2013
  Grate Post