LINQ模糊查询来实现复合搜索的功能,具体的操作过程是什么呢?在这里向你介绍一下处理过程,使用LINQ模糊查询有什么需要注意的呢?那么我们通过这个例子希望对你有所启发。
LINQ模糊查询实现的多条件复合搜索效果如下图:
LINQ模糊查询实现阶段一:
首先是找到了李永京(YJingLee)前辈的《LINQ体验(17)——LINQ to SQL语句之动态查询》一文,利用Lambda表达式树可以进行动态查询。写了个方法进行复合查询,动态组合条件,生成Lambda表达式。
- /// <summary>
- /// 这个方法带分页功能,通过输入的键值对NVC进行复合查询
- /// </summary>
- List<UserT_TractInfo> GetPagedObjectsByNVC(
- int startIndex, int pageSize,
- NameValueCollection nvc, bool isAnd)
- {
- IQueryable<UserT_TractInfo> query =
- Consulting.Instance.UserT_TractInfo;
- query.Where(t => t.IsDel == 0).
- Where(t => t.IsAuditing == 1);//审核和逻辑删除
- Expression condition = null;
- ParameterExpression param = Expression.
- Parameter(typeof(UserT_TractInfo), "c");
- int propertyCount = 0;
- foreach (string key in nvc)
- {
- Expression right = Expression.Constant(nvc[key]);//键
- string keyProperty = key;//属性
- if (typeof(UserT_TractInfo).GetProperty(keyProperty) != null)
- //当对象存在此属性时(因为键值对可能还有很多其他的参数,例如page)
- {
- Expression left = Expression.Property(param,
- typeof(UserT_TractInfo).GetProperty(keyProperty));//建立属性
- Expression filter = Expression.Equal(left, right);//过滤器
- if (condition == null)
- {
- condition = filter;
- }
- else
- {
- if (isAnd)
- {
- condition = Expression.And(condition, filter);
- }
- else
- {
- condition = Expression.Or(condition, filter);
- }
- }
- propertyCount++;
- }
- }
- //以上foreach组合了各个有效的键值对对应的conditionExpression,
- //复合查询最重要的组合工作就这么完了
- if (propertyCount > 0)
- {
- Expression pred = Expression.Lambda(condition, param);
- MethodCallExpression whereCallExpression =
- Expression.Call(typeof(Queryable), "Where",
- new Type[] { typeof(UserT_TractInfo) },
- Expression.Constant(query), pred);
- return Consulting.Instance.UserT_TractInfo.AsQueryable().
- Provider.CreateQuery<UserT_TractInfo>(whereCallExpression).
- OrderByDescending(t => t.ID).Skip(startIndex - 1).
- Take(pageSize).ToList();//查询出结果
- }
- else
- {
- return Consulting.Instance.UserT_TractInfo.
- OrderByDescending(t => t.ID).Skip(startIndex - 1).
- Take(pageSize).ToList();
- //如果没有有效键值对,则返回全部结果
- }
- }
搞了半天本来很兴奋的,之后才知道Lambda表达式是写不出.Contains()的,我的心瓦凉瓦凉的。
LINQ模糊查询实现阶段二:
虽然李永京的文章没给我多少帮助,但它后面有个回复很有价值:“用微软提供的System.Linq.Dynamic方便点。”很快找到了对应例子和Dynamic.cs,也找到了《Linq to SQL Dynamic 动态查询》,有更细致的例子,可惜Dynamic.cs也是不能使用like的,恨啊!
- return Consulting.Instance.UserT_TractInfo.Where(
- "b_number == @0","P(2007)031").OrderByDescending(t => t.ID).
- Skip(startIndex - 1).Take(pageSize).ToList();
代码很容易,但没什么用:(
LINQ模糊查询实现阶段三:
中文的实在是找不到了,在MS的官方BBS上找到了个链接,非常有用!《dynamic linq queries / dynamic where clause (part 2) 》,这个老外扩展了Dynamic.cs,写了个PredicateExtensions类,虽然不知道他是怎么想出来的,但确实有效!
这里放出核心代码,很容易看懂,简单就是美!
- searchPredicate = PredicateExtensions.
- True<UserT_TractInfo>();
- foreach (string key in nvcParam)
- {
- string condition = string.Empty;
- switch (key)
- {
- case "b_number":
- condition = nvcParam[key];
- searchPredicate = searchPredicate.And(u =>
- u.B_number.Contains(condition));
- break;
- case "b_address":
- condition = nvcParam[key];
- searchPredicate = searchPredicate.And(u =>
- u.B_address.Contains(condition));
- break;
- case "b_canton":
- condition = nvcParam[key];
- searchPredicate = searchPredicate.And(u =>
- u.B_canton.Contains(condition));
- break;
- case "a_status":
- condition = nvcParam[key];
- searchPredicate = searchPredicate.And(u =>
- u.A_status.ToString().Contains(condition));
- break;
- case "b_area":
- condition = nvcParam[key];
- searchPredicate = searchPredicate.And(u =>
- u.B_area.Contains(condition));
- break;
- case "c_clinchdate":
- condition = nvcParam[key];
- searchPredicate = searchPredicate.And(u =>
- u.C_clinchdate.Contains(condition));
- break;
- default:
- break;
- }
- }
- return Consulting.Instance.UserT_TractInfo.
- Where(searchPredicate).OrderByDescending(t => t.ID).
- Skip(startIndex - 1).Take(pageSize).ToList();
下面是我写了注释后的PredicateExtensions,我说不清楚构造函数的True和False具体是怎么起作用的,但结果就是我的注释那样,在复合查询写条件时很重要(不过目前全写AND就完成复合查询了,我还没搞多关键词OR的那种):
- /// <summary>
- /// 构造函数使用True时:单个AND有效,多个AND有效;
- ///单个OR无效,多个OR无效;混合时写在AND后的OR有效
- /// 构造函数使用False时:单个AND无效,多个AND无效;
- ///单个OR有效,多个OR有效;混合时写在OR后面的AND有效
- /// </summary>
- public static class PredicateExtensions
- {
- public static Expression<Func<T,
- bool>> True<T>() { return f => true; }
- public static Expression<Func<T, bool>> False<T>() {
- return f => false; }
- public static Expression<Func<T, bool>>
- Or<T>(this Expression<Func<T, bool>> expression1,
- Expression<Func<T, bool>> expression2)
- {
- var invokedExpression = Expression.Invoke(
- expression2, expression1.Parameters.Cast<Expression>());
- return Expression.Lambda<Func<T, bool>>(
- Expression.Or(expression1.Body,
- invokedExpression),
- expression1.Parameters);
- }
- public static Expression<Func<T, bool>> And<T>(
- this Expression<Func<T, bool>> expression1,
- Expression<Func<T, bool>> expression2)
- {
- var invokedExpression =
- Expression.Invoke(expression2,
- expression1.Parameters.Cast<Expression>());
- return Expression.Lambda<Func<T, bool>>
- (Expression.And(expression1.Body, invokedExpression),
- expression1.Parameters);
- }
- }
原文来自:http://www.cnblogs.com/killuakun/archive/2008/08/03/1259389.html
LINQ模糊查询实现复合搜索的基本内容就向你介绍到这里,希望对你了解和掌握复合搜索的LINQ模糊查询实现有所帮助。
【编辑推荐】