中文文档 Chinese version

Using this library, Entity Framework Core users can delete or update multiple records from a LINQ Query in a SQL statement without loading entities. This libary supports Entity Framework Core 5.0 and above.

Install NuGet Package:

As for Postgresql (with Npgsql.EntityFrameworkCore.PostgreSQL) users, please use: Install-Package Zack.EFCore.Batch.Npgsql

As for MS SQLServer users, please use: Install-Package Zack.EFCore.Batch.MSSQL

As for MySQL(with Pomelo.EntityFrameworkCore.MySql) users, please use: Install-Package Zack.EFCore.Batch.MySQL.Pomelo

As for Sqlite users, please use: Install-Package Zack.EFCore.Batch.Sqlite

As for Oracle users, please use: Install-Package Zack.EFCore.Batch.Oracle

Depending on the database, add the following code into OnConfiguring() method of your DbContext respectively.

optionsBuilder.UseBatchEF_MSSQL();// as for MSSQL Server
optionsBuilder.UseBatchEF_Npgsql();//as for Postgresql
optionsBuilder.UseBatchEF_MySQLPomelo();//as for MySQL
optionsBuilder.UseBatchEF_Sqlite();//as for Sqlite
optionsBuilder.UseBatchEF_Oracle();//as for Oracle

Use the extension method DeleteRangeAsync() of DbContext to delete a set of records. The parameter of DeleteRangeAsync() is the lambda expression of the filter Example code:

await ctx.DeleteRangeAsync<Book>(b => b.Price > n || b.AuthorName == "zack yang"); 

The code above will execute the following SQL statement on database：

Delete FROM [T_Books] WHERE ([Price] > @__p_0) OR ([AuthorName] = @__s_1)

and the DeleteRange() is the synchronous version of DeleteRangeAsync().

Use the extension method BatchUpdate() of DbContext to create a BatchUpdateBuilder. There are four methods in BatchUpdateBuilder as follows

• Set() is used for assigning a value to a property. The first parameter of the method is the lambda expression of the property, and the second one is the lambda expression of the value.
• Where() is used for setting the filter expression
• ExecuteAsync() is an asynchronous method that can execute the BatchUpdateBuilder, and the Execute() is a synchronous alternative of ExecuteAsync()

Example code:

await ctx.BatchUpdate<Book>()
    .Set(b => b.Price, b => b.Price + 3)
    .Set(b => b.Title, b => s)
    .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();

The code above will execute the following SQL statement on database：

Update [T_Books] SET [Price] = [Price] + 3.0E0, [Title] = @__s_1, [AuthorName] = COALESCE(SUBSTRING([Title], 3 + 1, 2), N'') + COALESCE(UPPER([AuthorName]), N''), [PubTime] = GETDATE()
WHERE ([Id] > @__p_0) OR ([AuthorName] IS NOT NULL AND ([AuthorName] LIKE N'Zack%'))

Take() and Skip() can be used to limit the affected rows of DeleteRangeAsync and BatchUpdate:

await ctx.Comments.Where(c => c.Article.Id == id).Skip(3).DeleteRangeAsync<Comment>(ctx);
await ctx.Comments.Where(c => c.Article.Id == id).Skip(3).Take(10).DeleteRangeAsync<Comment>(ctx);
await ctx.Comments.Where(c => c.Article.Id == id).Take(10).DeleteRangeAsync<Comment>(ctx);

await ctx.BatchUpdate<Comment>().Set(c => c.Message, c => c.Message + "abc")
	.Where(c => c.Article.Id == id)
	.Skip(3)
	.ExecuteAsync();

await ctx.BatchUpdate<Comment>().Set(c => c.Message, c => c.Message + "abc")
	.Where(c => c.Article.Id == id)
	.Skip(3)
	.Take(10)
	.ExecuteAsync();
await ctx.BatchUpdate<Comment>().Set(c => c.Message, c => c.Message + "abc")
   .Where(c => c.Article.Id == id)
   .Take(10)
   .ExecuteAsync();

At this point, BulkInsert cannot be supported on SqlLite.

List<Book> books = new List<Book>();
for (int i = 0; i < 100; i++)
{
	books.Add(new Book { AuthorName = "abc" + i, Price = new Random().NextDouble(), PubTime = DateTime.Now, Title = Guid.NewGuid().ToString() });
}
using (TestDbContext ctx = new TestDbContext())
{
	ctx.BulkInsert(books);
}

This library utilizes the EF Core to translate the lambda expression to SQL statement, so it supports nearly all the lambda expressions which EF Core supports.

The following databases have been tested that they can work well with Zack.EFCore.Batch: MS SQLServer(Microsoft.EntityFrameworkCore.SqlServer), MySQL(Pomelo.EntityFrameworkCore.MySql), PostgreSQL(Npgsql.EntityFrameworkCore.PostgreSQL), Oracle(Oracle.EntityFrameworkCore). In theory, as long as a database has its EF Core 5 Provider , the database can be supported by this library. If you are using a database that is not currently supported, please submit an issue. I can usually complete the development within one working day.

Report of this library