EF使用Contains从sqlite中查询出的结果不正确

OK,直入主题。

List<FoodInfo> foodInfos = FoodInfoDAL.GetAll(f => f.Name.Contains(name));
 public List<TEntity> GetAll(Expression<Func<TEntity, bool>> filter)
 {
     return Repository.Set<TEntity>().Where(filter).ToList();
 }

foodInfos 从sqlite查询出来的结果是错误的。

Why?
我们知道ef查询数据库也是用sql语句的，那表达式转成什么sql语句了呢？VS中监视看到了

SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[FoodDicID] AS [FoodDicID], 
[Extent1].[Name] AS [Name], 
[Extent1].[Code] AS [Code], 
[Extent1].[Genus] AS [Genus], 
[Extent1].[PYCode] AS [PYCode], 
[Extent1].[CreateTime] AS [CreateTime], 
[Extent1].[CreateDENumber] AS [CreateDENumber], 
[Extent1].[UpdateTime] AS [UpdateTime], 
[Extent1].[UpdateDENumber] AS [UpdateDENumber], 
[Extent1].[ClientGuid] AS [ClientGuid]
FROM [FoodInfo] AS [Extent1]
WHERE (CHARINDEX(@p__linq__0, [Extent1].[Name])) > 0

让我们看这个CHARINDEX函数，这是sql用来在一段字符中搜索字符或者字符串
但是sqlite中是没有此函数的，只有INSTR函数

So
我们可以通过拦截器把查询时CHARINDEX函数换成LIKE OR INSTR

public class SqliteInterceptor : IDbCommandInterceptor
    {
        private static Regex replaceRegex = new Regex(@"\(CHARINDEX\((.*?),\s?(.*?)\)\)\s*?>\s*?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)
        {
            bool isMatch = false;

            var text = replaceRegex.Replace(command.CommandText, (match) =>
            {
                if (match.Success)
                {
                    string paramsKey = match.Groups[1].Value;
                    string paramsColumnName = match.Groups[2].Value;
                    //replaceParams
                    foreach (DbParameter param in command.Parameters)
                    {
                        if (param.ParameterName == paramsKey.Substring(1))
                        {
                            param.Value = string.Format("%{0}%", param.Value);
                            break;
                        }
                    }
                    isMatch = true;
                    return string.Format("{0} LIKE {1}", paramsColumnName, paramsKey);
                }
                else
                    return match.Value;
            });
            if (isMatch)
                command.CommandText = text;
        }
    }

参考网址：

1. https://q.cnblogs.com/q/90934/
2. https://stackoverflow.com/questions/42192041/entity-framework-sqlite-contains-charindex-and-unicode
3. http://www.sqlitetutorial.net/sqlite-functions/sqlite-instr/
4. http://www.runoob.com/regexp/regexp-syntax.html
5. http://www.entityframeworktutorial.net/EntityFramework6/database-command-interception.aspx