110,538
社区成员
发帖
与我相关
我的任务
分享
//常规的使用方式:存在的问题是,使用Contains()查询的时候,发现查询字符串越短越不精确,会查出不应该有的记录
var query = context.Was_Ba_Talk.AsQueryable();
string search = tbSearch.Text;
if (!search.IsNullOrEmpty())
{
query = query.Where(c => c.Titile.Contains(search) || c.Content.Contains(search));
}
this.gridTalk.DataSource = query.OrderBy(c => c.SortNo).ToList(); //Sqlite不支持query直接OrderBy
DbSqlQuery<Was_Ba_Talk> query;
string sql = @"select * from Was_Ba_Talk";
string search = tbSearch.Text;
if (!search.IsNullOrEmpty())
{
sql += " where Titile like @p0 or Content like @p0";
query = context.Was_Ba_Talk.SqlQuery(sql, "%" + search + "%");
}
else
{
query = context.Was_Ba_Talk.SqlQuery(sql);
}
this.gridTalk.DataSource = query.OrderBy(c => c.SortNo).ToList(); //Sqlite不支持query直接OrderBy
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;
}
}
public partial class Local2Entities : DbContext
{
public Local2Entities()
: base(Functions.GetConnectionString_Local2())
{
DbInterception.Add(new SqliteInterceptor());
}
public ICommand SearchLocalErrorLogCommand
{
get
{
return new DelegateCommand<Button>((button) =>
{
Functions.ButtonZoomStoryboard(button, (sender, e) =>
{
try
{
ClearErrorInfo();
using (Local2Entities localEntity2 = new Local2Entities())
{
var q = from d in localEntity2.LocalErrorLog.AsNoTracking()
select d;
if (!string.IsNullOrEmpty(SearchLocalErrorLogErrorInfo))
{
q = q.Where(p => p.ErrorInfo.Contains(SearchLocalErrorLogErrorInfo));
}
SearchLocalErrorLog = (from d in q
orderby d.CreateTime descending
select d).ToList().Take(5000).ToList();
SearchLocalErrorLogTotalRecordNumber = SearchLocalErrorLog.Count();
}
}
catch (Exception ex)
{
Functions.PlayErrorSound();
Functions.Speak("发生错误");
ShowAndLogErrorMessage(ex.Message, ex);
return;
}
});
});
}
}
using (Local2Entities localEntity2 = new Local2Entities())
{
var q=localEntity2.Database.SqlQuery<LocalErrorLog>("select * from LocalErrorLog where ErrorInfo like ?", "%"+SearchLocalErrorLogErrorInfo+ "%");
SearchLocalErrorLog = q.ToList();
SearchLocalErrorLogTotalRecordNumber = SearchLocalErrorLog.Count();
}
/// <summary>
/// 返回字符或者字符串在另一个字符串中的起始位置
/// charindex(findString, origalString)
/// </summary>
[SQLiteFunction(Name = "CharIndex", FuncType = FunctionType.Scalar)]
public class FunctionCharIndexCn : SQLiteFunction
{
public override object Invoke(object[] args)
{
object arg1 = args[0];
object arg2 = args[1];
if (arg1 == null || arg2 == null) return -1; //没找到
string find = arg1.ToString();
string original = arg2.ToString();
return original.IndexOf(find);
}
}
public class EFDbFunctions
{
/// <summary>
/// 解决Sqlite中的CharIndex判断中文的bug,使用此自定义函数替换
/// </summary>
/// <param name="find"></param>
/// <param name="original"></param>
/// <returns></returns>
[DbFunctionAttribute("CodeFirstDatabaseSchema", "CharIndexCn")]
public static int CharIndexCn(string find, string original)
{
throw new NotSupportedException();
}
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Configurations.Add(new Was_Ba_OptionMap());
modelBuilder.Configurations.Add(new Was_Ba_TasksMap());
modelBuilder.Configurations.Add(new Was_Ba_WorkLogMap());
modelBuilder.Configurations.Add(new Was_Ba_TalkMap());
//加载自定义函数
modelBuilder.Conventions.Add(new FunctionsConvention("", typeof(EFDbFunctions)));
}
using (var context = new AppDbContext())
{
var query = context.Was_Ba_Talk.AsQueryable();
string search = tbSearch.Text;
if (!search.IsNullOrEmpty())
{
//query = query.Where(c => c.Title.Contains(search) || c.Content.Contains(search)); //对中文判断有问题
query = query.Where(c => EFDbFunctions.CharIndexCn(search, c.Title) > 0 || EFDbFunctions.CharIndexCn(search, c.Content) > 0);
}
this.gridTalk.DataSource = query.OrderBy(c => c.SortNo).ToList();
}
SELECT
[Extent1].[ListId] AS [ListId],
[Extent1].[Titile] AS [Titile],
[Extent1].[Content] AS [Content],
[Extent1].[SortNo] AS [SortNo],
[Extent1].[Status] AS [Status]
FROM [Was_Ba_Talk] AS [Extent1]
WHERE (((CHARINDEX(@p__linq__0, [Extent1].[Titile])) - 1) > 0) OR ((CHARINDEX(@p__linq__1, [Extent1].[Content])) > 0)