admin

EF SQLite模糊查找生成Like语句的坑
问题在使用EF连接SQLite数据库的时候发现模糊查找查找不出来对于的内容,断点调试查看生成的SQL语句发现生成的...
扫描右侧二维码阅读全文
13
2019/02

EF SQLite模糊查找生成Like语句的坑

问题

在使用EF连接SQLite数据库的时候发现模糊查找查找不出来对于的内容,断点调试查看生成的SQL语句发现生成的SQL语句类似于:

(CHARINDEX(@p__linq__2, [Extent1].[LeagueName])) > 0)

CHARINDEX函数在SQLSERVER中是支持的,但是在SQLite中并不支持这个函数。

解决方案

实现一个自己的Interceptor来替换生成的SQL语句即可

public class SqliteInterceptor: IDbCommandInterceptor
{
    private static Regex replaceRegex = new Regex(@"\(\(CHARINDEX\((.*?), (.*?)\)\) > 0\)");

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        ReplaceCharIndexFunc(command);
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        ReplaceCharIndexFunc(command);
    }

    private void ReplaceCharIndexFunc(DbCommand command)
    {
        var flag = false;
        var text = replaceRegex.Replace(command.CommandText, m =>
                                        {
                                            if (!m.Success) return m.Value;
                                            flag = true;
                                            var key = m.Groups[1].Value;
                                            var name = m.Groups[2].Value;
                                            //替换参数
                                            foreach (DbParameter commandParameter in command.Parameters)
                                            {
                                                if (commandParameter.ParameterName == key.Substring(1))
                                                {
                                                    commandParameter.Value = $"%{commandParameter.Value}%";
                                                    break;
                                                }
                                            }
                                            return $"{name} LIKE {key}";
                                        });
        if (flag)
            command.CommandText = text;
    }
}

然后在你的DBContext的构造函数中,把这个类添加到EF中即可

public MyDb() : base("name=defaultConn")
{
    DbInterception.Add(new SqliteInterceptor());
}
Last modification:February 17th, 2019 at 02:20 pm
If you think my article is useful to you, please feel free to appreciate

Leave a Comment