本文介绍了一个在Entity Framework Core 5中不需要预先加载数据而使用一句SQL语句批量更新、删除数据的开发包，并且分析了其实现原理，并且与其他实现方案做了比较。

一、背景

随着微软全面拥抱开源，.Net开源社区百花开放，涌现了非常多优秀的开源，ORM项目就有Dapper、SqlSugar、PetaPoco、FreeSQL等。作为微软官方提供的ORM框架，Entity Framework Core(以下简称EF Core)显然是被关注最多的。EF Core非常优秀而且功能丰富，但是EF Core有一个一直被人诟病的地方就是它并不能很好支持数据的批量更新和批量删除。在EF Core中批量更新和删除数据都要先把数据加载到内存中，然后再对数据操作，最后再SaveChanges，比如下面的代码用于把所有Id大于2或者AuthorName中含有”zack”的价格增加3：var books2 = ctx.Books.Where(b => b.Id > 2||b.AuthorName.Contains("zack"));

foreach(var b in books2)

{

b.Price = b.Price + 3;

}

ctx.SaveChanges();

让我们查看上面的程序幕后执行的SQL语句：

可以看到，EF Core先把数据用Select查询出来，然后在内存中逐个修改，最后再把被修改对象每个都执行一次Update语句去更新。

再比如，如下的代码用于删除Price大于5元的记录：var books1 = ctx.Books.Where(b => b.Price > 5);

ctx.RemoveRange(books1);

ctx.SaveChanges();

让我们查看上面的程序运行幕后执行的SQL语句：

可以看到，EF Core先把数据用Select查询出来，然后再对每条记录都执行Delete语句去删除。

很显然，如果批量更新或者删除的数据量比较大，这样的操作性能是非常低的。

因此，我们需要一种在EF Core中使用一条SQL语句就高性能地删除或者更新数据的方法。

二、为什么微软不提供这样的方法

尽管用户的要求强烈，但是微软一直没有提供高效的批量删除和更新的方式。在EF Core Github的issue中 [1]，微软给出的理由是：这样做会导致EF Core的对象状态跟踪混乱，比如对于同一个DbContext，如果用批量删除的方法删除了数据，那么在被删除之前查询出来的数据状态就混乱了，因此需要重构EF Core的代码，工作量比较大。

作为一个成熟的框架，考虑这些逻辑问题以避免潜在的风险是有必要的，是可以理解的。但是作为实际的开发者，我们是有办法规避这些问题的。比如一般的Web应用中，删除操作都是在一个单独的Http请求进行中的，因此不涉及到微软担心的问题。即使在有的场景下，涉及到在通过同一个DbContext在数据删除之前就把数据查询出来的场景，那么也完全可以通过在删除之后再查一次的方式来规避这个问题。

根据github上那个issue的回复，微软有考虑在EF Core 6.0中加入高效地批量删除和更新数据的方式，但是仅仅是“考虑”，并不确定。我们作为普通开发者可等不及了，因此要自己去解决。

三、已有解决方法

有如下三种已有的解决方法：

1.     执行原生SQL语句。在EF Core中提供了ctx.Database.ExecuteSqlRaw()等方法可以用来执行原生SQL语句，因此我们可以直接编写Delete、Update语句来删除或者更新数据。这种方式比较直接，缺点就是这样代码中直接操作数据表的方式不太符合模型驱动、分层隔离等思想，程序员直接面对数据库表，无法利用EF Core强类型的特性，如果模型发生改变，必须手动变更SQL语句；而且如果调用了一些DBMS特有的语法、函数，一旦程序迁移到其他DBMS，就可能要重新编写SQL语句，而无法利用EF Core强大的SQL翻译机制来屏蔽不同底层数据库的差异。

2.     使用其他ORM。FreeSQL等ORM中提供了批量Delete、Update语句的方法，使用也非常简单。这种方式的缺点是项目中必须引入第三方的ORM，无法复用EF Core的代码。

