使用EF Core 3.x+Mysql 時，如下EF運算式
var list=await ctx.Set<User>().where(v=>v.Account.Contains("test")).ToListAsync();
生成的陳述句：
SELECT
`b1`.`id`,
`b1`.`account`,
...
FROM
`basis_user` AS `b1`
WHERE
LOCATE(CONVERT( 'test' USING UTF8MB4) COLLATE utf8mb4_bin,
`b1`.`account`) > 0
其中 LOCATE(CONVERT( 'test' USING UTF8MB4) COLLATE utf8mb4_bin,`b1`.`account`) > 0是資料庫函式操作，數量量大時，會非常慢.
解決辦法:攔截EF生成的陳述句，將上述函式操作，替換成like
偽代碼如下：
1：定義攔截器
public class FmtCommandInterceptor : DbCommandInterceptor, IDbCommandInterceptor
{
public static readonly Regex Regex_Replace_MySql_Like
= new Regex(@"LOCATE\(CONVERT\('(?<v>.+?)' USING utf8mb4\) COLLATE utf8mb4_bin, (?<k>`.+`?)\) > 0",
RegexOptions.Compiled | RegexOptions.IgnoreCase);
public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
{
command.CommandText = Regex_Replace_MySql_Like.Replace(command.CommandText, " ${k} like '%${v}%' ");
return base.ReaderExecuting(command, eventData, result);
}
public override Task<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
{
command.CommandText = Regex_Replace_MySql_Like.Replace(command.CommandText, " ${k} like '%${v}%' ");
return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
}
}
2:注冊攔截器
services
.AddDbContextPool<yearDbContext>(o =>
{
o.UseMySql(Configuration.GetConnectionString("youdbConfigName"), mySqlOptions =>
{
mySqlOptions.ServerVersion(new Version(5, 6, 40), ServerType.MySql);
}).AddInterceptors(new FmtCommandInterceptor());
})
有其它場景時，也可以優化上面的正則替換方法，達到替換執行陳述句的效果
大家如果有其它辦法處理這類問題，也麻煩分享一下
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/42907.html
標籤：.NET Core