SQL Server Database DML Operation (Insert/ Update /Delete) with Microsoft Entity Framework 4.0
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 = new TransactionScope())
{
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 = new TransactionScope())
{
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 = new TransactionScope())
{
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 = new TransactionScope())
{
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.
