Filtering/Sorting/Searching not working if grid is configured with CRUD and UrlAdaptor

[Carlo]

I have a syncfusion grid in my view, whose definition starts as following:

@(Html.EJ().Grid<object>("Grid")

    .Datasource(ds =>

        ds.URL("GridController/DataSource")

        .InsertURL("GridController/PerformInsert")

        .UpdateURL("GridController/PerformUpdate")

        .Adaptor(AdaptorType.UrlAdaptor)

        )

        .Locale("it-IT")

        .AllowGrouping()

        .AllowSorting()

        .AllowFiltering()

        .AllowSearching()

        .AllowPaging()

        ...and then the rest..

in my controller I have this kind of DataSource (with skip and take, to enable dynamic page-based record retrival):

public ActionResult DataSource(DataManager dm)

        {

            dataresult.result = mylist.Skip(dm.skip).Take(dm.take);

            dataresult.count = mylist.Count();

    

            return Json(dataResult, JsonRequestBehavior.AllowGet);

        }

CRUD operations and paging work flawlessly. The issue I'm facing is that with this kind of setup (Url-adapter with partial data retrive through Datamanager and skip and take values) sorting, filtering and searching don't seem to be working at all. If I try to sort a column, the arrow properly changes direction but then nothing happens. If I try to set any kind of filter (even trying all the different filtering modes) nothing happens (also, if I select excel-like filtering, no values are loaded inside the filter window, I just see the loading circle). Any advice? Thanks for your kind help.

[Carlo]

I have done a couple of tests and it turns out that if I pass the datasource through ViewBag, without using the UrlAdaptor, filtering and sorting work properly...but I need them to be working in this setup which is using the UrlAdaptor (I need it to avoid stressing the server by loading only current page data). Any hint?

[Carlo]

After further reasearch, I think I have an idea of what's happening here: since I'm using UrlAdaptor, which retrives only currently needed data, so my GridController/Datasource method is called anytime the grid needs fresh data to perform a certain operation. This is of course the case with sorting and filtering -> when I do any of that, the GridController/Datasource method gets called and the "dm" item (of type DataManager), which is passed to the controller from the View, holds all the information about filtering and sorting. Now, is there an embedded way to "convert" this info in the datamanager into a NHibernate query? Is this datamanager object complying to some kind of standard which in someway could be understood by NHibernate, without this mechanism being implemented from scratch? Thanks

[Madhu Sudhanan P]

Hi Carlo,

Thanks for using Syncfusion products.

When using remote data binding in grid, the remote service should be configured to handle any grid actions such as filter, sort, paging etc. In your code, the controller`s action which returns grid data is not configured to handle any grid actions and hence the sorting and filtering were not worked.

        public ActionResult DataSource(DataManager dm)

        {

            //SIMPLY RETURNING take AND skip values, not handled other grid actions

            dataresult.result = mylist.Skip(dm.skip).Take(dm.take);

            dataresult.count = mylist.Count();

            return Json(dataResult, JsonRequestBehavior.AllowGet);
        }

To resolve this problem, we suggest you to return the data based on the grid action information which will be obtained in DataManager dm argument.

And you have mentioned as “is there an embedded way to "convert" this info in the datamanager into a NHibernate query?” in your query, we don’t have in-built way to convert the DataManager information to NHibernate query, but we can achieve this with the following steps.

1. Create a method to build NHibernate query from DataManager information. The ICriteria is used in the below code example.

private ICriteria ToQuery(DataManager dm, ISession session) {

            ICriteria criteria = session.CreateCriteria<EmployeeMap>();

           

            Count = session.CreateCriteria<EmployeeMap>().List().Count;

           

            Order order = null;      

           

            if (dm.Where != null && dm.Where.Count > 0) //Filtering

            {               

                 for (var i = 0; i < dm.Where.Count; i++) {

                     WhereFilter filter = dm.Where[i];

                     if (filter.IsComplex)

                         WhereFilter(criteria, filter); //Handle multiple column filter – REFER BELOW SAMPLE FOR CODE

                     if (filter.Operator == "equal")

                         criteria.Add(Restrictions.Eq(filter.Field, filter.value));

                     if (filter.Operator == "notequal")

                         criteria.Add(Restrictions.Not(Restrictions.Eq(filter.Field, filter.value)));

                     if (filter.Operator == "greaterthan")

                         criteria.Add(Restrictions.Ge(filter.Field, filter.value));

                     if (filter.Operator == "lessthan")

                         criteria.Add(Restrictions.Lt(filter.Field, filter.value));

                     if (filter.Operator == "greaterthanorequal")

                         criteria.Add(Restrictions.Ge(filter.Field, filter.value));

                     if (filter.Operator == "lessthanorequal")

                         criteria.Add(Restrictions.Le(filter.Field, filter.value));

                     if (filter.Operator == "startswith")

                         criteria.Add(Restrictions.Like(filter.Field, filter.value + "%"));

                     if (filter.Operator == "endswith")

                         criteria.Add(Restrictions.Like(filter.Field,  "%" + filter.value));

                     if (filter.Operator == "contains")

                         criteria.Add(Restrictions.Like(filter.Field, filter.value.ToString(), MatchMode.Anywhere));

                     Count = criteria.List().Count; //Required to show filter count

                }                

            }

          

            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting

            {

                for (var i = 0; i < dm.Sorted.Count; i++) {                  

                    order =  dm.Sorted[i].Direction == "ascending" ? Order.Asc(dm.Sorted[i].Name) : Order.Desc(dm.Sorted[i].Name);

                    criteria.AddOrder(order);

                }

            }

            if (dm.Skip != 0) //Paging

            {

                criteria.SetFirstResult(dm.Skip);

            }

            if (dm.Take != 0) //Paging

            {

                criteria.SetMaxResults(dm.Take);

            }

            return criteria;
        }

2. Now the above method can be used in the action which returns grid data as follows.

 

      //PROVIDE GRID DATASOURCE

        public ActionResult GetData(DataManager dm)

        {

            using (ISession session = AppSession.OpenSession())

            {

                list = ToQuery(dm,session).List<EmployeeMap>();

            }

            return Json(new { result = list, count = Count }, JsonRequestBehavior.AllowGet);
        }

For your convenience we have also created a simple grid sample with above code example and the same can be downloaded from the below location.

Sample Location: http://www.syncfusion.com/downloads/support/forum/120120/ze/UsingNHibernate_-_QueryFromDataManager110626930

In the above sample, the DataManager is used to build NHiberate query which will be used to retrieve appropriate data on grid action.The sample is created using Essential Studio v13.2.0.34.

Please refer the below link for available APIs in DataManager class.

http://www.syncfusion.com/kb/4300/server-side-api-for-datamanager-operations

Please let us know if you have any query.

Regards,
Madhu Sudhanan. P

[Carlo]

Thanks a lot. This saved me A LOT of work. You're always so helpful, I really appreciate that! Have a nice day!

[Madhu Sudhanan P]

Hi Carlo,

Thanks for the appreciation. We are happy that the requirement achieved.

Regards,
Madhu Sudhanan. P

[Jessica Rudman]

Good day

I am new to blazor and syncfusion,

I can use your ToQuery method code and it will work very well I think, however where does ICriteria come from?

I have an Interface ISession as I'm using the WebApiAdaptor but I don't have an interface called ICriteria.

I am using Visual Studio 2022.

Please help as I would like to use your code.

[Pon Selva Jeganathan]

Hi Jessica,

Before we start proceeding with your query, we request you to share with us the details below to provide you with the solution as early as possible. 

  

1. Confirm whether you are using the DataGrid or Treegrid component.
2. Detailed explanation of your requirement and share video demo of your requirement.
3. Complete code example. 

Regards,

Pon selva