使用EF Core 3.x+Mysql 時(shí)，如下EF表達(dá)式

var list=await ctx.Set<User>().where(v=>v.Account.Contains("test")).ToListAsync();

生成的語(yǔ)句：

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是數(shù)據(jù)庫(kù)函數(shù)操作，數(shù)量量大時(shí)，會(huì)非常慢.
解決辦法:攔截EF生成的語(yǔ)句，將上述函數(shù)操作，替換成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:注冊(cè)攔截器

                services 
                .AddDbContextPool<yearDbContext>(o =>
                {
                    o.UseMySql(Configuration.GetConnectionString("youdbConfigName"), mySqlOptions =>
                    {
                        mySqlOptions.ServerVersion(new Version(5, 6, 40), ServerType.MySql);
                    }).AddInterceptors(new FmtCommandInterceptor()); 
                })

有其它場(chǎng)景時(shí)，也可以優(yōu)化上面的正則替換方法，達(dá)到替換執(zhí)行語(yǔ)句的效果

大家如果有其它辦法處理這類問(wèn)題，也麻煩分享一下