本文介紹了一個在Entity Framework Core 5中不需要預先加載數(shù)據(jù)而使用一句SQL語句批量更新、刪除數(shù)據(jù)的開發(fā)包，并且分析了其實現(xiàn)原理，并且與其他實現(xiàn)方案做了比較。

一、背景

隨著微軟全面擁抱開源，.Net開源社區(qū)百花開放，涌現(xiàn)了非常多優(yōu)秀的開源，ORM項目就有Dapper、SqlSugar、PetaPoco、FreeSQL等。作為微軟官方提供的ORM框架，Entity Framework Core（以下簡稱EF Core）顯然是被關注最多的。EF Core非常優(yōu)秀而且功能豐富，但是EF Core有一個一直被人詬病的地方就是它并不能很好支持數(shù)據(jù)的批量更新和批量刪除。在EF Core中批量更新和刪除數(shù)據(jù)都要先把數(shù)據(jù)加載到內(nèi)存中，然后再對數(shù)據(jù)操作，最后再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();

?讓我們查看上面的程序幕后執(zhí)行的SQL語句：

?

可以看到，EF Core先把數(shù)據(jù)用Select查詢出來，然后在內(nèi)存中逐個修改，最后再把被修改對象每個都執(zhí)行一次Update語句去更新。

再比如，如下的代碼用于刪除Price大于5元的記錄：

var books1 = ctx.Books.Where(b => b.Price > 5);

ctx.RemoveRange(books1);

ctx.SaveChanges();

?讓我們查看上面的程序運行幕后執(zhí)行的SQL語句：

?

?

可以看到，EF Core先把數(shù)據(jù)用Select查詢出來，然后再對每條記錄都執(zhí)行Delete語句去刪除。

很顯然，如果批量更新或者刪除的數(shù)據(jù)量比較大，這樣的操作性能是非常低的。

因此，我們需要一種在EF Core中使用一條SQL語句就高性能地刪除或者更新數(shù)據(jù)的方法。

?

二、為什么微軟不提供這樣的方法

盡管用戶的要求強烈，但是微軟一直沒有提供高效的批量刪除和更新的方式。在EF Core Github的issue中?[1]，微軟給出的理由是：這樣做會導致EF Core的對象狀態(tài)跟蹤混亂，比如對于同一個DbContext，如果用批量刪除的方法刪除了數(shù)據(jù)，那么在被刪除之前查詢出來的數(shù)據(jù)狀態(tài)就混亂了，因此需要重構EF Core的代碼，工作量比較大。

作為一個成熟的框架，考慮這些邏輯問題以避免潛在的風險是有必要的，是可以理解的。但是作為實際的開發(fā)者，我們是有辦法規(guī)避這些問題的。比如一般的Web應用中，刪除操作都是在一個單獨的Http請求進行中的，因此不涉及到微軟擔心的問題。即使在有的場景下，涉及到在通過同一個DbContext在數(shù)據(jù)刪除之前就把數(shù)據(jù)查詢出來的場景，那么也完全可以通過在刪除之后再查一次的方式來規(guī)避這個問題。

根據(jù)github上那個issue的回復，微軟有考慮在EF Core 6.0中加入高效地批量刪除和更新數(shù)據(jù)的方式，但是僅僅是“考慮”，并不確定。我們作為普通開發(fā)者可等不及了，因此要自己去解決。

三、已有解決方法

有如下三種已有的解決方法：

1.???? 執(zhí)行原生SQL語句。在EF Core中提供了ctx.Database.ExecuteSqlRaw()等方法可以用來執(zhí)行原生SQL語句，因此我們可以直接編寫Delete、Update語句來刪除或者更新數(shù)據(jù)。這種方式比較直接，缺點就是這樣代碼中直接操作數(shù)據(jù)表的方式不太符合模型驅(qū)動、分層隔離等思想，程序員直接面對數(shù)據(jù)庫表，無法利用EF Core強類型的特性，如果模型發(fā)生改變，必須手動變更SQL語句；而且如果調(diào)用了一些DBMS特有的語法、函數(shù)，一旦程序遷移到其他DBMS，就可能要重新編寫SQL語句，而無法利用EF Core強大的SQL翻譯機制來屏蔽不同底層數(shù)據(jù)庫的差異。

