使用IDbCommandInterceptor解决EF-CORE-3.x-使用MYSQL时,未正常的生成LIKE查询语句
程序员文章站
2022-04-29 23:12:15
使用EF Core 3.x+Mysql 时,如下EF表达式 生成的语句: SELECT .`id`, .`account`, ... FROM AS WHERE LOCATE(CONVERT( 'test' USING UTF8MB4) COLLATE utf8mb4_bin, .`account` ......
使用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()); })
有其它场景时,也可以优化上面的正则替换方法,达到替换执行语句的效果
大家如果有其它办法处理这类问题,也麻烦分享一下