EF多条件查询扩展

一-->分页查询封装  BaseRepository.cs

 

/// <summary>  
/// 带分页的查询   
/// </summary>  
/// <typeparam name="TKey"></typeparam>  
/// <param name="pageIndex">页码</param>  
/// <param name="pageSize">页容量</param>  
/// <param name="whereLambda">条件 lambda表达式</param>  
/// <param name="orderBy">排序 lambda表达式</param>  
/// <returns></returns>  
IQueryable<T> GetPagedList<TKey>(int pageIndex, int pageSize, Expression<Func<T, bool>> whereLambda, Expression<Func<T, TKey>> orderByLambda, bool isAsc = true);

 

 

 

前言假设：在前端页面，我们一般会遇到多条件查询，但是我们看到带分页的查询都有一个Expression<Func<T,bool>> whereLambda ，为了支持多条件查询，我们只能多这个Expression<Func<T,bool>> whereLambda 进行扩展，让她可以进行多条件的查询

 

二-->Expression<Func<T,bool>> 扩展：

 

 

using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
namespace System
{
    /// <summary>
    /// 谓词表达式构建器
    /// </summary>
    public static class ExpressionExtensions
    {
        /// <summary>
        /// 创建一个值恒为 <c>true</c> 的表达式。
        /// </summary>
        /// <typeparam name="T">表达式方法类型</typeparam>
        /// <returns>一个值恒为 <c>true</c> 的表达式。</returns>
        public static Expression<Func<T, bool>> True<T>() { return p => true; }

        /// <summary>
        /// 创建一个值恒为 <c>false</c> 的表达式。
        /// </summary>
        /// <typeparam name="T">表达式方法类型</typeparam>
        /// <returns>一个值恒为 <c>false</c> 的表达式。</returns>
        public static Expression<Func<T, bool>> False<T>() { return f => false; }

        /// <summary>
        /// 使用 Expression.OrElse 的方式拼接两个 System.Linq.Expression。
        /// </summary>
        /// <typeparam name="T">表达式方法类型</typeparam>
        /// <param name="left">左边的 System.Linq.Expression 。</param>
        /// <param name="right">右边的 System.Linq.Expression。</param>
        /// <returns>拼接完成的 System.Linq.Expression。</returns>
        public static Expression<T> Or<T>(this Expression<T> left, Expression<T> right)
        {
            return MakeBinary(left, right, Expression.OrElse);
        }

        /// <summary>
        /// 使用 Expression.AndAlso 的方式拼接两个 System.Linq.Expression。
        /// </summary>
        /// <typeparam name="T">表达式方法类型</typeparam>
        /// <param name="left">左边的 System.Linq.Expression 。</param>
        /// <param name="right">右边的 System.Linq.Expression。</param>
        /// <returns>拼接完成的 System.Linq.Expression。</returns>
        public static Expression<T> And<T>(this Expression<T> left, Expression<T> right)
        {
            return MakeBinary(left, right, Expression.AndAlso);
        }

        /// <summary>
        /// 使用自定义的方式拼接两个 System.Linq.Expression。
        /// </summary>
        /// <typeparam name="T">表达式方法类型</typeparam>
        /// <param name="left">左边的 System.Linq.Expression 。</param>
        /// <param name="right">右边的 System.Linq.Expression。</param>
        /// <param name="func"> </param>
        /// <returns>拼接完成的 System.Linq.Expression。</returns>
        private static Expression<T> MakeBinary<T>(this Expression<T> left, Expression<T> right, Func<Expression, Expression, Expression> func)
        {
            //Debug.Assert(func != null, "func != null");
            return MakeBinary((LambdaExpression)left, right, func) as Expression<T>;
        }

