/// <summary>
///
/// </summary>
/// <param name="query"></param>
/// <param name="pageNumber"></param>
/// <param name="pageSize"></param>
/// <param name="returnTotalRecord"></param>
/// <returns></returns>
public static ListResult <T> GetDataPage(IOrderedQueryable <T> query, int?pageNumber, int?pageSize, bool returnTotalRecord = true)
{
if (query == null)
{
return(null);
}
var result = new ListResult <T>();
if (pageNumber != null && pageSize != null &&
pageNumber > 0 && pageSize > 0)
{
if (returnTotalRecord)
{
result.TotalRecord = query.Count();
}
result.ListOfObjects = query.Skip((int)pageSize * ((int)pageNumber - 1)).Take((int)pageSize).ToList();
}
else
{
if (returnTotalRecord)
{
result.TotalRecord = query.Count();
}
result.ListOfObjects = query.ToList();
}
return(result);
}
public EntityPagingModel(IOrderedQueryable <TEntity> query, TFilter filter, int pageSize = 15)
{
int page = 0;
if (filter != null)
{
page = filter.p;
}
if (query != null)
{
int TotalPages = (int)Math.Ceiling(query.Count() / (decimal)pageSize);
if (filter.p >= TotalPages)
{
filter.p = 0;
}
Filter = filter;
int start = page * pageSize,
end = start + pageSize;
Entries = query.Skip(start).Take(pageSize);
Paging = new PageIndexModel()
{
CurrentPage = filter.p,
PagingSize = pageSize,
TotalPages = TotalPages,
TotalRecords = query.Count(),
CurrentRecordRange = new Tuple <int, int>(start, end)
};
}
}
private void AddToWeatherStatusLog(string airportCode, IOrderedQueryable <tbl_WeatherStatusLog> status,
List <Forecastday> .Enumerator forecastdaysEnumerable, List <Forecastday2> .Enumerator simpleForecastdaysEnumerable, MMTSqlLiveEntities entities)
{
for (int i = 0; i < 3 - status.Count(); i++)
{
if (!forecastdaysEnumerable.MoveNext())
{
break;
}
tbl_WeatherStatusLog log = new tbl_WeatherStatusLog
{
AirPortCode = airportCode,
LastModifiedDate = DateTime.Now,
WeatherStatusDate = DateTime.Now.AddDays(i + status.Count()).Date,
MorningStatus = JsonConvert.SerializeObject(forecastdaysEnumerable.Current)
};
if (!forecastdaysEnumerable.MoveNext())
{
break;
}
log.EveningStatus = JsonConvert.SerializeObject(forecastdaysEnumerable.Current);
if (!simpleForecastdaysEnumerable.MoveNext())
{
break;
}
log.CompleteDayStatus = JsonConvert.SerializeObject(simpleForecastdaysEnumerable.Current);
entities.tbl_WeatherStatusLog.Add(log);
}
}
/// <summary>
///
/// </summary>
/// <param name="collection"></param>
/// <param name="context"></param>
/// <param name="page"></param>
/// <param name="pageSize"></param>
/// <typeparam name="TEntity"></typeparam>
/// <returns></returns>
public static ICollection <TEntity> AddPaginationToHeader <TEntity>(this IOrderedQueryable <TEntity> collection,
ApiController controller, int page, int pageSize, string resource = null) where TEntity : class
{
var resourceName = resource ??
controller.ActionContext.ControllerContext.ControllerDescriptor.ControllerName;
var totalCount = collection.Count();
var totalPages = (int)Math.Ceiling((double)totalCount / pageSize);
var urlHelper = new UrlHelper(controller.Request);
var prevLink = page > 0 ? urlHelper.Link(resourceName, new { page = page - 1, size = pageSize }) : "";
var nextLink = page < totalPages - 1
? urlHelper.Link(resourceName, new { page = page + 1, pageSize = pageSize })
: "";
var pagingHeader = new
{
TotalCount = totalCount,
TotalPages = totalPages,
PrevPageLink = prevLink,
NextPageLink = nextLink
};
var response = HttpContext.Current.Response;
response.Headers.Add("X-Pagination", Newtonsoft.Json.JsonConvert.SerializeObject(pagingHeader));
return(collection.Skip(pageSize * page).Take(pageSize).ToList());
}
private static void EagerLazy()
{
CRMEntities db = new CRMEntities();
db.Database.Log += Console.WriteLine;
IQueryable <Customer> cs = db.Customers;
//.....
cs = cs.Where(c => c.City == "London");
//ICollection<Customer> customers = db.Customers.ToList();
Customer customer = cs.FirstOrDefault();
cs = cs.Include(c => c.Orders);
IOrderedQueryable <Customer> customersLonSort = cs.OrderBy(c => c.LastName);
//....
ICollection <Customer> customersLon = customersLonSort.ToList();
bool exist = customersLon.Contains(customer);
int ntCustomers = customersLonSort.Count();
foreach (var cc in customersLon)
{
Console.WriteLine($"{cc.LastName} + {cc.FirstName}: {cc.Orders.Count} comenzi");
}
}
public OrderedPagination(IOrderedQueryable <T> source, int pageNumber, int pageSize)
{
orderedSource_ = source;
TotalItems = source.Count();
PageNumber = pageNumber;
PageSize = pageSize;
}
public PaginationUser ListPage(Pagination pagination, IOrderedQueryable <user> users)
{
int page = pagination.Page;
int size = pagination.Size;
if (page < 1)
{
page = 1;
}
int skip = (page - 1) * size;
int sizePage = users.Count();
if (sizePage % size > 0)
{
sizePage = sizePage / size + 1;
}
else
{
sizePage = sizePage / size;
}
var sql = users.Skip(skip).Take(size).ToList();
PaginationUser paginationUser = new PaginationUser(sizePage, page, sql);
return(paginationUser);
}
public static PageList <T> Create(IOrderedQueryable <T> source, int pageNumber, int pageSize)
{
int count = source.Count();
var items = source.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList();
return(new PageList <T>(items, count, pageNumber, pageSize));
}
public PaginationComment ListPage(Pagination pagination, IOrderedQueryable <comment> comments)
{
int page = pagination.Page;
int size = pagination.Size;
if (page < 1)
{
page = 1;
}
int skip = (page - 1) * size;
int sizePage = comments.Count();
if (sizePage % size > 0)
{
sizePage = sizePage / size + 1;
}
else
{
sizePage = sizePage / size;
}
var sql = comments.Skip(skip).Take(size).ToList();
PaginationComment paginationComment = new PaginationComment(sizePage, page, sql);
return(paginationComment);
}
public static PagedList <T> ToPagedList <T>(this IOrderedQueryable <T> query, IPageable cmd)
{
var list = new PagedList <T>
{
PageNumber = !cmd.PageNumber.HasValue || cmd.PageNumber < 1
? 1
: cmd.PageNumber.Value,
PageSize = !cmd.PageSize.HasValue || cmd.PageSize < 1 || cmd.PageSize > 1000
? 10
: cmd.PageSize.Value,
};
var recordsToSkip = list.PageNumber > 1
? (list.PageNumber - 1) * list.PageSize
: 0;
var result = query
.Skip(recordsToSkip)
.Take(list.PageSize)
.GroupBy(x => new { Total = query.Count() })
.FirstOrDefault();
list.Items = result?.ToArray() ?? new T[0];
list.TotalItems = result?.Key.Total ?? 0;
return(list);
}
/// <summary>
/// 对IOrderedQueryable进行分页
/// </summary>
/// <typeparam name="TSource">实体类</typeparam>
/// <param name="source">List列表</param>
/// <param name="recordCount">记录总数</param>
/// <param name="pageSize">每页大小</param>
/// <param name="pageIndex">索引</param>
public static List <TSource> ToList <TSource>(this IOrderedQueryable <TSource> source, out int recordCount, int pageSize, int pageIndex = 1)
{
recordCount = source.Count();
#region 计算总页数
var allCurrentPage = 1;
if (pageIndex < 1)
{
pageIndex = 1;
}
if (pageSize < 0)
{
pageSize = 0;
}
if (pageSize != 0)
{
allCurrentPage = (recordCount / pageSize);
allCurrentPage = ((recordCount % pageSize) != 0 ? allCurrentPage + 1 : allCurrentPage);
allCurrentPage = (allCurrentPage == 0 ? 1 : allCurrentPage);
}
if (pageIndex > allCurrentPage)
{
pageIndex = allCurrentPage;
}
#endregion
return(source.Skip(pageSize * (pageIndex - 1)).Take(pageSize).ToList());
}
private void timer_Elapsed2(object sender, ElapsedEventArgs e)
{
if ((this.beginTime.TimeOfDay.Hours == 11 && this.beginTime.TimeOfDay.Minutes == 0) || (this.beginTime.TimeOfDay.Hours == 17 && this.beginTime.TimeOfDay.Minutes == 0) || (this.beginTime.TimeOfDay.Hours == 22 && this.beginTime.TimeOfDay.Minutes == 0))
{
this.notifyIcon1.ShowBalloonTip(10000, "", "请及时签出！", ToolTipIcon.Info);
}
if ((this.beginTime.TimeOfDay.Hours == 12 && this.beginTime.TimeOfDay.Minutes == 30) || (this.beginTime.TimeOfDay.Hours == 18 && this.beginTime.TimeOfDay.Minutes == 30) || (this.beginTime.TimeOfDay.Hours == 23 && this.beginTime.TimeOfDay.Minutes == 30))
{
IOrderedQueryable <signrecord> source = from item in this.entity.signrecord
where item.userId == this.uid
orderby item.signTimestamp descending
select item;
if (source.Count <signrecord>() > 0)
{
if (source.First <signrecord>().in_out)
{
this.contextMenuStrip1.Items[1].Text = "签出 " + this.beginTime;
}
else
{
this.contextMenuStrip1.Items[1].Text = "签入 " + this.beginTime;
}
}
}
}
public IQueryable <T> GetPagedData <T>(IOrderedQueryable <T> records)
{
if (PageSize == 0)//this is default
{
PageSize = 12;
}
if (Index == 0)
{
Index = 1;
}
var remains = records.Count() - (PageSize * Index);
var remainingPagesCount = remains > 0 ? remains / PageSize : 0;
if (remains > 0 && remains % PageSize != 0)
{
remainingPagesCount++;
}
PagingViewData = new ViewDataDictionary(ViewData)
{
{ "Index", Index },
{ "Size", PageSize },
{ "RemainingPagesCount", remainingPagesCount },
{ "Page", RouteData.Values["page"].ToString() },
{ "Query", Request.QueryString.Value }
};
return(records
.Skip((Index - 1) * PageSize)
.Take(PageSize));
}
/// <summary>
/// 6.0 分页查询
/// </summary>
/// <typeparam name="TKey"></typeparam>
/// <param name="pagedData">分页模型（页码，页容量；总行数，当前页数据集合）</param>
/// <param name="whereLambda">查询条件</param>
/// <param name="orderBy">排序条件</param>
/// <param name="isAsc">是否升序</param>
/// <param name="joinPropertyNames">join属性名</param>
public void GetPagedList <TKey>(
Model.FormatModels.PageData pagedData,
System.Linq.Expressions.Expression <Func <T, bool> > whereLambda,
System.Linq.Expressions.Expression <Func <T, TKey> > orderBy,
bool isAsc = true,
params string[] joinPropertyNames)
{
//1.获取 对应实体类 的查询 对象
DbQuery <T> dbQuery = db.Set <T>();
//2.循环 要连接查询的 属性
foreach (var joinProperty in joinPropertyNames)
{
dbQuery = dbQuery.Include(joinProperty);
}
//3.加入 查询 条件
IQueryable <T> query = dbQuery.Where(whereLambda);
//4.加入 排序 条件
IOrderedQueryable <T> orderedQuery = null;
if (isAsc)
{
orderedQuery = query.OrderBy(orderBy);
}
else
{
orderedQuery = query.OrderByDescending(orderBy);
}
//5.分页查询 Skip Take
//5.1查询 当前页码 的 数据集合
pagedData.rows = orderedQuery.Skip((pagedData.PageIndex - 1) * pagedData.PageSize).Take(pagedData.PageSize).ToList();
//5.2查询符合条件的 总行数
pagedData.total = orderedQuery.Count();
}
internal static int CalculatePageCount <T>(this IOrderedQueryable <T> query, int pageSize, out int queryCount)
{
Tracer.Verbose(() => "calculating pagecount");
if (pageSize < 0)
{
throw new ArgumentOutOfRangeException("pageSize", "pageSize cannot be less than zero");
}
Tracer.Verbose(() => "getting the query count");
queryCount = query.Count();
Tracer.Verbose(() => "check for pageSize");
if (pageSize == 0)
{
Tracer.Verbose(() => "paging is disabled, pageCount set to 1");
return(1);
}
Tracer.Verbose(() => "calculating pageCount");
int remainder;
int pageCount = Math.DivRem(queryCount, pageSize, out remainder);
if (remainder > 0)
{
Tracer.Verbose(() => "adding 1 extra page for the remaining items");
pageCount++;
}
Tracer.Verbose(() => "PageCount is " + pageCount);
return(pageCount);
}
public FuncResult ElementSelect(int pageSize, int currentPage, string userAccount, string userName)
{
IOrderedQueryable <SysUserInfo> query = _context.SysUserInfo.
Where(a =>
(
(string.IsNullOrWhiteSpace(userAccount) || a.UserAccount.Contains(userAccount)) &&
(string.IsNullOrWhiteSpace(userName) || a.UserName.Contains(userName))
)
).OrderByDescending(e => e.CreationDate);
int total = query.Count();
var data = query.Skip(pageSize * currentPage).Take(pageSize).ToList().Select(e => new
{
User_Id = e.UserId,
User_Account = e.UserAccount,
User_Name = e.UserName,
User_Org_Id = e.UserOrgId,
User_Group_Names = e.UserGroupNames,
User_Email = e.UserEmail,
User_Password = e.UserPassword,
User_Mobile_No = e.UserMobile,
CreationDate = e.CreationDate.ToString("yyyy-MM-dd HH:mm:ss"),
User_Valid_Time = e.ValidTime
});
return(new FuncResult()
{
IsSuccess = true, Content = new { data, total }
});
}
/// <summary>
/// 查询列表
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public FuncResult Select(SearchSysUserInfo model)
{
IOrderedQueryable <SysUserInfo> query = _context.SysUserInfo.
Where(a =>
(
(string.IsNullOrWhiteSpace(model.User_Account) || a.UserAccount.Contains(model.User_Account)) &&
(string.IsNullOrWhiteSpace(model.User_Name) || a.UserName.Contains(model.User_Name))
)
).OrderByDescending(e => e.CreationDate);
int total = query.Count();
var data = query.Skip(model.limit * model.page).Take(model.limit).ToList().Select(e => new
{
User_Id = e.UserId,
User_Account = e.UserAccount,
User_Name = e.UserName,
User_Org_Id = e.UserOrgId,
User_Group_Names = e.UserGroupNames,
User_Email = e.UserEmail,
User_Password = e.UserPassword,
User_Mobile_No = e.UserMobile,
CreationDate = e.CreationDate.ToString("yyyy-MM-dd HH:mm:ss")
});
return(new FuncResult()
{
IsSuccess = true, Content = new { data, total }
});
}
private void DataBindResourceType()
{
using (pm2Entities pm2Entities = new pm2Entities())
{
IOrderedQueryable <Res_ResourceType> orderedQueryable =
from rt in pm2Entities.Res_ResourceType
where rt.Res_ResourceType2 == null
orderby rt.ResourceTypeCode
select rt;
if (orderedQueryable.Count <Res_ResourceType>() > 0)
{
foreach (Res_ResourceType current in orderedQueryable)
{
TreeNode treeNode = new TreeNode(current.ResourceTypeName, current.ResourceTypeId);
this.trvwResourceType.Nodes.Add(treeNode);
if (!string.IsNullOrEmpty(this.resourceTypeId) && treeNode.Value == this.resourceTypeId)
{
treeNode.Selected = true;
}
}
if (string.IsNullOrEmpty(this.resourceTypeId))
{
this.trvwResourceType.Nodes[0].Selected = true;
this.AddResourceTypeIdToUrl();
}
}
}
}
public async Task <IHttpActionResult> GetSchool(int pageSize, int page, string order, bool isAsc)
{
PageResult <Diploma> result = new PageResult <Diploma>();
IOrderedQueryable <Diploma> services = db.Diplomas;
var count = services.Count();
if (count == 0)
{
result.Data = null;
result.Count = 0;
result.CurrentPage = 1;
result.Order = order;
result.IsAsc = isAsc;
result.PageSize = pageSize;
return(Ok(result));
}
result.Count = count;
var schs = isAsc ? LinqOrder.DataSort(services, order, "asc") : LinqOrder.DataSort(services, order, "desc");
//schools = isAsc ? schools.OrderBy(order):schools.OrderByDescending(order);
result.Data = await schs.Skip((page - 1) *pageSize).Take(pageSize).ToListAsync();
result.CurrentPage = page;
result.Order = order;
result.IsAsc = isAsc;
result.PageSize = pageSize;
return(Ok(result));;
}
public static bool IsUserOnTrial(MenuDartDBContext db, IPrincipal User)
{
//retrieve user info entry
IOrderedQueryable <UserInfo> userFound = from userInfo in db.UserInfo
where userInfo.Name == User.Identity.Name
orderby userInfo.Name ascending
select userInfo;
//if there is a user logged in
if ((userFound == null) || (userFound.Count() == 0))
{
return(false);
}
//should only be one in the list
IList <UserInfo> userInfoList = userFound.ToList();
if (userInfoList[0].TrialEnded)
{
return(false);
}
else
{
return(true);
}
}
//phân trang tìm theo thể lọai
public PaginationComic ListPage(Pagination pagination, IOrderedQueryable <ComicCategoryFilte> comics)
{
int page = pagination.Page;
int size = pagination.Size;
if (page < 1)
{
page = 1;
}
int skip = (page - 1) * size;
int sizePage = comics.Count();
if (sizePage % size > 0)
{
sizePage = sizePage / size + 1;
}
else
{
sizePage = sizePage / size;
}
var sql = comics.Skip(skip).Take(size).ToList();
PaginationComic paginationComic = new PaginationComic(sizePage, page, sql);
return(paginationComic);
}
public JsonResult GetEvents(int iDisplayStart, int iDisplayLength, string sSearch)
{
try
{
int totalRecords = _db.Events.Count();
int totalDisplayRecords;
if (!string.IsNullOrEmpty(sSearch))
{
IOrderedQueryable <Event> initialSearch = _db.Events.Where(x => x.RegistrationLink.ToLower().Contains(sSearch) || x.StartingDate.ToLower().Contains(sSearch) ||
x.Technology.ToLower().Contains(sSearch) || x.Title.ToLower().Contains(sSearch)).OrderBy(x => x.Id);
var result = initialSearch.Skip(iDisplayStart).Take(iDisplayLength).ToList();
totalDisplayRecords = initialSearch.Count();
return(Json(new { aaData = result, iTotalRecords = totalRecords, iTotalDisplayRecords = totalDisplayRecords }, JsonRequestBehavior.AllowGet));
}
totalDisplayRecords = totalRecords;
var eventList = _db.Events.OrderBy(x => x.Id).Skip(iDisplayStart).Take(iDisplayLength).ToList();
return(Json(new { aaData = eventList, iTotalRecords = totalRecords, iTotalDisplayRecords = totalDisplayRecords }, JsonRequestBehavior.AllowGet));
}
catch (Exception e)
{
return(Json(new { error = e.Message.ToString() }));
}
}
public async static Task <PagedList <TEntity> > ToPagedList(IOrderedQueryable <TEntity> source, int pageNumber, int pageSize)
{
var count = source.Count();
var data = await source.Skip((pageNumber - 1) *pageSize).Take(pageSize).ToListAsync();
return(new PagedList <TEntity>(data, count, pageNumber, pageSize));
}
/// <summary>
/// 随机查询定量数据行
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="entitySet"></param>
/// <param name="count"></param>
/// <returns></returns>
public static IList <TEntity> RandomSelect <TEntity>(this IOrderedQueryable <TEntity> entitySet, int count)
{
int totalCount = entitySet.Count();
Random random = new Random();
if (totalCount > 1)
{
int seed = totalCount > count ? count : totalCount;
IList <int> skipCounts = new List <int>(seed);
IList <TEntity> results = new List <TEntity>(seed);
for (int i = 0; i < count; i++)
{
int skipCount = random.Next(seed);
while (skipCounts.Contains(skipCount))
{
skipCount = random.Next(seed);
}
results.Add(entitySet.Skip(skipCount).FirstOrDefault());
}
return(results);
}
else
{
return(entitySet.ToList());
}
}
/// <summary>
/// 分页扩展方法
/// </summary>
/// <typeparam name="T">领域实体</typeparam>
/// <param name="queryable">集合对象</param>
/// <param name="pageIndex">页索引</param>
/// <param name="pageSize">页容量</param>
/// <param name="rowCount">总记录条数</param>
/// <param name="pageCount">总页数</param>
/// <returns>对象集合</returns>
public static IQueryable <T> ToPage <T>(this IOrderedQueryable <T> queryable, int pageIndex, int pageSize, out int rowCount, out int pageCount)
where T : PlainEntity
{
rowCount = queryable.Count();
pageCount = (int)Math.Ceiling(rowCount * 1.0 / pageSize);
return(queryable.Skip((pageIndex - 1) * pageSize).Take(pageSize));
}
public static PaginatedList <T> CreateAsync(IOrderedQueryable <T> source, int pageIndex, int pageSize)
{
var count = source.Count();
var items = source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
return(new PaginatedList <T>(items, count, pageIndex, pageSize));
}
public static PaginList <T> Create(IOrderedQueryable <T> qry, int pageSize, int pageIndex, Model modelin, string sortExpression, string defaultSortExpression)
{
var pageCount = (int)Math.Ceiling(qry.Count() / (double)pageSize);
return(new PaginList <T>(qry.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList(),
pageSize, pageIndex, pageCount));
}
public static IQueryable <T> GetPageList <T>(IOrderedQueryable <T> List, int PageIndex, int PageSize)
{
int PageCount = GetPageCount(PageSize, List.Count());
PageIndex = CheckPageIndex(PageIndex, PageCount);
return(List.Skip((PageIndex - 1) * PageSize).Take(PageSize));
}
/// <summary>
/// Initializes a new instance of the <see cref="PagedList{T}"/> class that divides the supplied superset into subsets the size of the supplied pageSize. The instance then only containes the objects contained in the subset specified by index.
/// </summary>
/// <param name="superset">The collection of objects to be divided into subsets. If the collection implements <see cref="IOrderedQueryable{T}"/>, it will be treated as such.</param>
/// <param name="pageNumber">The one-based index of the subset of objects to be contained by this instance.</param>
/// <param name="pageSize">The maximum size of any individual subset.</param>
/// <exception cref="ArgumentOutOfRangeException">The specified index cannot be less than zero.</exception>
/// <exception cref="ArgumentOutOfRangeException">The specified page size cannot be less than one.</exception>
public PagedList(IOrderedQueryable <T> superset, int pageNumber, int pageSize)
: base(pageNumber, pageSize, superset?.Count() ?? 0)
{
if (TotalItemCount > 0)
{
InitSubset(superset, pageNumber, pageSize);
}
}
/// <summary>
/// 分页
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="orderedResult">已排序集合对象</param>
/// <param name="pageIndex">页码</param>
/// <param name="pageSize">页容量</param>
/// <returns>实体对象列表</returns>
public static Page<T> ToPage<T>(this IOrderedQueryable<T> orderedResult, int pageIndex, int pageSize)
{
int rowCount = orderedResult.Count();
int pageCount = (int)Math.Ceiling(rowCount * 1.0 / pageSize);
IList<T> pagedResult = orderedResult.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
return new Page<T>(pagedResult, pageIndex, pageSize, pageCount, rowCount);
}