Thursday, September 01, 2011

When you use Entity Framework for database DML (Insert, Update and Delete) operation then it is very common to get errors like

“Foreign Key References”

“The DELETE statement conflicted with the REFERENCE constraint”

Here you need to be very careful when sending object into entity methods

Normally we use following methods to do database operation using entity framework

AddObject

ApplyChanges

Attach

DeleteObject

Suppose you have some objects which is having child objects like

Object Structure

-Country

----Airports

------Name

--------Address

----FerriesTerminal

------Name

--------Address

Namespace (.Net Entity Framework 4.0)

using System.Collections.Generic;

using System.Linq;

using System.Transactions;

using System.Data.Entity;

Now if you are doing some operation on one particular object like delete “Country”, you have to nullify all the under laying objects before sending it for delete operation.

Like: Let’s say you are working with “Country” object only, not dealing with other under laying object ex: “Airport”, “Name” etc.

List<Country> country = new List<Country>();

Load country object from source or from sample data.

Important

Before doing any database DML operation (Delete/Update/Insert) makes sure all under laying object is NULL

foreach (Country con in country)

{

con.AirPorts = null;

con.FerriesTerminal = null;

}

I solved lots of error applying above for loop in my input objects.

Delete

using (var ctx = DBContext)

{

using (var tc = newTransactionScope())

{

foreach (Country con in country)

{

//Mark con Object for Delete

con.MarkAsDeleted();

ctx.Country.Attach(con);

//Delete data from Country Table

ctx.Country.DeleteObject(con);

}

ctx.SaveChanges();

tc.Complete();

}

}

Another Way of Delete

using (var ctx = DBContext)

{

using (var tc = newTransactionScope())

{

foreach (Country con in country)

{

//Mark con Object for Delete

con.MarkAsDeleted();

//Delete data from Country Table

ctx.Country.ApplyChanges(con);

}

ctx.SaveChanges();

tc.Complete();

}

}

Update

using (var ctx = DBContext)

{

using (var tc = newTransactionScope())

{

foreach (Country con in country)

{

//Mark Object as Modified

con.MarkAsModified()

//Update data into Country Table

ctx.Country.ApplyChanges(con);

}

ctx.SaveChanges();

tc.Complete();

}

}

Insert

using (var ctx = DBContext)

{

using (var tc = newTransactionScope())

{

foreach (Country con in country)

{

//Insert data into Country Table

ctx.Country.AddObject(con);

}

ctx.SaveChanges();

tc.Complete();

}

}

Note: I observed, sometime its take lots of time to figure out what the problem going on and what’s the solution, if it’s really taking so much time to solve the issues with entity framework then I would suggest to use STORED PROCEDURE for complex DML operations.