正文
（转）Entity Framework4.1实现动态多条件查询、分页和排序
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
原文：http://www.cnblogs.com/ahui/archive/2011/08/04/2127282.html
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个字段一起排序，不过只要修改排序参数还是可以实现的！
另外这种分页查询只能针对一个表或者视图
