Entity Framework4.1实现动态多条件查询、分页和排序

EF通用的分页实现:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

/// <summary> /// 根据条件分页获得记录 /// </summary> /// <param name="where">条件</param> /// <param name="orderBy">排序</param> /// <param name="ascending">是否升序</param> /// <param name="pageIndex">当前页码</param> /// <param name="pageSize">每页大小</param> /// <param name="totalRecord">总记录数</param> /// <returns>记录列表</returns> public  virtual  List<T> GetMany(Expression<Func<T,  bool >>  where ,  string  orderBy,  bool  ascending ,  int  pageIndex,  int  pageSize,  out  int  totalRecord) {      totalRecord = 0;      where  =  where .And(u => u.Flag != ( int )Flags.Delete);      var  list = dbset.Where( where );        totalRecord = list.Count();      if  (totalRecord <= 0)  return  new  List<T>();        list = list.OrderBy(orderBy,  ascending ).Skip((pageIndex - 1) * pageSize).Take(pageSize);        return  list.ToList(); }

 

动态排序扩展:

public  static  IQueryable<T> OrderBy<T>( this  IQueryable<T> source, string  propertyName, bool  ascending ) where  T : class {      Type type = typeof (T);        PropertyInfo property = type.GetProperty(propertyName);      if  (property == null )          throw  new  ArgumentException( "propertyName" , "Not Exist" );        ParameterExpression param = Expression.Parameter(type, "p" );      Expression propertyAccessExpression = Expression.MakeMemberAccess(param, property);      LambdaExpression orderByExpression = Expression.Lambda(propertyAccessExpression, param);        string  methodName = ascending  ? "OrderBy"  : "OrderByDescending" ;        MethodCallExpression resultExp = Expression.Call( typeof (Queryable), methodName, new  Type[] { type, property.PropertyType }, source.Expression, Expression.Quote(orderByExpression));        return  source.Provider.CreateQuery<T>(resultExp); }

 

如果要通过Expression获取字段，可以使用以下代码:

/// <summary> /// 获取对应的字段名 /// </summary> /// <typeparam name="TSource"></typeparam> /// <param name="keySelector"></param> /// <returns></returns> public  static  string  GetMemberName<TSource, TKey>(Expression<Func<TSource, TKey>> keySelector) {      string  fieldName = null ;      var  exp = keySelector.Body as  UnaryExpression;      if  (exp == null )      {          var  body = keySelector.Body as  MemberExpression;          fieldName = body.Member.Name;      }      else      {          fieldName = (exp.Operand as  MemberExpression).Member.Name;      }      return  fieldName; }

 

多条件组合(参见老赵相关文章):

/// <summary> /// 统一ParameterExpression /// </summary> internal  class  ParameterReplacer : ExpressionVisitor {      public  ParameterReplacer(ParameterExpression paramExpr)      {          this .ParameterExpression = paramExpr;      }        public  ParameterExpression ParameterExpression { get ; private  set ; }        public  Expression Replace(Expression expr)      {          return  this .Visit(expr);      }        protected  override  Expression VisitParameter(ParameterExpression p)      {          return  this .ParameterExpression;      } }   public  static  class  PredicateExtensionses {      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 >> And<T>( this  Expression<Func<T, bool >> exp_left, Expression<Func<T, bool >> exp_right)      {          var  candidateExpr = Expression.Parameter( typeof (T), "candidate" );          var  parameterReplacer = new  ParameterReplacer(candidateExpr);            var  left = parameterReplacer.Replace(exp_left.Body);          var  right = parameterReplacer.Replace(exp_right.Body);          var  body = Expression.And(left, right);            return  Expression.Lambda<Func<T, bool >>(body, candidateExpr);      }        public  static  Expression<Func<T, bool >> Or<T>( this  Expression<Func<T, bool >> exp_left, Expression<Func<T, bool >> exp_right)      {          var  candidateExpr = Expression.Parameter( typeof (T), "candidate" );          var  parameterReplacer = new  ParameterReplacer(candidateExpr);            var  left = parameterReplacer.Replace(exp_left.Body);          var  right = parameterReplacer.Replace(exp_right.Body);          var  body = Expression.Or(left, right);            return  Expression.Lambda<Func<T, bool >>(body, candidateExpr);      } }

　　

 

调用示例:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

public  static  PagedList<UsersDTO> GetUsers( int  pageIndex,  int  pageSize,  string  orderBy,  bool  ascending ,      Companys company,  string  email,  string  nickName,  bool ? isAdmin, UserStatus userStatus) {      PagedList<UsersDTO> result =  new  PagedList<UsersDTO>(pageIndex, pageSize);      int  totalRecord = 0;        Expression<Func<Users,  bool >>  where  = PredicateExtensionses.True<Users>();      if  (company != Companys.All)  where  =  where .And(u => u.Company == ( int )company);      if  (! string .IsNullOrEmpty(email))  where  =  where .And(u => u.Email.Contains(email));      if  (! string .IsNullOrEmpty(nickName))  where  =  where .And(u => u.NickName.Contains(nickName));      if  (isAdmin.HasValue)      {          if  (isAdmin.Value)  where  =  where .And(u => u.IsAdmin == 1);          else  where  =  where .And(u => u.IsAdmin == 0);      }      if  (userStatus != UserStatus.All)  where  =  where .And(u => u.UserStatus == ( int )userStatus);        if  ( string .IsNullOrEmpty(orderBy))          orderBy = MapHelper.GetMappedName<UsersDTO, Users>(u => u.UserId);      else          orderBy = MapHelper.GetMappedName<UsersDTO, Users>(orderBy);        List<Users> list = _usersDao.GetMany( where , orderBy,  ascending , pageIndex, pageSize,  out  totalRecord);      result.TotalRecordCount = totalRecord;      foreach  ( var  data  in  list)      {          result.Items.Add(Mapper.Map<Users, UsersDTO>(data));      }      return  result; }

　　

上述方法的缺点是无法针对2个字段一起排序，不过只要修改排序参数还是可以实现的！

另外这种分页查询只能针对一个表或者视图