        /// <summary>
        /// 拼接两个 <paramref>
        /// <name>System.Linq.Expression</name>
        /// </paramref>  ，两个 <paramref>
        /// <name>System.Linq.Expression</name>
        /// </paramref>  的参数必须完全相同。
        /// </summary>
        /// <param name="left">左边的 <paramref>
        /// <name>System.Linq.Expression</name>
        /// </paramref> </param>
        /// <param name="right">右边的 <paramref>
        /// <name>System.Linq.Expression</name>
        /// </paramref> </param>
        /// <param name="func">表达式拼接的具体逻辑</param>
        /// <returns>拼接完成的 <paramref>
        /// <name>System.Linq.Expression</name>
        /// </paramref> </returns>
        private static LambdaExpression MakeBinary(this LambdaExpression left, LambdaExpression right, Func<Expression, Expression, Expression> func)
        {
            var data = Combinate(right.Parameters, left.Parameters).ToArray();
            right = ParameterReplace.Replace(right, data) as LambdaExpression;
            //Debug.Assert(right != null, "right != null");
            return Expression.Lambda(func(left.Body, right.Body), left.Parameters.ToArray());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="left"></param>
        /// <param name="right"></param>
        /// <returns></returns>
        private static IEnumerable<KeyValuePair<T, T>> Combinate<T>(IEnumerable<T> left, IEnumerable<T> right)
        {
            var a = left.GetEnumerator();
            var b = right.GetEnumerator();
            while (a.MoveNext() && b.MoveNext())
                yield return new KeyValuePair<T, T>(a.Current, b.Current);
        }
    }

    #region class: ParameterReplace
    internal sealed class ParameterReplace : ExpressionVisitor
    {
        public static Expression Replace(Expression e, IEnumerable<KeyValuePair<ParameterExpression, ParameterExpression>> paramList)
        {
            var item = new ParameterReplace(paramList);
            return item.Visit(e);
        }

        private readonly Dictionary<ParameterExpression, ParameterExpression> _parameters;

        public ParameterReplace(IEnumerable<KeyValuePair<ParameterExpression, ParameterExpression>> paramList)
        {
            _parameters = paramList.ToDictionary(p => p.Key, p => p.Value, new ParameterEquality());
        }

        protected override Expression VisitParameter(ParameterExpression p)
        {
            ParameterExpression result;
            if (_parameters.TryGetValue(p, out result))
                return result;
            return base.VisitParameter(p);
        }

        #region class: ParameterEquality
        private class ParameterEquality : IEqualityComparer<ParameterExpression>
        {
            public bool Equals(ParameterExpression x, ParameterExpression y)
            {
                if (x == null || y == null)
                    return false;

                return x.Type == y.Type;
            }

            public int GetHashCode(ParameterExpression obj)
            {
                if (obj == null)
                    return 0;

                return obj.Type.GetHashCode();
            }
        }
        #endregion
    }
    #endregion
}

 

 

 

三-->如何使用这个扩展：

 

 

/// <summary>
/// 菜单
/// </summary>
public class MenuController : BaseController
{
    public IMenuRepository menu { get; set; } //IOC属性注入
	
	/// <summary>
    /// 菜单列表分页查询
    /// </summary>
    /// <param name="pageIndex">当前页</param>
    /// <param name="menuName">菜单名称</param>
    /// <param name="menuLevel">菜单级别</param>
    /// <returns></returns>
	[HttpPost]
	public JsonResult List(int pageIndex, int pageSize, string menuName, int menuLevel)
	{
		//在控制器中我们很多时候需要对页面提交过来的数据做多条件查询。所以我们需要做 Expression<Func<Menu, bool>>做扩展
		
		Expression<Func<Menu, bool>> filter = r => true;
		var totalCount = menu.GetAll().Count(); //数据总条数
		int pageCount = (int)Math.Ceiling(totalCount * 1.0f / pageSize);//总页数
		
		//条件查询一
		if (!string.IsNullOrEmpty(menuName))
		{
			filter = filter.And(r => r.MenuName.Contains(menuName));
		}
		//条件查询二
		if (menuLevel > 0)
		{
			filter = filter.And(r => r.MenuLevel == menuLevel);
		}
		//调用分页中的方法
		var data = menu.GetPagedList(pageIndex, pageSize, filter, r => r.sort).ToList();
	
		var result = new { TotalCount = totalCount, PageCount = pageCount, Data = data.ToList() };
		return Success(result);
	}
}

 

select * from user where age=25 and name like '%张%' or name like '%李%'
//想生成如下带括号的语句就声明两个filter，姓名的全部or，完了再和另外一个and
select * from user where age=25 and （name like '%张%' or name like '%李%'）

if (!string.IsNullOrEmpty(model.tagNames))
{
    Expression<Func<V_User, bool>> filterB = r=>false;
    string[] tags = model.tagNames.Split(',');
    for (int i = 0; i < tags.Length; i++)
    {
        var name = tags[i].ToString();
        filterB = filterB.Or(r => r.tagnames.Contains(name));
    }
    filter = filter.And(filterB);
}