3.     使用已有的EF Core扩展。EF Plus、EFCore.BulkExtensions等开源库中都提供了在EF Core框架下进行批量操作的方法。实现这个的核心就是要获得EF Core生成的SQL语句以及SelectExpression。由于EF Core 5.0之前的版本中没有提供公开的API用于获取一个LINQ操作对应的SQL语句，所以这些开源库都是通过访问EF Core框架中一些类的私有成员来完成的获取LINQ对应的SQL语句以及SelectExpression的方法 [2]。由于用的是访问私有成员这样不符合面向对象原则的方式，所以一旦EF Core框架代码发生改变，代码就可能会失败，之前就发生过EF Core新版本发布造成这些开源库无法工作的情况。而且，在撰写这篇文章的时候，这些开源库还没有适配.Net 5。

四、我的实现Zack.EFCore.Batch

我开发了一个Entity Framework Core的扩展库，让开发者在Entity Framework Core中可以用一句SQL进行数据的删除或者更新。由于开发中用到了Entity Framework Core 5的API，所以这个库要求Entity Framework Core 5及以上版本，也就是.Net 5及以上版本。

下面介绍一下使用方法：

第一步，通过Nuget安装 Install-Package Zack.EFCore.Batch

第二步，把如下代码添加到你的DbContext类的OnConfiguring方法中：

optionsBuilder.UseBatchEF();

第三步: 使用DbContext的扩展方法DeleteRangeAsync()来删除一批数据. DeleteRangeAsync()的参数就是过滤条件的lambda表达式。

批量删除的例子代码如下:

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

上面的代码将会在数据库中执行如下SQL语句：Delete FROM [T_Books] WHERE ([Price] > @__p_0) OR ([AuthorName] = @__s_1)

DeleteRange()方法是DeleteRangeAsync()的同步方法版本。

使用DbContext的扩展方法BatchUpdate()来创建一个BatchUpdateBuilder对象。 BatchUpdateBuilder类有如下四个方法：

1)      Set()方法用于给一个属性赋值。方法的第一个参数是属性的lambda表达式,第二个参数是值的lambda表达式。

2)      Where() 是过滤条件

3)      ExecuteAsync()使用用于执行BatchUpdateBuilder的异步方法

4)      Execute()是ExecuteAsync()的同步方法版本。

例子代码:await ctx.BatchUpdate()

.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();

上面的代码将会在SQLServer数据库中执行如下SQL语句：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%'))

这个开发包使用EF Core实现的lambda表达式到SQL语句的翻译，所以几乎所有EF Core支持的lambda表达式写法都被支持。

项目的GitHub地址： https://github.com/yangzhongke/Zack.EFCore.Batch

五、实现原理分析

其实要把lambda表达式转换为SQL语句并不难，只要对表达式树进行解析就可以生成SQL语句，但是最难的部分是对于.Net函数到SQL片段的翻译，因为相同的.Net函数在不同DBMS中等效的SQL片段是不同的，如果我自己实现这个是很麻烦的，因此我想到了直接借用EF Core的表达式树到SQL语句的翻译引擎来实现是最佳的方法。

不幸的是，在.Net Core 3.x及之前，是无法直接获取一个Linq查询翻译后的SQL语句的。.Net Core中可以通过日志等方式获取翻译后的SQL语句，但是这些都是Linq执行后才能获得的，而且是无法在拿到一个Lambda表达式或者IQueryable的时候立即获得SQL的。经过询问.Net Core开发团队得知，在.Net Core 3.X及之前，也是没有公开的API可以完成表达式树到SQL片段翻译的功能。

从.Net 5开始，Entity Framework Core 中提供了不用执行查询，就可以直接获取Linq查询对应的SQL语句的方法，那就是调用IQueryable的ToQueryString()方法 [3]。

因此我就想通过这个ToQueryString()方法拿到的SQL语句来入手来实现这个功能。 可以把用到的Lambda表达式片段、过滤表达式拼接到一个查询表达式中，然后调用ToQueryString()方法获取翻译后的SQL语句，然后编写词法分析器和语法分析器对SQL语句进行分析，提取出Where子句以及Select列中的表达式片段，然后再把这些片段重新组合成Update、Delete的SQL语句即可。

不过，由于不同DBMS的语法不同，编写这样的词法及语法分析器是很麻烦的，我就想能否研究ToQueryString()的实现原理，然后直接拿到解析过程中的SQL片段，这样就避免了生成SQL后再去解析的工作。

