Linq通用分页数据查询方法

在使用EF的过程有很多需要分页查询数据的地方，但是经常重复在输入分页的相关代码，这样即不便于维护，也增加了不少工作量。

对于通用查询有几个要点，一是要动态定义查询条件，还可以动态选择所需要的列。

1、数据查询方法

Code Snippet
  1. publicstaticList<TResult> Query<TEntity, TOrderBy, TResult>(thisIQueryable<TEntity> query,
  2.                                                                       Expression<Func<TEntity, bool>> where,
  3.                                                                       Expression<Func<TEntity, TOrderBy>> orderby,
  4.                                                                       Expression<Func<TEntity, int, TResult>> selector,
  5.                                                                       bool isAsc)

 

Code Snippet
  1. publicstaticList<object> Query<TEntity, TOrderBy>(thisIQueryable<TEntity> query,
  2.                                                                       Expression<Func<TEntity, bool>> where,
  3.                                                                       Expression<Func<TEntity, TOrderBy>> orderby,
  4.                                                                       Func<IQueryable<TEntity>, List<object>> selector,
  5.                                                                       bool isAsc)
Code Snippet
  1. publicstaticPageInfo<object> Query<TEntity, TOrderBy>(thisIQueryable<TEntity> query, int index, int pageSize,
  2.                                                                 Expression<Func<TEntity, bool>> where,
  3.                                                                 Expression<Func<TEntity, TOrderBy>> orderby,
  4.                                                                 Func<IQueryable<TEntity>, List<object>> selector,
  5.                                                                 bool isAsc)

3、分页查询方法

Code Snippet
  1. publicstaticPageInfo<object> Query<TEntity, TOrderBy>(thisIQueryable<TEntity> query, int index, int pageSize,
  2.                                                          List<Expression<Func<TEntity, bool>>> wheres,
  3.                                                          Expression<Func<TEntity, TOrderBy>> orderby,
  4.                                                          Func<IQueryable<TEntity>, List<object>> selector, bool isAsc)

 

Code Snippet
  1. publicstaticPageInfo<TResult> Query<TEntity, TOrderBy, TResult>(thisIQueryable<TEntity> query, int index, int pageSize,
  2.                                                          List<Expression<Func<TEntity, bool>>> wheres,
  3.                                                          Expression<Func<TEntity, TOrderBy>> orderby,
  4.                                                          Func<IQueryable<TEntity>, List<TResult>> selector, bool isAsc)

 

Code Snippet
  1. publicstaticPageInfo<TResult> Query<TEntity, TOrderBy, TResult>(thisIQueryable<TEntity> query, int index, int pageSize,
  2.                                                                 Expression<Func<TEntity, bool>> where,
  3.                                                                 Expression<Func<TEntity, TOrderBy>> orderby,
  4.                                                                 Func<IQueryable<TEntity>, List<TResult>> selector,
  5.                                                                 bool isAsc)

 

工具类方法：

 

Code Snippet
  1. publicclassPageInfo
  2.     {
  3.         publicstaticvoid CheckPageIndexAndSize(refint index, refint size)
  4.         {
  5.             if (index < 1)
  6.             {
  7.                 index = 1;
  8.             }
  9.  
  10.             if (size < 1)
  11.             {
  12.                 size = 20;
  13.             }
  14.         }
  15.  
  16.         publicstaticvoid CheckPageIndexAndSize(refint index, int size, int count)
  17.         {
  18.             if (count >= index * size)
  19.             {
  20.                 return;
  21.             }
  22.  
  23.             index = count / size;
  24.             if (count % size > 0)
  25.             {
  26.                 index++;
  27.             }
  28.  
  29.             if (index == 0)
  30.             {
  31.                 index = 1;
  32.             }
  33.         }
  34.  
  35.     }
  36.  
  37.     publicclassPageInfo<T> : PageInfo
  38.     {
  39.         internal PageInfo()
  40.         {
  41.             DataList = newList<T>();
  42.         }
  43.         public PageInfo(int index, int pageSize, int count, List<T> dataList)
  44.         {
  45.             Index = index;
  46.             PageSie = pageSize;
  47.             Count = count;
  48.             DataList = dataList;
  49.         }
  50.  
  51.         publicint Index { get; privateset; }
  52.         publicint PageSie { get; privateset; }
  53.         publicint Count { get; privateset; }
  54.         publicList<T> DataList { get; privateset; }
  55.  
  56.         publicPageInfo<T> Empty
  57.         {
  58.             get { returnnewPageInfo<T>(); }
  59.         }
  60.     }

 

实现代码：

