public ForumItemListDTO GetForumList(int eventId, DatabasePager pager) { using (var context = new OpletalovaDbEntities()) { var skipNum = pager.SelectedPageIndex*pager.PageSize; if (skipNum < 0) skipNum = 0; var dto = new ForumItemListDTO(); var query = context.FORUM_ITEM .Where(fi => fi.EventId == eventId); dto.TotalRecords = query.Count(); dto.ForumItemList = query .Include(fi => fi.USER) .OrderByDescending(fi => fi.Created) .Skip(skipNum) // how many records we should escape .Take(pager.PageSize).ToList(); // how many rows we should returned return dto; } }
public EventListDTO GetEventList(EventDbFilter dbFilter, DatabasePager pager) { using (var context = new OpletalovaDbEntities()) { var query = context.EVENT .OrderByDescending(e => e.From) .ThenBy(e => e.To) .AsQueryable(); // file names are in different table with 1:N relation (not M:N!) // I am sure it can be done another way but this is fast! if (!String.IsNullOrEmpty(dbFilter.FileName)) { var eventIds = context.FILE_INFO.Where(f => f.FileName.Contains(dbFilter.FileName)) .Select(f => f.EventId) .ToList(); query = query.Where(e => eventIds.Contains(e.Id)); } if (!String.IsNullOrEmpty(dbFilter.ActionName)) query = query.Where(q => q.Name.Contains(dbFilter.ActionName)); if (dbFilter.EventTypeId.HasValue) query = query.Where(q => q.Type == dbFilter.EventTypeId.Value); query = dbFilter.AccessibilityId.HasValue ? query.Where(q => q.Accessibility == dbFilter.AccessibilityId.Value) : query.Where(q => dbFilter.UserAccessibilitiesByRight.Contains(q.Accessibility)); if (dbFilter.CreatorId.HasValue) query = query.Where(q => q.USER.Id == dbFilter.CreatorId.Value); query = dbFilter.IsFindNewer ? query.Where(q => q.From >= dbFilter.PilotTime) : query.Where(q => q.From <= dbFilter.PilotTime); // we need to know how many records are ther in total (to be able set paging) var totalRecords = query.Count(); // lets return only records which we want to show var skipNum = pager.SelectedPageIndex * pager.PageSize; if (skipNum < 0) skipNum = 0; query = query .Include(e => e.USER) .Include(e => e.EVENT_ACCESSIBILITY) .Include(e => e.EVENT_TYPE) .Include(e => e.FILE_INFO) .Skip(skipNum) // how many records we should escape .Take(pager.PageSize); // how many rows we should returned return new EventListDTO() { Eventlist = query.ToList(), TotalRecords = totalRecords }; } }