欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

使用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()); 
                })

有其它场景时,也可以优化上面的正则替换方法,达到替换执行语句的效果

大家如果有其它办法处理这类问题,也麻烦分享一下