2.???? 使用其他ORM。FreeSQL等ORM中提供了批量Delete、Update語句的方法，使用也非常簡單。這種方式的缺點是項目中必須引入第三方的ORM，無法復用EF Core的代碼。

3.???? 使用已有的EF Core擴展。EF Plus、EFCore.BulkExtensions等開源庫中都提供了在EF Core框架下進行批量操作的方法。實現(xiàn)這個的核心就是要獲得EF Core生成的SQL語句以及SelectExpression。由于EF Core 5.0之前的版本中沒有提供公開的API用于獲取一個LINQ操作對應的SQL語句，所以這些開源庫都是通過訪問EF Core框架中一些類的私有成員來完成的獲取LINQ對應的SQL語句以及SelectExpression的方法?[2]。由于用的是訪問私有成員這樣不符合面向?qū)ο笤瓌t的方式，所以一旦EF Core框架代碼發(fā)生改變，代碼就可能會失敗，之前就發(fā)生過EF Core新版本發(fā)布造成這些開源庫無法工作的情況。而且，在撰寫這篇文章的時候，這些開源庫還沒有適配.Net 5。

?

四、我的實現(xiàn)Zack.EFCore.Batch

我開發(fā)了一個Entity Framework Core的擴展庫，讓開發(fā)者在Entity Framework Core中可以用一句SQL進行數(shù)據(jù)的刪除或者更新。由于開發(fā)中用到了Entity Framework Core 5的API，所以這個庫要求Entity Framework Core 5及以上版本，也就是.Net 5及以上版本。

?

下面介紹一下使用方法：

第一步，通過Nuget安裝 Install-Package Zack.EFCore.Batch

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

optionsBuilder.UseBatchEF();

第三步: 使用DbContext的擴展方法DeleteRangeAsync()來刪除一批數(shù)據(jù). DeleteRangeAsync()的參數(shù)就是過濾條件的lambda表達式。

批量刪除的例子代碼如下:

?

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

?

上面的代碼將會在數(shù)據(jù)庫中執(zhí)行如下SQL語句：

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

?

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

使用DbContext的擴展方法BatchUpdate()來創(chuàng)建一個BatchUpdateBuilder對象。 BatchUpdateBuilder類有如下四個方法：

1)????? Set()方法用于給一個屬性賦值。方法的第一個參數(shù)是屬性的lambda表達式,第二個參數(shù)是值的lambda表達式。

2)????? Where() 是過濾條件

3)????? ExecuteAsync()使用用于執(zhí)行BatchUpdateBuilder的異步方法

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

?

例子代碼:

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

?

上面的代碼將會在SQLServer數(shù)據(jù)庫中執(zhí)行如下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%'))

?

這個開發(fā)包使用EF Core實現(xiàn)的lambda表達式到SQL語句的翻譯，所以幾乎所有EF Core支持的lambda表達式寫法都被支持。

?

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

五、實現(xiàn)原理分析

其實要把lambda表達式轉(zhuǎn)換為SQL語句并不難，只要對表達式樹進行解析就可以生成SQL語句，但是最難的部分是對于.Net函數(shù)到SQL片段的翻譯，因為相同的.Net函數(shù)在不同DBMS中等效的SQL片段是不同的，如果我自己實現(xiàn)這個是很麻煩的，因此我想到了直接借用EF Core的表達式樹到SQL語句的翻譯引擎來實現(xiàn)是最佳的方法。