Code Snippet
  1. publicstaticclassLinqExtent
  2.     {
  3.         publicstaticList<TResult> Query<TEntity, TOrderBy, TResult>(thisIQueryable<TEntity> query,
  4.                                                                       Expression<Func<TEntity, bool>> where,
  5.                                                                       Expression<Func<TEntity, TOrderBy>> orderby,
  6.                                                                       Expression<Func<TEntity, int, TResult>> selector,
  7.                                                                       bool isAsc)
  8.         {
  9.             if (selector == null)
  10.             {
  11.                 thrownewArgumentNullException("selector");
  12.             }
  13.  
  14.             var queryable = query;
  15.             if (where != null)
  16.             {
  17.                 queryable = queryable.Where(where);
  18.             }
  19.             if (orderby != null)
  20.             {
  21.                 queryable = isAsc ? queryable.OrderBy(orderby) : queryable.OrderByDescending(orderby);
  22.             }
  23.  
  24.             return queryable.Select(selector).ToList();
  25.         }
  26.  
  27.         publicstaticList<object> Query<TEntity, TOrderBy>(thisIQueryable<TEntity> query,
  28.                                                                       Expression<Func<TEntity, bool>> where,
  29.                                                                       Expression<Func<TEntity, TOrderBy>> orderby,
  30.                                                                       Func<IQueryable<TEntity>, List<object>> selector,
  31.                                                                       bool isAsc)
  32.         {
  33.             if (selector == null)
  34.             {
  35.                 thrownewArgumentNullException("selector");
  36.             }
  37.  
  38.             var queryable = query;
  39.             if (where != null)
  40.             {
  41.                 queryable = queryable.Where(where);
  42.             }
  43.             if (orderby != null)
  44.             {
  45.                 queryable = isAsc ? queryable.OrderBy(orderby) : queryable.OrderByDescending(orderby);
  46.             }
  47.  
  48.             return selector(queryable);
  49.         }
  50.  
  51.         publicstaticPageInfo<object> Query<TEntity, TOrderBy>(thisIQueryable<TEntity> query, int index, int pageSize,
  52.                                                                 Expression<Func<TEntity, bool>> where,
  53.                                                                 Expression<Func<TEntity, TOrderBy>> orderby,
  54.                                                                 Func<IQueryable<TEntity>, List<object>> selector,
  55.                                                                 bool isAsc)
  56.         {
  57.             return Query(query, index, pageSize, newList<Expression<Func<TEntity, bool>>> {where}, orderby, selector,
  58.                          isAsc);
  59.         }
  60.         publicstaticPageInfo<object> Query<TEntity, TOrderBy>(thisIQueryable<TEntity> query, int index, int pageSize,
  61.                                                          List<Expression<Func<TEntity, bool>>> wheres,
  62.                                                          Expression<Func<TEntity, TOrderBy>> orderby,
  63.                                                          Func<IQueryable<TEntity>, List<object>> selector, bool isAsc)
  64.         {
  65.             //if (selector == null)
  66.             //{
  67.             //    throw new ArgumentNullException("selector");
  68.             //}
  69.  
  70.             //PageInfo.CheckPageIndexAndSize(ref index,ref pageSize);
  71.             //IQueryable<TEntity> queryable = query;
  72.             //if (wheres != null)
  73.             //{
  74.             //    wheres.ForEach(p=>queryable = queryable.Where(p));
  75.             //}
  76.  
  77.             //int count = query.Count();
  78.             //PageInfo.CheckPageIndexAndSize(ref index,pageSize,count);
  79.             //if (count > 0)
  80.             //{
  81.             //    if (orderby != null)
  82.             //    {
  83.             //        queryable = isAsc ? queryable.OrderBy(orderby) : queryable.OrderByDescending(orderby);
  84.  
  85.             //    }
  86.  
  87.             //    return new PageInfo<object>(index,pageSize,count,selector(queryable));
  88.             //}
  89.  
  90.             //return new PageInfo<object>(index,pageSize,count,new List<object>());
  91.  
  92.             return Query<TEntity, TOrderBy, object>(query, index, pageSize, wheres, orderby, selector, isAsc);
  93.         }
  94.  
  95.         publicstaticPageInfo<TResult> Query<TEntity, TOrderBy, TResult>(thisIQueryable<TEntity> query, int index, int pageSize,
  96.                                                          List<Expression<Func<TEntity, bool>>> wheres,
  97.                                                          Expression<Func<TEntity, TOrderBy>> orderby,
  98.                                                          Func<IQueryable<TEntity>, List<TResult>> selector, bool isAsc)
  99.         {
  100.             if (selector == null)
  101.             {
  102.                 thrownewArgumentNullException("selector");
  103.             }
  104.  
  105.             PageInfo.CheckPageIndexAndSize(ref index, ref pageSize);
  106.             IQueryable<TEntity> queryable = query;
  107.             if (wheres != null)
  108.             {
  109.                 wheres.ForEach(p => queryable = queryable.Where(p));
  110.             }
  111.  
  112.             int count = query.Count();
  113.             PageInfo.CheckPageIndexAndSize(ref index, pageSize, count);
  114.             if (count > 0)
  115.             {
  116.                 if (orderby != null)
  117.                 {
  118.                     queryable = isAsc ? queryable.OrderBy(orderby) : queryable.OrderByDescending(orderby);
  119.  
  120.                 }
  121.  
  122.                 returnnewPageInfo<TResult>(index, pageSize, count, selector(queryable));
  123.             }
  124.  
  125.             returnnewPageInfo<TResult>(index, pageSize, count, newList<TResult>());
  126.         }
  127.  
  128.         publicstaticPageInfo<TResult> Query<TEntity, TOrderBy, TResult>(thisIQueryable<TEntity> query, int index, int pageSize,
  129.                                                                 Expression<Func<TEntity, bool>> where,
  130.                                                                 Expression<Func<TEntity, TOrderBy>> orderby,
  131.                                                                 Func<IQueryable<TEntity>, List<TResult>> selector,
  132.                                                                 bool isAsc)
  133.         {
  134.             return Query(query, index, pageSize, newList<Expression<Func<TEntity, bool>>> { where }, orderby, selector,
  135.                          isAsc);
  136.         }
  137.     }
posted @ 2013-01-19 13:51  cdboy  阅读(7378)  评论(3)  编辑  收藏  举报