What is Batching of Statement in Entity Framework Core?

There are many new features introduced in Entity Framework Core (EF Core). And one of the most awaited feature is, batching of statement. So what exactly batching of statement is? Batching of statement means that it doesn’t send individual command for every insert/update/delete statement. It will batch multiple statements in a single round trip to the database. So In this post, let’s see how it works and then compare the results with EF6.

Batching of Statement in Entity Framework Core

So EF Core will prepare a batch of multiple statements and then executes them in a single round trip. So this provides better performance and speed. Let’s see how it works. We shall take the help of SQL Server Profiler to find out the actual query generated and executed.

Insert Operation

First, let’s see how it behaves in case of insert statements. The following code adds 3 records in the category table.

using (var c= new SampleDBContext())
{
    c.Categories.Add(new Category() { CategoryID = 1, CategoryName = "Clothing" });
    c.Categories.Add(new Category() { CategoryID = 2, CategoryName = "Footwear" });
    c.Categories.Add(new Category() { CategoryID = 3, CategoryName = "Accessories" });
    c.SaveChanges();
}

And when SaveChanges() is executed, following query is generated (taken from SQL Profiler).

exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Categories] ([CategoryID], [CategoryName])
VALUES (@p0, @p1),
(@p2, @p3),
(@p4, @p5);
',N'@p0 int,@p1 nvarchar(4000),@p2 int,@p3 nvarchar(4000),@p4 int,@p5 nvarchar

(4000)',@p0=1,@p1=N'Clothing',@p2=2,@p3=N'Footwear',@p4=3,@p5=N'Accessories'

As you can see, there are no 3 individual insert statements. They are grouped into one statement and makes uses of table valued parameters for the values. And here is the screenshot of SQL Server Profiler.

If we execute the same code against EF 6, there would see 3 individual insert statements in the SQL Server Profiler.

That’s a pretty big difference in terms of performance and speed. And it will also be cost efficient if these queries are run against a cloud deployed database. Now, let’s see what happens in case of update statements.

Update Operation

Following code will get a list of all category records and then iterates through them and append “-Test” text for each category name and finally saves it. At this point of time, there are only 3 records exists in the database.

using (var c= new SampleDBContext())
{
    List<Category> lst = dataContext.Categories.ToList();
    foreach (var item in lst)
    {
        item.CategoryName = item.CategoryName + "-Test";
    }
    c.SaveChanges();
}

And when executed against EF Core, following query is generated (taken from SQL Profiler).

exec sp_executesql N'SET NOCOUNT ON;
UPDATE [Categories] SET [CategoryName] = @p0
WHERE [CategoryID] = @p1;
SELECT @@ROWCOUNT;
UPDATE [Categories] SET [CategoryName] = @p2
WHERE [CategoryID] = @p3;
SELECT @@ROWCOUNT;
UPDATE [Categories] SET [CategoryName] = @p4
WHERE [CategoryID] = @p5;
SELECT @@ROWCOUNT;
',N'@p1 int,@p0 nvarchar(4000),@p3 int,@p2 nvarchar(4000),@p5 int,@p4 nvarchar(4000)',@p1=1,@p0=N'Clothing-Test',@p3=2,@p2=N'Footwear-Test',@p5=3,@p4=N'Accessories-Test'

As you can see, there are 3 update statements, but all are combined into a single SQL statement. And the same code executed against EF 6, there would see 3 individual update statements in the SQL Server Profiler.

With EF 6, there would be 1+N round-trips to the database. One to load the data and then one for each row. But with EF core, save operations are batched so that there would be only two round-trips to the database.

Insert, Update, Delete (all 3) Operations

Now let’s try to mix all 3 operations together and see how EF Core and EF 6 behaves. Following code will updates existing record, insert 2 new records and then finally delete one record.

using (var c= new SampleDBContext())
{
    Category cat = dataContext.Categories.Where(c => c.CategoryID == 3).First();
    cat.CategoryName = "Accessory";
    c.Categories.Add(new Category() { CategoryID = 4, CategoryName = "Fragnance" });
    c.Categories.Add(new Category() { CategoryID = 5, CategoryName = "Sports" });
    Category catToDelete = dataContext.Categories.Where(c => c.CategoryID == 2).First();
    c.Entry(catToDelete).State = EntityState.Deleted;
    c.SaveChanges();
}

And when SaveChanges() is executed, following query is generated (taken from SQL Profiler).

exec sp_executesql N'SET NOCOUNT ON;
DELETE FROM [Categories]
WHERE [CategoryID] = @p0;
SELECT @@ROWCOUNT;
UPDATE [Categories] SET [CategoryName] = @p1
WHERE [CategoryID] = @p2;
SELECT @@ROWCOUNT;
INSERT INTO [Categories] ([CategoryID], [CategoryName])
VALUES (@p3, @p4),
(@p5, @p6);
',N'@p0 int,@p2 int,@p1 nvarchar(4000),@p3 int,@p4 nvarchar(4000),@p5 int,@p6 nvarchar

(4000)',@p0=2,@p2=3,@p1=N'Accessory',@p3=4,@p4=N'Fragnance',@p5=5,@p6=N'Sports'

As you can see, there are individual DELETE, UPDATE and INSERT statements, but grouped into a single SQL statement. And here is the screenshot of SQL Server Profiler.

And what would happen in case of EF 6? Well, you guessed it right. Individual statements will be executed on the database as you can see in SQL Profiler.

So batching with EF Core works quite well and it can certainly boost the speed and performance of your application to a great extent. Wait, what happens in case of large query like a table with 500 columns and 100 rows you want to insert? Is it going to fail?

Well, the batching limit depends on your database provider. As an example, the maximum number of parameters supported by the SQL Server query is 2100. So EF Core works smartly here and will split the query in multiple batches when the batching limit exceeds based on the database provider. But batching everything in one query is sometimes not a good idea. Is there a way to disable batching?

How to disable batching

Yes, you can disable the batching. To disable the batching, you need to configure MaxBatchSize option. You can configure this within OnConfiguring method.

protected override void OnConfiguring(DbContextOptionsBuilder optionbuilder)
{
    string sConnString = @"Server=localhost;Database=EFSampleDB;Trusted_Connection=true;";
    optionbuilder.UseSqlServer(sConnString , b => b.MaxBatchSize(1));
}

Here, 1 is set as max batch size, which means batching would now have only one query. In other words, it behaves like EF 6. To insert 3 records, there would be 3 individual insert statements. Using this option, you can define the maximum batch size.

Summary

Batching was much awaited feature and community asked it a number of times and now EF Core supporting batching out of the box is really great. It can boost performance and speed of your application. At this point of time, EF Core is itself not as powerful compare to EF6, but it will become more and more mature with the time.

Thank you for reading. Keep visiting this blog and share this in your network. Please put your thoughts and feedback in the comments section.

PS: If you found this content valuable and want to return the favour, then