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

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

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 operation are almost 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.