虽然EF Core是开源的，不过由于关于EF Core的源代码并没有一个全面介绍的文档，而EF Core的代码又是非常复杂的，所以研究EF Core的源代码是非常耗时的。研究过程中，我几次都想要放弃，最后终于把功能实现了，通过开发这个库，我也对于EF Core的内部原理，特别是从Lambda表达式到SQL的翻译的整个过程了解的非常透彻。我这里不对研究的过程去回顾，而是直接为大家讲解一下EF Core的原理，然后再讲解一下我这个Zack.EFCore.Batch的实现原理。

1.  EF Core的SQL翻译原理

EF Core中有很多的服务，比如对于IQueryable进行预处理的QueryTranslationPreprocessor、从查询中提取查询参数的RelationalParameterBasedSqlProcessor、把表达式树翻译为SQL语句的QuerySqlGenerator等。这些服务一般都是通过IXXX Factory这样的工厂类的Create()方法创建的，比如QueryTranslationPreprocessor对应的IQueryTranslationPreprocessorFactory、QuerySqlGenerator对应的IQuerySqlGeneratorFactory。而这些工厂类的对象则是通过dbContext.GetService()来从DbContext中获得的。当然，也有的服务是不需要通过工厂直接获得的，比如Lambda编译器服务IQueryCompiler就可以直接通过ctx.GetService()获取。

因此，如果你想使用EF Core中其他的服务，都可以尝试把对应的服务接口类型或者工厂类型放到GetService()中查询一下试试。

EF Core中还允许调用DbContextOptionsBuilder的ReplaceService()方法把EF Core中的默认服务替换为自定义实现类。

EF Core中把一个IQueryable对象翻译为SQL语句的代码分散在各个类中，我经过努力，把它们整合为一段可以运行的代码，如下：Expression query = queryable.Expression;

var databaseDependencies = ctx.GetService();

IQueryTranslationPreprocessorFactory _queryTranslationPreprocessorFactory = ctx.GetService();

IQueryableMethodTranslatingExpressionVisitorFactory _queryableMethodTranslatingExpressionVisitorFactory = ctx.GetService();

IQueryTranslationPostprocessorFactory _queryTranslationPostprocessorFactory = ctx.GetService();

QueryCompilationContext queryCompilationContext = databaseDependencies.QueryCompilationContextFactory.Create(true);

IDiagnosticsLogger logger = ctx.GetService>();

QueryContext queryContext = ctx.GetService().Create();

QueryCompiler queryComipler = ctx.GetService() as QueryCompiler;

//parameterize determines if it will use "Declare" or not

MethodCallExpression methodCallExpr1 = queryComipler.ExtractParameters(query, queryContext, logger, parameterize: true) as MethodCallExpression;

QueryTranslationPreprocessor queryTranslationPreprocessor = _queryTranslationPreprocessorFactory.Create(queryCompilationContext);

MethodCallExpression methodCallExpr2 = queryTranslationPreprocessor.Process(methodCallExpr1) as MethodCallExpression;

QueryableMethodTranslatingExpressionVisitor queryableMethodTranslatingExpressionVisitor =

_queryableMethodTranslatingExpressionVisitorFactory.Create(queryCompilationContext);

ShapedQueryExpression shapedQueryExpression1 = queryableMethodTranslatingExpressionVisitor.Visit(methodCallExpr2) as ShapedQueryExpression;

QueryTranslationPostprocessor queryTranslationPostprocessor= _queryTranslationPostprocessorFactory.Create(queryCompilationContext);

ShapedQueryExpression shapedQueryExpression2 = queryTranslationPostprocessor.Process(shapedQueryExpression1) as ShapedQueryExpression;

IRelationalParameterBasedSqlProcessorFactory _relationalParameterBasedSqlProcessorFactory =

ctx.GetService();

RelationalParameterBasedSqlProcessor _relationalParameterBasedSqlProcessor = _relationalParameterBasedSqlProcessorFactory.Create(true);

SelectExpression selectExpression = (SelectExpression)shapedQueryExpression2.QueryExpression;