不幸的是，在.Net Core 3.x及之前，是無法直接獲取一個Linq查詢翻譯后的SQL語句的。.Net Core中可以通過日志等方式獲取翻譯后的SQL語句，但是這些都是Linq執(zhí)行后才能獲得的，而且是無法在拿到一個Lambda表達式或者IQueryable的時候立即獲得SQL的。經(jīng)過詢問.Net Core開發(fā)團隊得知，在.Net Core 3.X及之前，也是沒有公開的API可以完成表達式樹到SQL片段翻譯的功能。

?從.Net 5開始，Entity Framework Core 中提供了不用執(zhí)行查詢，就可以直接獲取Linq查詢對應的SQL語句的方法，那就是調(diào)用IQueryable的ToQueryString()方法?[3]。

?因此我就想通過這個ToQueryString()方法拿到的SQL語句來入手來實現(xiàn)這個功能。 可以把用到的Lambda表達式片段、過濾表達式拼接到一個查詢表達式中，然后調(diào)用ToQueryString()方法獲取翻譯后的SQL語句，然后編寫詞法分析器和語法分析器對SQL語句進行分析，提取出Where子句以及Select列中的表達式片段，然后再把這些片段重新組合成Update、Delete的SQL語句即可。

不過，由于不同DBMS的語法不同，編寫這樣的詞法及語法分析器是很麻煩的，我就想能否研究ToQueryString()的實現(xiàn)原理，然后直接拿到解析過程中的SQL片段，這樣就避免了生成SQL后再去解析的工作。

雖然EF Core是開源的，不過由于關于EF Core的源代碼并沒有一個全面介紹的文檔，而EF Core的代碼又是非常復雜的，所以研究EF Core的源代碼是非常耗時的。研究過程中，我?guī)状味枷胍艞?，最后終于把功能實現(xiàn)了，通過開發(fā)這個庫，我也對于EF Core的內(nèi)部原理，特別是從Lambda表達式到SQL的翻譯的整個過程了解的非常透徹。我這里不對研究的過程去回顧，而是直接為大家講解一下EF Core的原理，然后再講解一下我這個Zack.EFCore.Batch的實現(xiàn)原理。

1.? EF Core的SQL翻譯原理

EF Core中有很多的服務，比如對于IQueryable進行預處理的QueryTranslationPreprocessor、從查詢中提取查詢參數(shù)的RelationalParameterBasedSqlProcessor、把表達式樹翻譯為SQL語句的QuerySqlGenerator等。這些服務一般都是通過IXXX Factory這樣的工廠類的Create()方法創(chuàng)建的，比如QueryTranslationPreprocessor對應的IQueryTranslationPreprocessorFactory、QuerySqlGenerator對應的IQuerySqlGeneratorFactory。而這些工廠類的對象則是通過dbContext.GetService<XXX>()來從DbContext中獲得的。當然，也有的服務是不需要通過工廠直接獲得的，比如Lambda編譯器服務IQueryCompiler就可以直接通過ctx.GetService<IQueryCompiler>()獲取。

?因此，如果你想使用EF Core中其他的服務，都可以嘗試把對應的服務接口類型或者工廠類型放到GetService()中查詢一下試試。

EF Core中還允許調(diào)用DbContextOptionsBuilder的ReplaceService()方法把EF Core中的默認服務替換為自定義實現(xiàn)類。

?EF Core中把一個IQueryable對象翻譯為SQL語句的代碼分散在各個類中，我經(jīng)過努力，把它們整合為一段可以運行的代碼，如下：

?Expression query = queryable.Expression;

var databaseDependencies = ctx.GetService<DatabaseDependencies>();

IQueryTranslationPreprocessorFactory _queryTranslationPreprocessorFactory = ctx.GetService<IQueryTranslationPreprocessorFactory>();

IQueryableMethodTranslatingExpressionVisitorFactory _queryableMethodTranslatingExpressionVisitorFactory = ctx.GetService<IQueryableMethodTranslatingExpressionVisitorFactory>();

IQueryTranslationPostprocessorFactory _queryTranslationPostprocessorFactory = ctx.GetService<IQueryTranslationPostprocessorFactory>();

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

