Entity Framework: Batch Insert Update Delete Operations

Entity Framework is a well-known Microsoft open source (from EF 5) data access technology for .NET applications. Entity Framework is a new framework which completely replaces traditional ADO.NET data access techniques. Entity Framework enables new approaches to work with relational databases. It will reduce line of code that developers uses for data access. This article will briefly describe how to perform batch CRUD operations using entity framework and its extensions.

To implement Bulk operation, we need to add some base classes.

Customer class

  1. public class Customer   
  2. {  
  3.     [Key]  
  4.     public int Id  
  5.     {  
  6.         get;  
  7.         set;  
  8.     }  
  9.   
  10.     public string Name  
  11.     {  
  12.         get;  
  13.         set;  
  14.     }  
  15.   
  16.     public string Country  
  17.     {  
  18.         get;  
  19.         set;  
  20.     }  
  21.   
  22.     public bool Status  
  23.     {  
  24.         get;  
  25.         set;  
  26.     }  
  27. }  
DataContext

I used entityframework 6 for data access.
  1. public class DataContext: DbContext  
  2. {  
  3.     public DbSet < Customer > Customers   
  4.     {  
  5.         get;  
  6.         set;  
  7.     }  
  8. }  
GetCustomers method is used to get all customers, which we will use for performing batch Insert operation.
  1. public static List < Customer > GetCustomers()  
  2. {  
  3.     var customers = new List < Customer >  
  4.         {  
  5.         new Customer()  
  6.           {  
  7.             Name = "John", Country = "IN", Status = true  
  8.         },  
  9.           
  10.         new Customer()  
  11.         {  
  12.             Name = "Tom", Country = "USA", Status = true  
  13.         },  
  14.         new Customer()  
  15.         {  
  16.             Name = "Eric", Country = "USA", Status = false  
  17.         },  
  18.         new Customer()   
  19.         {  
  20.             Name = "Sam", Country = "CHINA", Status = true  
  21.         },  
  22.         new Customer()  
  23.         {  
  24.             Name = "Rick", Country = "IN", Status = false  
  25.         },  
  26.         new Customer()   
  27.         {  
  28.             Name = "Addy", Country = "IN", Status = true  
  29.         },  
  30.         new Customer()  
  31.         {  
  32.             Name = "Chang", Country = "CHINA", Status = false  
  33.         },  
  34.     };  
  35.   
  36.     return customers;  
  37. }  
Insert

After a couple of improvements in Entity Framework that makes adding many rows to a SQL Server Compact database via Entity Framework feasible. AddRange is in built function to perform batch operation.
  1. static void Main(string[] args)   
  2. {  
  3.     using(var db = new DataContext())  
  4.     {  
  5.         // Insert  
  6.         var customers = GetCustomers();  
  7.         db.Customers.AddRange(customers);  
  8.         db.SaveChanges();  
  9.   
  10.         foreach(var customer in db.Customers.ToList())  
  11.         {  
  12.             Console.WriteLine("CustomerInfo - {0}-{1}-{2}", customer.Name, customer.Country, customer.Status);  
  13.         }  
  14.     }  
  15.   
  16.     Console.ReadLine();  
  17. }  
Output

It will insert all records on a single database call.

output

Update and Delete

A current limitation of the Entity Framework is that in order to update or delete an entity you have to first retrieve it into memory. Also, for single deletes, the object must be retrieved before it can be deleted requiring two calls to the database. To overcome this problem we have to extend the current entity framework using EntityFramework.Extended. EntityFramework.Extended have useful features like Batch Update and Delete, Audit log, Query Result cache, Future Queries. Batch update and delete eliminates the need to retrieve and load an entity before modifying it. Here are a few lines of code to demonstrate how to delete, update.

Install via nuget

PM> Install-Package EntityFramework.Extended

Update

Scenario:
Update customers which have country USA.
If we do this without any extensions, we have to fetch all customers which have country USA, modify the list and update it using loops. Using Entity Framework.Exdended we don’t need to fetch the list of customers, simply add where condition, set update data and execute query.
  1. static void Main(string[] args)  
  2. {  
  3.     using(var db = new DataContext())  
  4.     {  
  5.         db.Customers.Where(c => c.Country == "USA").Update(c => new Customer()  
  6.         {  
  7.             Country = "IN"  
  8.         });  
  9.   
  10.         foreach(var customer in db.Customers.ToList())   
  11.         {  
  12.             Console.WriteLine("CustomerInfo - {0}-{1}-{2}", customer.Name, customer.Country, customer.Status);  
  13.         }  
  14.     }  
  15.   
  16.     Console.ReadLine();  
  17. }  
Output

All customers (Tom and Eric) which have country USA will update to IN.

output

Delete
 
Scenario: Delete customers which have country China. Batch update and delete operation are almost same. Add where condition to both Delete and Update call EntityFramework.Extended function.
  1. static void Main(string[] args)  
  2. {  
  3.     using(var db = new DataContext())  
  4.     {  
  5.         db.Customers.Where(c => c.Country == "CHINA").Delete();  
  6.   
  7.         foreach(var customer in db.Customers.ToList())  
  8.         {  
  9.             Console.WriteLine("CustomerInfo - {0}-{1}-{2}", customer.Name, customer.Country, customer.Status);  
  10.         }  
  11.     }  
  12.   
  13.     Console.ReadLine();  
  14. }  
Output: Two records are deleted having a customer name Chang and Sam.

output

Conclusion

Entity framework is improving day by day. But Entity framework extensions and utilities make developer’s life easy. They enhances and optimizes Entity Framework's performance, add more capabilities, add more features and extend current version limitation. Hope Microsoft will include Batch insert, update and delete facility in Entity Framework version.