LINQ Bulk Insert Update Delete

After long time I have free time so I think this is the best time to update my blog with new post and this time I choose to write something on bulk database operations. Well, when I came across the requirement of bulk database operations in one of my project, I search hard on net and finally I found one method that I can use.  I am going to show the method which I use in one of my project for bulk insert, update and delete.
During working on bulk database operations from LINQ to entities, first thing came in my mind is transaction scope. i.e.I should have command on each of the database transactions I am going to do from my data access layer so that in case of error I can easily rollback all the database changes. To archive transaction scope functionality, I use “System.Transactions.TransactionScope" class. Below is the code I use for bulk insert.
public void BulkInsert(Order[] orders)
{
try
{
using (System.Transactions.TransactionScope scop = new System.Transactions.TransactionScope())
{
using (NorthwindEntities entity = new NorthwindEntities())
{
foreach (Order order in orders)
{
entity.AddToOrders(order);
}
entity.SaveChanges();
}
scop.Complete();
}
}
catch (Exception ex)
{
throw ex;
}
}

So, you can see how easy bulk inserting is, in LINQ to entity framework. Update or delete multiple records from database is quite different then bulk insert. Below is the method for bulk update or delete.
public void BulkDelete(int[] orderIDs)
{
try
{
using (System.Transactions.TransactionScope scop = new System.Transactions.TransactionScope())
{
using (LINQToEF.DataAccess.NorthwindEntities entity = new NorthwindEntities())
{
entity.Connection.Open();
foreach (int orderID in orderIDs)
{
var orderLinq = from o in entity.Orders
where o.OrderID == orderID
select o;

Order order = orderLinq.FirstOrDefault();

entity.DeleteObject(order);
}
entity.SaveChanges();
entity.Connection.Close();
}
scop.Complete();
}
}
catch (Exception ex)
{
throw ex;
}
}

That’s all!!! Hope you can do bulk update on your own by using bulk delete code.

I am not sure all developers working in LINQ to entity framework are using this method or not. I know bulk update and delete method is quite heavy in LINQ to entity framework as first it need to fetch the record from database which we need to update or delete but I can’t found any other easy method then this so currently I am using this method in my projects. I have tested this methods with around 5000 records. i.e. I am inserting, updating and deleting around 5000 records using this method and it’s working fine in my projects.

Will post other methods if I found in future… Bye for now…

Comments

  1. ZZZ ProjectsDecember 4, 2014 at 5:10 AM

    ZZZ Projects offers 2 kinds of bulk operations via the Entity Framework Extensions Library (http://zzzprojects.com/entity-framework-extensions/). They increase drastically your performance over the SaveChanges method from Entity Framework.

    BulkSaveChanges
    The first way is via our main feature, the BulkSaveChanges method which literally replaces the SaveChanges method. You can expect to save your entities a minimum of 15x times faster and have better performances with only one entity too. This method supports all kind of associations and entity types (TPC, TPH, and TPT), you use it the same way you used the SaveChanges method.

    Bulk Operations
    The second ways is via Bulk Operations methods (BulkDelete, BulkInsert, BulkUpdate and BulkMerge) which increases even more the performance and allows customization for many settings like what's the primary key to use.

    ReplyDelete

Post a Comment

Popular posts from this blog

Lambda Expressions In Entity Framework

From the two options I mentioned in my last post, I chose lambda expression for this post for those programmers who are not much comfortable with SQL like LINQ query. Microsoft gives us very good option called “Lambda Expression”. In this post I am going to demonstrate below operations: Simple select query using LINQ & Simple select query using lambda expression Select query with where condition using LINQ & select query with where condition using lambda expression Inner Join using LINQ & Inner Join using lambda expression Left Join using LINQ & Left Join using lambda expression Group by using LINQ & Group by using lambda expression Sorting using LINQ & Sorting using lambda expression Simple select: // Using LINQ Employee [] allEmployees1 = ( from E in NorthwindEntity.Employees select E).ToArray(); // Using lambda expression Employee [] allEmployees2 = NorthwindEntity.Employees.ToArray(); // Where condition using LINQ Employee [] allEmployees1 = ( from
Read more

LINQ To Entity Framework Tutorial

As a startup, I choose this topic for those beginners who wants to use Entity Framework. The ADO.NET Entity Framework helps developers to access database very easily. All you need is to create a data model from actual database and programming against generated data model instead of programming directly against the actual database by either creating stored procedure or writing select/insert/update/delete query in data access layer itself. Using this way of programming during the development phase of the project helps to reduce amount of code to access database and also reduce maintenance work in future. Please follow below steps in order to create data access application using ADO.NET Entity Framework. In this tutorial I am using “Northwind” database in backend. Create new “ASP.Net Empty Web Application” using Visual Studio 2010. Create new folder under web application project. Name it “DataAccess” Right click on “DataAccess” folder and click on Add –> New Item… Select “Dat
Read more

Select Columns From Multiple Tables Using LINQ

From Northwind database, to show all available products it needs to select values from multiple tables because in Products table there are two columns – CategoryID and SupplierID which are referenced from Category and Suppliers tables. There are more then one options to do this but I use below way to select from multiple tables and bind to grid. First of all create a class and create properties, ProductID, ProductName, CompanyName, CategoryName, UnitPrice. These are the properties which I want to show in grid. public class AvailableProducts { public int ProductID { get ; set ; } public string ProductName { get ; set ; } public string CompanyName { get ; set ; } public string CategoryName { get ; set ; } public decimal UnitPrice { get ; set ; } } Now, write below function in data access layer: public List < AvailableProducts > GetAllAvailableProducts() { var linqAllProducts = from p in NorthwindEntity.Products from c in NorthwindEntity.Categories from s in NorthwindEntity.Supp
Read more