selectExpression = _relationalParameterBasedSqlProcessor.Optimize(selectExpression, queryContext.ParameterValues, out bool canCache);

IQuerySqlGeneratorFactory querySqlGeneratorFactory = ctx.GetService();

QuerySqlGenerator querySqlGenerator = querySqlGeneratorFactory.Create();

var cmd = querySqlGenerator.GetCommand(selectExpression);

string sql = cmd.CommandText;

大致解释一下上面的代码：

queryable是一个待转换的IQueryable对象，ctx是一个DbContext对象。QueryCompilationContext是Lambda到SQL翻译这个“编译”过程的上下文，很多工厂类的Create方法都要用它做参数。QueryContext是查询语句的上下文。SelectExpression是Linq查询的表达式树翻译为强类型的抽象语法树的树根。QuerySqlGenerator的GetCommand()方法用于遍历SelectExpression生成目标SQL语句。

QuerySqlGenerator的GetCommand方法最终会调用VisitSelect(SelectExpression selectExpression)来拼接生成SQL语句，其中会调用VisitSqlBinary(SqlBinaryExpression sqlBinaryExpression)、VisitFromSql(FromSqlExpression fromSqlExpression)、VisitLike(LikeExpression likeExpression)等方法来把运算表达式、From、Like等翻译成对应的SQL片段。由于不同DBMS中一些函数等实现不同，而SelectExpression、LikeExpression等都是一个抽象节点，是独立于具体DBMS的抽象模型，因此各个DBMS的EF Provider只要负责编写代码把这些XXExpression翻译为各自的SQL片段即可，不同DBMS的EF Core中的代码大部分都是各种XXTranslatorProvider。

2.  Zack.EFCore.Batch的实现原理

这个库最核心的代码就是ZackQuerySqlGenerator，它是一个继承自QuerySqlGenerator的类。它通过override父类的VisitSelect方法，然后把父类的VisitSelect方法的代码全部拷过来。这样的目的就是在VisitSelect拼接SQL语句的过程中把各个SQL片段截获到。以下面的代码为例：if (selectExpression.Predicate != null)

{

Sql.AppendLine().Append("WHERE ");

var oldSQL = Sql.Build().CommandText;//zack's code

Visit(selectExpression.Predicate);

this.PredicateSQL = Diff(oldSQL, this.Sql.Build().CommandText); //zack's code

}

这里就是首先把拼接Where条件之前的SQL语句保存到oldSQL变量中，再把拼接Where条件之后的SQL语句和oldSQL求一个差运算，就得到了Where语句的SQL片段。

然后通过optBuilder.ReplaceService();把ZackQuerySqlGenerator对应的ZackQuerySqlGeneratorFactory替换为IQuerySqlGeneratorFactory的默认实现。这样EF Core再完成从SelectExpression到SQL语句的翻译，就会使用ZackQuerySqlGenerator类，这样我们就可以截获翻译生成的SQL片段了。

再解释一下批量更新数据库的BatchUpdateBuilder类的主要代码。代码主要就是把Age=Age+1, Name=AuthorName.Trim()这样的赋值表达式重新生成Select(new{b.Age,b.Age+1,b.Name,b.AuthorName.Trime()})这样的表达式，这样就把N个赋值表达式重新拼接为2*N个查询表达式，再把查询条件拼接形成一个IQueryable对象，再调用ZackQuerySqlGenerator翻译IQueryable获取到Where的SQL片段以及各个列的SQL片段，最后重新拼接成一个Update的SQL语句。

六、局限性

Zack.EFCore.Batch有如下局限性：

1.     由于Zack.EFCore.Batch用到了EF Core 5.0的新API，所以暂不支持EF Core 3.X及以下版本。

2.     由于Zack.EFCore.Batch是直接操作数据库，所以更新、删除后，会存在微软担心的同一个DbContext中已经查询出来的对象跟踪状态和数据库不一致的情况。在同一个DbContext实例中，如果需要在批量删除或者更新之后操作同一个DbContex中之前查询出来的数据，建议再执行一遍查询操作。

3.     代码中使用了一个内部API QueryCompiler，这是不推荐的做法。