本文介紹了一個在Entity Framework Core 5中不需要預(yù)先加載數(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）顯然是被關(guān)注最多的潘懊。EF Core非常優(yōu)秀而且功能豐富姚糊，但是EF Core有一個一直被人詬病的地方就是它并不能很好支持?jǐn)?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();

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

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

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

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

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

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

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

根據(jù)github上那個issue的回復(fù)奏纪，微軟有考慮在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強(qiáng)類型的特性，如果模型發(fā)生改變牛哺，必須手動變更SQL語句陋气；而且如果調(diào)用了一些DBMS特有的語法、函數(shù)引润，一旦程序遷移到其他DBMS巩趁，就可能要重新編寫SQL語句，而無法利用EF Core強(qiáng)大的SQL翻譯機(jī)制來屏蔽不同底層數(shù)據(jù)庫的差異淳附。

2.使用其他ORM议慰。FreeSQL等ORM中提供了批量Delete、Update語句的方法奴曙，使用也非常簡單别凹。這種方式的缺點是項目中必須引入第三方的ORM，無法復(fù)用EF Core的代碼洽糟。

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

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

我開發(fā)了一個Entity Framework Core的擴(kuò)展庫抠蚣，讓開發(fā)者在Entity Framework Core中可以用一句SQL進(jìn)行數(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的擴(kuò)展方法DeleteRangeAsync()來刪除一批數(shù)據(jù). DeleteRangeAsync()的參數(shù)就是過濾條件的lambda表達(dá)式吻谋。

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

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的擴(kuò)展方法BatchUpdate()來創(chuàng)建一個BatchUpdateBuilder對象现横。 BatchUpdateBuilder類有如下四個方法：

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

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表達(dá)式到SQL語句的翻譯骇两，所以幾乎所有EF Core支持的lambda表達(dá)式寫法都被支持。

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

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

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

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

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

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

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

雖然EF Core是開源的猪半，不過由于關(guān)于EF Core的源代碼并沒有一個全面介紹的文檔兔朦，而EF Core的代碼又是非常復(fù)雜的，所以研究EF Core的源代碼是非常耗時的磨确。研究過程中沽甥，我?guī)状味枷胍艞墸詈蠼K于把功能實現(xiàn)了乏奥，通過開發(fā)這個庫摆舟，我也對于EF Core的內(nèi)部原理，特別是從Lambda表達(dá)式到SQL的翻譯的整個過程了解的非常透徹邓了。我這里不對研究的過程去回顧恨诱，而是直接為大家講解一下EF

Core的原理，然后再講解一下我這個Zack.EFCore.Batch的實現(xiàn)原理骗炉。

1.? EF Core的SQL翻譯原理

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

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

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

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

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

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查詢的表達(dá)式樹翻譯為強(qiáng)類型的抽象語法樹的樹根颓影。QuerySqlGenerator的GetCommand()方法用于遍歷SelectExpression生成目標(biāo)SQL語句各淀。

QuerySqlGenerator的GetCommand方法最終會調(diào)用VisitSelect(SelectExpression

selectExpression)來拼接生成SQL語句，其中會調(diào)用VisitSqlBinary(SqlBinaryExpression sqlBinaryExpression)诡挂、VisitFromSql(FromSqlExpression fromSqlExpression)碎浇、VisitLike(LikeExpression likeExpression)等方法來把運(yùn)算表達(dá)式、From璃俗、Like等翻譯成對應(yīng)的SQL片段奴璃。由于不同DBMS中一些函數(shù)等實現(xiàn)不同，而SelectExpression城豁、LikeExpression等都是一個抽象節(jié)點苟穆，是獨立于具體DBMS的抽象模型，因此各個DBMS的EF Provider只要負(fù)責(zé)編寫代碼把這些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");

?????? varoldSQL = 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求一個差運(yùn)算，就得到了Where語句的SQL片段叹话。

然后通過optBuilder.ReplaceService<IQuerySqlGeneratorFactory,

ZackQuerySqlGeneratorFactory>();把ZackQuerySqlGenerator對應(yīng)的ZackQuerySqlGeneratorFactory替換為IQuerySqlGeneratorFactory的默認(rèn)實現(xiàn)偷遗。這樣EF Core再完成從SelectExpression到SQL語句的翻譯，就會使用ZackQuerySqlGenerator類驼壶，這樣我們就可以截獲翻譯生成的SQL片段了氏豌。

再解釋一下批量更新數(shù)據(jù)庫的BatchUpdateBuilder類的主要代碼。代碼主要就是把Age=Age+1,

Name=AuthorName.Trim()這樣的賦值表達(dá)式重新生成Select(new{b.Age,b.Age+1,b.Name,b.AuthorName.Trime()})這樣的表達(dá)式热凹，這樣就把N個賦值表達(dá)式重新拼接為2*N個查詢表達(dá)式泵喘，再把查詢條件拼接形成一個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ù)庫，所以更新、刪除后芜繁，會存在微軟擔(dān)心的同一個DbContext中已經(jīng)查詢出來的對象跟蹤狀態(tài)和數(shù)據(jù)庫不一致的情況旺隙。在同一個DbContext實例中，如果需要在批量刪除或者更新之后操作同一個DbContex中之前查詢出來的數(shù)據(jù)骏令，建議再執(zhí)行一遍查詢操作蔬捷。

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