Entity IQueryable with page, search and sort

Question

I'm sure this has been answered before, but can't seem to find it.

I'm looking for a simple download (or tutorial) for building an IQueryable that can be sorted, paged and searched.

e.g.

IQueryable<T> myThings , params[] searchKeyValuePairs, params[] orderKeyValuePairs, int pageSize, int pageNumber

Or something similar. Guessing it will be using Dynamic Linq.

My requirement is for a number of MVC3 views for broadly similar data. BA specified about 20 views of each of 10 data types...but MOST of these views are simply "All Today" and "Sorted by Cost" type views.

Data is from SQL or Oracle through EF4.

user1802006 · Accepted Answer · 2012-11-07 04:47:54Z

For paging:

public class PagingList<T> : List<T> { public int PageIndex { get; set; } public int PageSize { get; set; } public int TotalCount { get; set; } public int TotalPages { get; set; } public string pagerange { get; set; } public int pagingrange { get; set; } public PagingList(IQueryable<T> data, int page, int pagesize) { PageIndex = page; PageSize = pagesize; TotalCount = data.Count(); TotalPages = (int)Math.Ceiling(TotalCount /(double)PageSize); this.AddRange(data.Skip(PageIndex * PageSize).Take(PageSize)); } //public void GeneratePageRange() //{ // for (int i = 1; i <= TotalPages; i++) // { // pagingrange = i // } //} public bool HasPreviousPage { get { return (PageIndex > 0); } } public bool HasNextPage { get { return (PageIndex + 1 < TotalPages); } } }

Thanks - I actually have a paging and "single column" sorting base class at the moment - but it doesn't support searching, and especially not multi-column searching.
Note: This requires an OrderBy() statement before the Skip as it is an IQueryable

Rusty · Accepted Answer · 2012-11-07 04:52:46Z

For searching:

public static class DataYouWant { /// <summary> /// Function which returns the Griddata for json result of jqGrid /// </summary> public static GridData Getdata<T>(ObjectSet<T> baseList,int currentPage,int rowsPerPage, string sortcolumn, string sortord, string searchQuery, string searchColumns)where T: class { var query = baseList.OrderBy("it." + sortcolumn + " " + sortord); string strPredicate = string.Empty; dynamic searchvalue = searchQuery; if (!string.IsNullOrEmpty(searchColumns)) { var coltype = baseList.EntitySet.ElementType.Members[searchColumns].TypeUsage.EdmType; if (CheckIntType(coltype)) { strPredicate = "it." + searchColumns + " = @" + searchColumns; searchvalue = Convert.ToInt32(searchQuery); } else strPredicate = "CONTAINS(it." + searchColumns + ",@" + searchColumns + ")"; query = baseList.Where(strPredicate, new ObjectParameter(searchColumns, searchvalue)).OrderBy("it." + sortcolumn + " " + sortord); } var pageddata = new PagingList<T>(query, currentPage, rowsPerPage); return new GridData() { Page = pageddata.PageIndex + 1, Records = pageddata.TotalCount, Rows = pageddata, Total = pageddata.TotalPages, UserData = "ok" }; } /// <summary> /// Checks the EdmType and /// </summary> public static bool CheckIntType(EdmType objEdmType) { switch (objEdmType.Name) { case "Int32": return true;break; case "Int16": return true;break; case "Int64": return true; break; case "Decimal": return true;break; default: return false; break; } } }

@Rusty could you please tell me how to pass ObjectSet<T> baseList parameter to this method and what will be the type of Rows within GridData property

Collectives™ on Stack Overflow

Entity IQueryable with page, search and sort

2 Answers 2

2 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Related