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

74 阅读 0 评论 49 点赞

我是靠谱客的博主欣喜小天鹅，最近开发中收集的这篇文章主要介绍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;
}
}

参考网址：

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