使用Entity Framework对通用列表进行动态过滤，排序和分页 [英] Dynamic filtering, sorting and paging on generic list with Entity Framework

问题描述

我在MVC4应用中使用jQuery数据表( http://www.datatables.net )，并且您可能知道此表允许服务器端处理.我将在与多个控制器绑定的多个视图中使用该表，因此我想实现一种通用的方法来归档，排序和分页数据，而无需为每个控制器编写方法.如果我要这样做，它们看起来都一样，但是它们将针对数据库中的另一个实体，并在不同的列上进行文本过滤和排序.这是我今天要做的:

I am using jQuery datatable (http://www.datatables.net) in my MVC4 app and as you may know this table allows server side processing. I am going to use the table in multiple views tied to multiple controllers so I'd like to implement a generic way to filer, sort and page data without the need to write a method for each controller. If I were to do that, they would all look the same but they would target a different entity from the database and be doing textual filtering and sorting on different columns. Here what I have to do today:

    public virtual ActionResult AjaxHandler(jQueryDataTableParamModel param)
    {
        var myProducts = _productRepository.Products;
        IEnumerable<Product> filteredProducts = myProducts;

        // Filtering
        if (!string.IsNullOrEmpty(param.sSearch))
        {
            var searchTermLower = param.sSearch.Trim().ToLower();
            filteredProducts = filteredProducts
                     .Where(c => c.Title.Contains(param.sSearch)
                                 ||
                      c.Manufacturer.ManufacturerName.ToLower().Contains(searchTermLower)
                                 ||
                      c.Category.CategoryTitle.ToLower().Contains(searchTermLower)
                                 ||
                      c.Size.Title.ToLower().Contains(searchTermLower)
                                 ||
                      c.Price.ToString("C").Contains(searchTermLower));
        }

        // Sorting
        var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
        var sortDirection = Request["sSortDir_0"];
        if (sortColumnIndex == 0)
        {
            filteredProducts = sortDirection == "asc" ? filteredProducts.OrderBy(x => x.CreatedDate) : filteredProducts.OrderByDescending(x => x.CreatedDate);
        }
        else if (sortColumnIndex == 1)
        {
            filteredProducts = sortDirection == "asc" ? filteredProducts.OrderBy(x => x.Title) : filteredProducts.OrderByDescending(x => x.Title);
        }
        else if (sortColumnIndex == 2)
        {
            filteredProducts = sortDirection == "asc" ? filteredProducts.OrderBy(x => x.Manufacturer.ManufacturerName) : filteredProducts.OrderByDescending(x => x.Manufacturer.ManufacturerName);
        }
        else if (sortColumnIndex == 3)
        {
            filteredProducts = sortDirection == "asc" ? filteredProducts.OrderBy(x => x.Size.Title) : filteredProducts.OrderByDescending(x => x.Size.Title);
        }
        else if (sortColumnIndex == 4)
        {
            filteredProducts = sortDirection == "asc" ? filteredProducts.OrderBy(x => x.Category.CategoryTitle) : filteredProducts.OrderByDescending(x => x.Category.CategoryTitle);
        }
        else if (sortColumnIndex == 4)
        {
            filteredProducts = sortDirection == "asc" ? filteredProducts.OrderBy(x => x.Price) : filteredProducts.OrderByDescending(x => x.Price);
        }

        // Paging
        var displayedProducts = filteredProducts.Skip(param.iDisplayStart).Take(param.iDisplayLength);
        var result = from c in displayedProducts
                     select new[] { c.ProductId.ToString(CultureInfo.InvariantCulture), c.CreatedDate.ToString("G"), c.Title, c.Manufacturer.ManufacturerName, c.Size.Title, c.Category.CategoryTitle, c.Price.ToString("C") };
        return Json(new
        {
            sEcho = param.sEcho,
            iTotalRecords = myProducts.Count(),
            iTotalDisplayRecords = filteredProducts.Count(),
            aaData = result
        }, JsonRequestBehavior.AllowGet);
    }

我尝试了几种方法来使这种通用方法都不能完全起作用-有些原因是因为我对所有列进行了过滤，而另一些原因是出于其他原因.我希望有一种更好的方法可以做到这一点，所以我可以只传递列或选择列的函数并使之起作用.

I tried several things to make this generic none of which worked completely - some because of the fact that I filter on all columns and others for other reasons. I am hoping there is a better way to do this so I can just pass columns or functions that select columns instead and have it work.

推荐答案

    /// Used to indicate the sort order.
    /// </summary>
    public enum SortOrder
    {
        /// <summary>
        /// Indicates whether the order is ascending.
        /// </summary>
        Ascending = 0,

        /// <summary>
        /// Indicates whether the order is descending.
        /// </summary>
        Descending = 1,

        /// <summary>
        /// Indicates whether the order is neutral.
        /// </summary>
        Neutral = 2
    }

    /// <summary>
    /// DTO for sorting and paging
    /// </summary>
    public class FilterProperties
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="FilterProperties"/> class.
        /// </summary>
        public FilterProperties()
        {
            // parameterless constructor
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="FilterProperties"/> class.
        /// </summary>
        /// <param name="sortColumnName">column name of sorting.</param>
        /// <param name="sortOrder">order of the sorting</param>
        /// <param name="pageSize">items in the page.</param>
        /// <param name="filterValue">value of the filter.</param>
        /// <param name="pageselected">current page selected.</param>
        public FilterProperties(string sortColumnName, SortOrder sortOrder, int pageSize, string filterValue = "All", int pageselected = 0)
        {
            this.SortColumnName = sortColumnName;
            this.SortOrder = sortOrder;
            this.PageSize = pageSize;
            this.PageSelected = pageselected;
            this.FilterValue = filterValue;
        }

        /// <summary>
        /// Gets or sets the filter column name
        /// </summary>
        public string FilterColumnName { get; set; }

        /// <summary>
        /// Gets or sets the filter value
        /// </summary>
        public string FilterValue { get; set; }

        /// <summary>
        /// Gets or sets the sort field name.
        /// </summary>
        public string SortColumnName { get; set; }

        /// <summary>
        /// Gets or sets the sort direction.
        /// </summary>
        public SortOrder SortOrder { get; set; }

        /// <summary>
        /// Gets or sets the page size.
        /// </summary>
        [Obsolete("Use RecordCount instead (remove this)")]
        public int PageSize { get; set; }

        /// <summary>
        /// Gets or sets the current page index.
        /// </summary>
        [Obsolete("Use StartRecord instead (remove this)")]
        public int PageSelected { get; set; }

        /// <summary>
        /// Gets or sets the zero-based index of the starting record to return in
        /// the filtered result set.         
        /// </summary>
        public int StartRecord { get; set; }

        /// <summary>
        /// Gets or sets the number of records to return in the filtered result set.         
        /// </summary>
        public int RecordCount { get; set; }
    }
}

Extension Method for sorting and paging
public static class SortingAndPagingHelper
    {
        /// <summary>
        /// Returns the list of items of type on which method called
        /// </summary>
        /// <typeparam name="TSource">This helper can be invoked on IEnumerable type.</typeparam>
        /// <param name="source">instance on which this helper is invoked.</param>
        /// <param name="sortingModal">Page no</param>
        /// <returns>List of items after query being executed on</returns>
        public static IEnumerable<TSource> SortingAndPaging<TSource>(
            this IEnumerable<TSource> source, 
            FilterProperties sortingModal)
        {
            // Is there any sort column supplied?
            IEnumerable<TSource> data = source;
            if (!string.IsNullOrEmpty(sortingModal.SortColumnName))
            {
                // Gets the coloumn name that sorting to be done on
                PropertyInfo propertyInfo =
                    data.GetType().GetGenericArguments()[0].GetProperty(sortingModal.SortColumnName);

                // Define the sorting function
                data = sortingModal.SortOrder == SortOrder.Ascending
                    ? data.OrderBy(x => propertyInfo.GetValue(x, null))
                    : data.OrderByDescending(x => propertyInfo.GetValue(x, null));
            }

            // Apply paging to (sorted) data
            return sortingModal.RecordCount > 0
                ? data.Skip(sortingModal.StartRecord).Take(sortingModal.RecordCount)
                : data.Skip((sortingModal.PageSelected - 1) * sortingModal.PageSize).Take(sortingModal.PageSize);
        }
    }

这篇关于使用Entity Framework对通用列表进行动态过滤，排序和分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！