?

IDiagnosticsLogger<DbLoggerCategory.Query> logger = ctx.GetService<IDiagnosticsLogger<DbLoggerCategory.Query>>();

QueryContext queryContext = ctx.GetService<IQueryContextFactory>().Create();

QueryCompiler queryComipler = ctx.GetService<IQueryCompiler>() 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<IRelationalParameterBasedSqlProcessorFactory>();

RelationalParameterBasedSqlProcessor _relationalParameterBasedSqlProcessor = _relationalParameterBasedSqlProcessorFactory.Create(true);

?

SelectExpression selectExpression = (SelectExpression)shapedQueryExpression2.QueryExpression;

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

IQuerySqlGeneratorFactory querySqlGeneratorFactory = ctx.GetService<IQuerySqlGeneratorFactory>();

QuerySqlGenerator querySqlGenerator = querySqlGeneratorFactory.Create();

var cmd = querySqlGenerator.GetCommand(selectExpression);

string sql = cmd.CommandText;

?

大致解釋一下上面的代碼：

queryable是一個待轉(zhuǎn)換的IQueryable對象，ctx是一個DbContext對象。QueryCompilationContext是Lambda到SQL翻譯這個“編譯”過程的上下文，很多工廠類的Create方法都要用它做參數(shù)。QueryContext是查詢語句的上下文。SelectExpression是Linq查詢的表達式樹翻譯為強類型的抽象語法樹的樹根。QuerySqlGenerator的GetCommand()方法用于遍歷SelectExpression生成目標SQL語句。

QuerySqlGenerator的GetCommand方法最終會調(diào)用VisitSelect(SelectExpression selectExpression)來拼接生成SQL語句，其中會調(diào)用VisitSqlBinary(SqlBinaryExpression sqlBinaryExpression)、VisitFromSql(FromSqlExpression fromSqlExpression)、VisitLike(LikeExpression likeExpression)等方法來把運算表達式、From、Like等翻譯成對應的SQL片段。由于不同DBMS中一些函數(shù)等實現(xiàn)不同，而SelectExpression、LikeExpression等都是一個抽象節(jié)點，是獨立于具體DBMS的抽象模型，因此各個DBMS的EF Provider只要負責編寫代碼把這些XXExpression翻譯為各自的SQL片段即可，不同DBMS的EF Core中的代碼大部分都是各種XXTranslatorProvider。

2.? Zack.EFCore.Batch的實現(xiàn)原理

這個庫最核心的代碼就是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<IQuerySqlGeneratorFactory, ZackQuerySqlGeneratorFactory>();把ZackQuerySqlGenerator對應的ZackQuerySqlGeneratorFactory替換為IQuerySqlGeneratorFactory的默認實現(xiàn)。這樣EF Core再完成從SelectExpression到SQL語句的翻譯，就會使用ZackQuerySqlGenerator類，這樣我們就可以截獲翻譯生成的SQL片段了。

?再解釋一下批量更新數(shù)據(jù)庫的BatchUpdateBuilder類的主要代碼。代碼主要就是把Age=Age+1, Name=AuthorName.Trim()這樣的賦值表達式重新生成Select(new{b.Age,b.Age+1,b.Name,b.AuthorName.Trime()})這樣的表達式，這樣就把N個賦值表達式重新拼接為2*N個查詢表達式，再把查詢條件拼接形成一個IQueryable對象，再調(diào)用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是直接操作數(shù)據(jù)庫，所以更新、刪除后，會存在微軟擔心的同一個DbContext中已經(jīng)查詢出來的對象跟蹤狀態(tài)和數(shù)據(jù)庫不一致的情況。在同一個DbContext實例中，如果需要在批量刪除或者更新之后操作同一個DbContex中之前查詢出來的數(shù)據(jù)，建議再執(zhí)行一遍查詢操作。

3.???? 代碼中使用了一個內(nèi)部API QueryCompiler，這是不推薦的做法。