Entity Framework: Batch Insert Update Delete Operations

Bhavik Patel

Entity FrameworkBatch Insert Update Delete Operations

21 Comments

Join the conversation! Your thoughts help the community grow.

Alex Smith5y ago
When calling UpdateAll, is there a way to get rows affected?
Zack Yang5y ago
I have created a library to batch delete or update records with a round trip on EF Core 5. Sample code as follows: await ctx.DeleteRangeAsync(b => b.Price > n || b.AuthorName == "zack yang"); await ctx.BatchUpdate() .Set(b => b.Price, b => b.Price + 3) .Set(b=>b.AuthorName,b=>b.Title.Substring(3,2)+b.AuthorName.ToUpper()) .Set(b => b.PubTime, b => DateTime.Now) .Where(b => b.Id > n || b.AuthorName.StartsWith("Zack")) .ExecuteAsync(); Github repository: https://github.com/yangzhongke/Zack.EFCore.Batch Report: https://www.reddit.com/r/dotnetcore/comments/k1esra/how_to_batch_delete_or_update_in_entity_framework/
Devendra Lodha8y ago
Hi Bhavik,Just want to ask one question, if the user wants to insert 10000 records would that work using AddRange.
Hamid Khan9y ago
Very good article thanks we can also use batch operation:https://www.credera.com/blog/technology-insights/microsoft-solutions/entity-framework-batch-operations-using-ef-utilities/
Subash10y ago
Nice
Hari Shanker10y ago
Nice
Mohammed Ibrahim10y ago
nice
- Bhavik Patel10y ago
  Thank you
Rahul Kumar Saxena10y ago
Good Show
- Bhavik Patel10y ago
  Thank you Rahul
Humayun Kabir Mamun10y ago
Nice...
- Bhavik Patel10y ago
  Thank you
Jaipal Reddy10y ago
good one. .
- Bhavik Patel10y ago
  Thank you jaipal
Manas Mohapatra10y ago
Informative one
- Bhavik Patel10y ago
  Thank you Manas
Vivek Kumar10y ago
Nice article
Humayun Kabir Mamun10y ago
Nice...
- Bhavik Patel10y ago
  thanks
Banketeshvar Narayan10y ago
Good One
- Bhavik Patel10y ago
  Thank you

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

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

Customer class

public class Customer
{
[Key]
public int Id
{
get;
set;
}
public string Name
{
get;
set;
}
public string Country
{
get;
set;
}
public bool Status
{
get;
set;
}
}

DataContext

I used entityframework 6 for data access.

public class DataContext: DbContext
{
public DbSet < Customer > Customers
{
get;
set;
}
}

GetCustomers method is used to get all customers, which we will use for performing batch Insert operation.

public static List < Customer > GetCustomers()
{
var customers = new List < Customer >
{
new Customer()
{
Name = "John", Country = "IN", Status = true
},
new Customer()
{
Name = "Tom", Country = "USA", Status = true
},
new Customer()
{
Name = "Eric", Country = "USA", Status = false
},
new Customer()
{
Name = "Sam", Country = "CHINA", Status = true
},
new Customer()
{
Name = "Rick", Country = "IN", Status = false
},
new Customer()
{
Name = "Addy", Country = "IN", Status = true
},
new Customer()
{
Name = "Chang", Country = "CHINA", Status = false
},
};
return customers;
}

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.

static void Main(string[] args)
{
using(var db = new DataContext())
{
// Insert
var customers = GetCustomers();
db.Customers.AddRange(customers);
db.SaveChanges();
foreach(var customer in db.Customers.ToList())
{
Console.WriteLine("CustomerInfo - {0}-{1}-{2}", customer.Name, customer.Country, customer.Status);
}
}
Console.ReadLine();
}

Output

It will insert all records on a single database call.

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

static void Main(string[] args)
{
using(var db = new DataContext())
{
db.Customers.Where(c => c.Country == "USA").Update(c => new Customer()
{
Country = "IN"
});
foreach(var customer in db.Customers.ToList())
{
Console.WriteLine("CustomerInfo - {0}-{1}-{2}", customer.Name, customer.Country, customer.Status);
}
}
Console.ReadLine();
}

Output

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

Delete

Scenario: Delete customers which have country China. Batch update and delete operations are almost the same. Add where condition to both Delete and Update call EntityFramework.Extended function.

static void Main(string[] args)
{
using(var db = new DataContext())
{
db.Customers.Where(c => c.Country == "CHINA").Delete();
foreach(var customer in db.Customers.ToList())
{
Console.WriteLine("CustomerInfo - {0}-{1}-{2}", customer.Name, customer.Country, customer.Status);
}
}
Console.ReadLine();
}

Output: Two records are deleted having a customer name, Chang and Sam.

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.