Entity Framework4.1实现动态多条件查询、分页和排序
来源:互联网 发布:编程让系统死机 编辑:程序博客网 时间:2024/05/21 18:25
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个字段一起排序,不过只要修改排序参数还是可以实现的!
另外这种分页查询只能针对一个表或者视图
0 0
- Entity Framework4.1实现动态多条件查询、分页和排序
- Entity Framework4.1实现动态多条件查询、分页和排序
- EF实现分页查询+条件查询+排序
- Entity Framework多视图条件查询、排序
- 动态排序和动态查询条件
- MVC 列表多条件动态查询分页
- ajax多条件查询动态分页
- maven后台实现无条件分页查询和条件分页查询
- Entity Framework多表多条件动态查询
- struts hibernate spring 实现分页、排序、复杂条件查询②
- jfinal动态sql管理简洁实现分页多条件模糊查询
- Grails 多条件查询和分页
- 分页和多条件查询功能
- 分页和多条件查询功能
- 使用MVC,实现多条件查询,排序与分页 的 存储过程
- sqlserver 多表关联多条件分页排序查询
- spring boot 复杂多条件分页排序列表查询
- jpa条件查询和分页
- iOS生命周期简单演示
- Rhapsody软件框架和UML的实时系统开发
- 剑指Offer--第一个只出现一次的字符位置
- pycharm-professional-4.5.4 注册码 破解
- 使用Eclipse配置Struts2开发环境
- Entity Framework4.1实现动态多条件查询、分页和排序
- TimesTen临时(内存)空间使用和调整临时(内存)空间
- 在使用SimpleJdbcDaoSupport作为基类创建Dao时候出现的奇怪的BUG
- SpannableStringBuilder的使用
- 《设计模式》之抽象工厂模式
- SQL基础语句汇总
- java基础-面向对象(接口和抽象类)
- iOS巅峰之Swift 函数, 闭包详解(下)
- 高效率集合BitSet