Shulou(Shulou.com)06/01 Report--

Editor to share with you what the Entity Framework Core batch statement is, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

There are many new features in Entity Framework Core (EF Core), and one of the most anticipated features is batch statements. So what is a batch statement? The batch statement means that it will not send a separate request for each insert / update / delete statement, it will execute multiple statements in batches in a single request of the database. In this article, let's take a look at how it works and compare the results with EF6.

EF Core prepares more than one statement at a time and then executes them in a single request, thus providing better performance and speed. Let's see how it works. We will use SQL Server Profiler to capture the queries actually generated and executed.

Insert operation

First, let's look at the behavior of the insert statement. The following code adds three records to the category table:

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

When SaveChanges () is executed, the following is the generation statement (captured by SQL Server 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)', @ p0u1Med "p1" nude Clothingbooks, "p2", "p2", "p3", "p3", "p4", "p5"

As you can see, there are no three separate insert statements, which are combined into a single statement and use table-valued parameters as values. Here is a screenshot of SQL Server Profiler:

If we execute the same code in EF 6, we will see three separate insert statements in SQL Server Profiler:

This varies greatly in terms of performance and speed. If these queries are targeted at cloud-deployed databases, it will also be more cost-effective. Now, let's see what happens if it is an update statement.

Update operation

The following code takes a list of all category records, then iterates through them, appends "- Test" text to each category name, and saves it. There are only three records in the database at this point in time.

Using (var dataContext = new SampleDBContext ()) {List lst = dataContext.Categories.ToList ()

Foreach (var item in lst) {item.CategoryName = item.CategoryName + "- Test";} dataContext.SaveChanges ();}

And when EF Core executes, the following query is generated (captured by SQL Server Profiler).

Exec sp_executesql N'SET NOCOUNT ON;UPDATE [Categories] SET [CategoryName] = @ p0WHERE [CategoryID] = @ p1 select @ @ ROWCOUNT;UPDATE [Categories] SET [CategoryName] = @ p2WHERE [CategoryID] = @ p3 select @ @ ROWCOUNT;UPDATE [Categories] SET [CategoryName] = @ p4WHERE [CategoryID] = @ p5scape select @ @ ROWCOUNT ', N'@p1 int,@p0 nvarchar (4000), @ p3 int,@p2 nvarchar (4000), @ p5 int,@p4 nvarchar (4000)', @ p1, @ p1, p0, 3, 4, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 5, 5, 3, 4, 4, 5, 5, 3, 4, 4, 5, 2, 4, 4, 4, 4, 5, 4, 4, 5, 4, 4, 5, 4, 5, 4, 4, 4, respectively.

You can see that there are three update statements, but all of them are combined into a single SQL statement. Execute the same code in EF 6, and three separate update statements are displayed in SQL Server Profiler:

With EF 6, there will be 1 + N round trips to the database, loading data once and modifying each row of data; but with EF Core, the save operation is batch, so there are only two round trips to the database.

Insert, update, delete mixed operation

Now let's try to mix the three operations together and look at the behavior of EF Core and EF 6. The following code updates the existing record, inserts two new records, and finally deletes one record.

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

When SaveChanges () is executed, the following query is generated (captured by SQL Server Profiler):

Exec sp_executesql N'SET NOCOUNT ON;DELETE FROM [Categories] WHERE [CategoryID] = @ ROWCOUNT;UPDATE [Categories] SET [CategoryName] = @ p1WHERE [CategoryID] = @ 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 int,@p6 nvarchar 2', @ p0 # 2, "p2" 3, "p1", "p3", "p4", "p5", "5", "p6"

As you can see, there are single DELETE,UPDATE and INSERT statements that are combined into a single SQL statement. Here is a screenshot of SQL Server Profiler:

What happens in EF 6? Well, you guessed right. You can see a single statement executed on the database through SQL Profiler:

Therefore, using EF Core for batch processing can greatly improve the speed and performance of your application. Wait, what happens if a large query, such as a table that inserts 500 columns and 100 rows? Will it fail?

The batch limit depends on your database provider. For example, a SQL Server query supports a maximum number of parameters of 2100, so EF Core works beautifully in this range, and when the batch limit is beyond the scope of the database provider, the query is made in batches. However, it is sometimes not a good way to batch everything in a query. Is there any way to disable batch processing?

How to disable batch processing

Yes, you can disable batch processing. To disable batch processing, you need to modify the MaxBatchSize option, which you can configure in the 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, the maximum batch size is set to 1, which means that the batch can now only be a single query. In other words, it behaves like EF 6, and to insert three records, there will be three separate insert statements. Use this option to define the maximum batch size.

These are all the contents of the article "what are Entity Framework Core batch statements?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.