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个字段一起排序，不过只要修改排序参数还是可以实现的！

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