Extending EF for dynamic where clues from grid

[Admir HodžićParticipant]

To be more clear what I want to try here is little story how I construct my code for generic sorting on server side.

Jqgrid when is set to server processing sends JToken object to controllers
That json contains sort order definition.

Inspired by this stackoverflow topic http://stackoverflow.com/questions/36298868/how-to-dynamically-order-by-certain-entity-properties-in-entityframework-7-core

I made server side sorting so easy.

First I create Extension class

using core.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;

namespace core
{
    public static class QueryableExtensions
    {
        public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, SortModel sortModel)
        {
            var expression = source.Expression;
            var parameter = Expression.Parameter(typeof(T), "x");
            var selector = Expression.PropertyOrField(parameter, sortModel.ColId);
            var method = string.Equals(sortModel.Sort, "desc", StringComparison.OrdinalIgnoreCase) ?  "OrderByDescending" :   "OrderBy";
            expression = Expression.Call(typeof(Queryable), method,
                new Type[] { source.ElementType, selector.Type },
                expression, Expression.Quote(Expression.Lambda(selector, parameter)));
            return  source.Provider.CreateQuery<T>(expression);
        }
    }
}

and also I made sorting model inside models

    public class SortModel
    {
        public string ColId { get; set; }
        public string Sort { get; set; }
    }

Whit this two classes I easly can do server side sorting for grids whic has columns same as EF object (tables)

[HttpPost]

        public string GetPageData(string jsonData)
        {
            JToken token = JObject.Parse(jsonData);

            List<JToken> filterGroups = token.SelectToken("filterGroups").Children().ToList();
            int pageSize = (int)token.SelectToken("pagesize");
            int pageNum = (int)token.SelectToken("pagenum");
            string sortField = (string)token.SelectToken("sortdatafield");
            string sortOrder = (string)token.SelectToken("sortorder");
            
                SortModel sm = new SortModel();

            sm.Sort = string.IsNullOrEmpty(sortOrder) || sortOrder =="asc" ? "asc" : "desc";
            sm.ColId = string.IsNullOrEmpty(sortOrder) || string.IsNullOrEmpty(sortField) ? "Id" : sortField;

            List<SortModel> sml = new List<SortModel>();
            sml.Add(sm);
            var docs = _context.MjeKlistDoc
                .OrderBy(sml)
                .Skip(pageNum * pageSize).Take(pageSize)
                .ToList();
            core.Models.MjenjacnicaViewModels.JSONData data = new JSONData();
            data.TotalRecords = _context.MjeKlistDoc.Count() ;
            data.Docs = docs;
            return JsonConvert.SerializeObject(data);
        }

This is super easy implemented server side sorting and paging using EF7.

Now I want to try something similar for filters.
Before I start reinventing wheel I wondering is there someone who already did this.
Is there a sample code that implement dynamic filtering (creating where clue) for filters sent by grid to controllers.

[Peter StoevKeymaster]

Hi adopilot,

The Grid’s Filtering example: http://aspcore.jqwidgets.com/mvc/TagHelpers/GridFilter/light, sends the Grid’s filters to controllers and the controller returns the result back to the Grid. The Grid then gets that data and re-renders itself with the received data that matches the filtering expressing.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

[Author]

Posts