前面两篇文章只写了增删改功能,并不是把查询功能遗漏了,本篇就单独来讨论分页查询。分页查询很常用,实现并不难,但是想要用起来方便舒服还是要花些心思。虽然分页查询的实现跟ABP框架并没有什么关系,但是为了延续性暂且还是放在这里了。
对于分页查询来讲,需要注意的有两点:一是查询条件的处理,用户的输入是多样化的、无法预知的,查询规则要足够灵活和简单;二是查询结果的分页处理,一般是根据当前页码和每页记录数来展示数据,要有足够快的查询效率和响应速度。
1.定义查询条件输入类
在应用层AbpDemo.Application新建用于查询输入的基类,包含过滤条件和排序规则两个属性。
public class PagedBaseDto
{
/// <summary>
/// 过滤条件
/// </summary>
public List<DataFilter> Filters { get; set; }
/// <summary>
/// 排序规则
/// </summary>
public List<DataSort> Sorts { get; set; }
}
其中过滤条件为:
public class DataFilter
{
public string FilterName { get; set; }
public string FilterValue { get; set; }
public FilterType FilterType { get; set; }
public ExpressionType ExpressionType { get; set; }
}
public enum FilterType
{
String,
Int,
Long,
Boolean
}
public enum ExpressionType
{
/// <summary>
/// like
/// </summary>
Contains,
/// <summary>
/// 等于
/// </summary>
Equal,
/// <summary>
/// 小于
/// </summary>
LessThan,
/// <summary>
/// 小于等于
/// </summary>
LessThanOrEqual,
/// <summary>
/// 大于
/// </summary>
GreaterThan,
/// <summary>
/// 大于等于
/// </summary>
GreaterThanOrEqual
}
排序方式为:
public class DataSort
{
public string SortName { get; set; }
public SortType SortType { get; set; }
}
public enum SortType
{
/// <summary>
/// 升序
/// </summary>
Asc,
/// <summary>
/// 降序
/// </summary>
Desc
}
2.处理查询条件并返回结果
在应用层的基类AbpDemoAppServiceBase中创建一个方法用于处理查询条件并返回结果。方法的输入参数继承了上一步的基类PagedBaseDto。
/// <summary>
/// 分页查询
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public virtual async Task<IQueryable<TEntity>> Page(TPagedInput input)
{
IQueryable<TEntity> query = Repository.GetAll();
var sourceExpression = query.Expression;
//where表达式
if (input.Filters != null)
{
foreach (var filter in input.Filters)
{
if (string.IsNullOrWhiteSpace(filter.FilterName) || string.IsNullOrWhiteSpace(filter.FilterValue))
{
continue;
}
var whereLambdaExtenstion = GetLambdaExtention(filter);
sourceExpression = Expression.Call(typeof(Queryable), "Where", new Type[1] { typeof(TEntity) }, sourceExpression, Expression.Quote(whereLambdaExtenstion.GetLambda()));
}
}
if (input.Sorts != null)
{
ParameterExpression parameter = Expression.Parameter(typeof(TEntity), "x");
string methodAsc = "OrderBy";
string methodDesc = "OrderByDescending";
foreach (var sort in input.Sorts)
{
if (string.IsNullOrWhiteSpace(sort.SortName))
{
continue;
}
MemberExpression body = Expression.PropertyOrField(parameter, sort.SortName);
sourceExpression = Expression.Call(typeof(Queryable), sort.SortType == SortType.Asc ? methodAsc : methodDesc, new Type[] { typeof(TEntity), body.Type }, sourceExpression, Expression.Quote(Expression.Lambda(body, parameter)));
methodAsc = "ThenBy";
methodDesc = "ThenByDescending";
}
}
query = query.Provider.Execute<IEnumerable<TEntity>>(sourceExpression).AsQueryable();
//result.TotalCount = query.Count();
//if (input.PageSize != -1)
//{
// result.Items = query.Skip((input.PageIndex - 1) * input.PageSize).Take(input.PageSize).MapTo<IEnumerable<TEntityDto>>().ToList();
//}
//else
//{
// result.Items = query.MapTo<IEnumerable<TEntityDto>>().ToList();
//}
return await Task.FromResult(query);
}
//Lambda表达式处理
private LambdaExtention<TEntity> GetLambdaExtention(DataFilter filter)
{
var whereLambdaExtenstion = new LambdaExtention<TEntity>();
switch (filter.FilterType)
{
case FilterType.Int:
whereLambdaExtenstion.GetExpression(filter.FilterName, int.Parse(filter.FilterValue), filter.ExpressionType);
break;
case FilterType.Long:
whereLambdaExtenstion.GetExpression(filter.FilterName, long.Parse(filter.FilterValue), filter.ExpressionType);
break;
case FilterType.Boolean:
whereLambdaExtenstion.GetExpression(filter.FilterName, filter.FilterValue.ToUpper() == "TRUE" ? true : false, filter.ExpressionType);
break;
default:
whereLambdaExtenstion.GetExpression(filter.FilterName, filter.FilterValue, filter.ExpressionType);
break;
}
return whereLambdaExtenstion;
}
由于ABP框架中使用的是仓储模式,仓储模式一般使用Lambda表达式或Linq语句来进行查询,因此需要将输入的查询条件转化成Lambda表达式。这个过程就需要用到Lambda表达式相关的辅助类。
/// <summary>
/// 动态Lambda构造类
/// </summary>
/// <typeparam name="Dto"></typeparam>
public class LambdaExtention<Dto> where Dto : class
{
private List<Expression> m_lstExpression = null;
private ParameterExpression m_Parameter = null;
public LambdaExtention()
{
m_lstExpression = new List<Expression>();
m_Parameter = Expression.Parameter(typeof(Dto), "x");
}
//构造表达式,存放到m_lstExpression集合里面
public void GetExpression(string strPropertyName, object strValue, ExpressionType expressType)
{
Expression expRes = null;
MemberExpression member = Expression.PropertyOrField(m_Parameter, strPropertyName);
if (member.Type == typeof(int?) || member.Type == typeof(int))
{
strValue = Convert.ToInt32(strValue);
}
else if (member.Type == typeof(DateTime?) || member.Type == typeof(DateTime))
{
strValue = Convert.ToDateTime(strValue);
}
if (expressType == ExpressionType.Contains)
{
expRes = Expression.Equal(member, Expression.Constant(strValue, member.Type));
}
else if (expressType == ExpressionType.Equal)
{
expRes = Expression.Equal(member, Expression.Constant(strValue, member.Type));
}
else if (expressType == ExpressionType.LessThan)
{
expRes = Expression.LessThan(member, Expression.Constant(strValue, member.Type));
}
else if (expressType == ExpressionType.LessThanOrEqual)
{
expRes = Expression.LessThanOrEqual(member, Expression.Constant(strValue, member.Type));
}
else if (expressType == ExpressionType.GreaterThan)
{
expRes = Expression.GreaterThan(member, Expression.Constant(strValue, member.Type));
}
else if (expressType == ExpressionType.GreaterThanOrEqual)
{
expRes = Expression.GreaterThanOrEqual(member, Expression.Constant(strValue, member.Type));
}
//return expRes;
m_lstExpression.Add(expRes);
}
//得到Lamada表达式的Expression对象
public Expression<Func<Dto, bool>> GetLambda()
{
Expression whereExpr = null;
foreach (var expr in m_lstExpression)
{
if (whereExpr == null) whereExpr = expr;
else whereExpr = Expression.And(whereExpr, expr);
}
if (whereExpr == null)
return null;
return Expression.Lambda<Func<Dto, bool>>(whereExpr, m_Parameter);
}
}
3.对查询结果进行分页操作
在原有分页查询输入基类PagedBaseDto的基础上添加当前页码和每页记录数的属性。
public class PagedBaseDto
{
/// <summary>
/// 过滤条件
/// </summary>
public List<DataFilter> Filters { get; set; }
/// <summary>
/// 排序规则
/// </summary>
public List<DataSort> Sorts { get; set; }
private int pageSize = 10;
/// <summary>
/// 每页记录数
/// </summary>
public int PageSize
{
get { return pageSize; }
set { pageSize = value; }
}
private int pageIndex = 1;
/// <summary>
/// 当前页码
/// </summary>
public int PageIndex
{
get { return pageIndex; }
set { pageIndex = value; }
}
}
而应用服务基类AbpDemoAppServiceBase中的分页查询方法就可以改为:
/// <summary>
/// 分页查询
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public virtual async Task<PagedResultDto<TEntityDto>> Page(TPagedInput input)
{
if (input == null)
{
return new PagedResultDto<TEntityDto>();
}
PagedResultDto<TEntityDto> result = new PagedResultDto<TEntityDto>();
IQueryable<TEntity> query = Repository.GetAll();
var sourceExpression = query.Expression;
//where表达式
if (input.Filters != null)
{
foreach (var filter in input.Filters)
{
if (string.IsNullOrWhiteSpace(filter.FilterName) || string.IsNullOrWhiteSpace(filter.FilterValue))
{
continue;
}
var whereLambdaExtenstion = GetLambdaExtention(filter);
sourceExpression = Expression.Call(typeof(Queryable), "Where", new Type[1] { typeof(TEntity) }, sourceExpression, Expression.Quote(whereLambdaExtenstion.GetLambda()));
}
}
if (input.Sorts != null)
{
ParameterExpression parameter = Expression.Parameter(typeof(TEntity), "x");
string methodAsc = "OrderBy";
string methodDesc = "OrderByDescending";
foreach (var sort in input.Sorts)
{
if (string.IsNullOrWhiteSpace(sort.SortName))
{
continue;
}
MemberExpression body = Expression.PropertyOrField(parameter, sort.SortName);
sourceExpression = Expression.Call(typeof(Queryable), sort.SortType == SortType.Asc ? methodAsc : methodDesc, new Type[] { typeof(TEntity), body.Type }, sourceExpression, Expression.Quote(Expression.Lambda(body, parameter)));
methodAsc = "ThenBy";
methodDesc = "ThenByDescending";
}
}
query = query.Provider.Execute<IEnumerable<TEntity>>(sourceExpression).AsQueryable();
result.TotalCount = query.Count();
if (input.PageSize != -1)
{
result.Items = query.Skip((input.PageIndex - 1) * input.PageSize).Take(input.PageSize).MapTo<IEnumerable<TEntityDto>>().ToList();
}
else
{
result.Items = query.MapTo<IEnumerable<TEntityDto>>().ToList();
}
return await Task.FromResult(result);
}
其中PagedResultDto是ABP框架自带的分页查询结果的数据结构,这里没有改动直接用了。
4.在派生类中使用分页方法
由于分页查询的方法是在基类中定义的,继承基类的派生类可以直接使用,只需要根据基类的约束条件修改下代码就行了。
应用服务接口:
/// <summary>
/// 货品管理-应用服务接口
/// </summary>
public interface IGoodsAppService: IAbpDemoAppServiceBase<Goods,DetailGoodsDto,string,CreateGoodsDto,UpdateGoodsDto, PagedGoodsDto>,IApplicationService
{
}
应用服务:
/// <summary>
/// 货品管理-应用服务
/// </summary>
public class GoodsAppService: AbpDemoAppServiceBase<Goods,DetailGoodsDto,string,CreateGoodsDto,UpdateGoodsDto,PagedGoodsDto>,IGoodsAppService
{
public GoodsAppService(IRepository<Goods,string> repository):base(repository)
{
}
}
其中新加的类PagedGoodsDto是继承于第一步创建的PagedBaseDto类。