ADO.NET Entity: Insert Update and Delete with Relationship

ADO.NET Entity: Insert Update and Delete with Relationship

Rate This
  • Comments 18

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

 

Leave a Comment
  • Please add 7 and 2 and type the answer here:
  • Post
  • Great, super straight forward.  I've been looking for this basic clean cut example and your other (without relationship) post all morning!  

    Thank you!

  • Great post! Thanks for the example and I look forward to your next post!

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

  • Great Example. Simple, but explain complex things.

    Finally I solve my problems with your post.

    Thanks Again.

    Dinesh.

  • 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

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

  • 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

  • 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?

  • 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

  • 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

  • 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'.\r\nSe terminó la instrucción."}

  • @Wilson,

    Please visit http://blogs.msdn.com/wriju/archive/2008/10/16/ado-net-entity-insert-update-and-delete-with-relationship.aspx

  • Hi wriju

    Can you give me your Email?

    I have some question from you?

    Thanks

  • 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.

  • Great post!  This just made my day so much easier.  Thanks wriju.

Page 1 of 2 (18 items) 12