We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

ASP.NET Core EJ2 Grid with WebApiAdaptor - server side

Hi,

I have been going through forum for last 3-4 hours on a solution for filtering with "<e-grid-filtersettings type="Menu"></e-grid-filtersettings>" on server side.
Unfortunately most of the sample is missing the filter server side using WebApiAdaptor.

I found out the Request.Query returned by filter is not of same format, so using filter.Split on 'eq' amd 'ne' required bit coding.

startswith(tolower(Name),'1')
endswith(tolower(Name),'1')
substringof('1',tolower(Name)) should be substringof(tolower(Name), '1')
tolower(Name) eq '1' should be eq(tolower(Name), '1')
tolower(Name) ne '1' should be ne(tolower(Name), '1') , I think this should make split code must easier.

Is there any easy way to do server side filtering with WebApiAdaptor? please provide a demo.

So far my code looks like this, but stuck at filtering...
.cshtml
<ejs-grid id="Grid1" allowFiltering="true" allowSorting="true" allowPaging="true">
                    <e-grid-filtersettings type="Menu"></e-grid-filtersettings>
                    <e-data-manager url="/api/admin" adaptor="WebApiAdaptor" crossdomain="true"></e-data-manager>
                    <e-grid-columns>
                        <e-grid-column field="Id" type="number" width="80" allowFiltering="false"></e-grid-column>
                        <e-grid-column field="Name" type="string" width="200"></e-grid-column>
                        <e-grid-column field="CreatedBy" type="string" width="100" allowFiltering="false"></e-grid-column>
                        <e-grid-column field="CreatedDate" customFormat="@(new {type = "date", format = "dd/MM/yyyy"})" type="date" width="100" allowFiltering="false"></e-grid-column>
                        <e-grid-column field="ModifiedBy" type="string" width="100" allowFiltering="false"></e-grid-column>
                        <e-grid-column field="ModifiedDate" customFormat="@(new {type = "date", format = "dd/MM/yyyy"})" type="date" width="100" allowFiltering="false"></e-grid-column>
                    </e-grid-columns>
                </ejs-grid>

Code
        // GET api/admin
        [HttpGet]
        public object GetApiRoles()
        {
            //--filter--startswith(tolower(Name),'1')
            //--filter--endswith(tolower(Name),'1')
            //--filter--substringof('1',tolower(Name))
            //--filter--tolower(Name) eq '1'
            //--filter--tolower(Name) ne '1'

            IQueryable<Apiroles> data = _context.Apiroles;
            var queryString = Request.Query;
            var skip = Convert.ToInt32(queryString["$skip"]);
            var take = Convert.ToInt32(queryString["$top"]);
            string filter = queryString["$filter"];

            if (filter != null)
            {
                string key;
                int keys = filter.Split(new string[] { "'" }, StringSplitOptions.None).Count();
                if (keys > 1)
                {
                    string[] key1 = filter.Split(new string[] { "'" }, StringSplitOptions.None);
                    data = data.Where(p => p.Name.Contains(key1[1].ToString()));
                }
                else
                {
                    string[] str = filter.Split(new string[] { " " }, StringSplitOptions.None);
                    var val = str[2];
                    data = data.Where(fil => fil.Name ==  val || fil.Name ==  val);
                }
            }
            return take != 0 ? new { Items = data.Skip(skip).Take(take).ToList(), Count = data.Count() } : new { Items = data.ToList(), Count = data.Count() };
        }




11 Replies

PS Pavithra Subramaniyam Syncfusion Team May 6, 2019 07:56 AM

Hi shimaz, 
 
Thanks for contacting Syncfusion support. 
 
For WebApiAdaptor we need to handle the Grid actions such as filtering, paging, sorting in server side by manually. We have prepared a simple sample for server side Menu filtering with WebApiAdaptor. Please refer to the below code example and sample link for more information. 
 
[controller.cs] 
  public class OrdersController : Controller 
    { 
        // GET: api/Orders 
        [HttpGet] 
 
        public object Get() 
        { 
            var queryString = Request.Query; 
            var data = OrdersDetails.GetAllRecords().ToList(); 
            string filter = queryString["$filter"]; 
            string auto = queryString["$inlineCount"]; 
            // filtering 
            if (filter != null) 
            { 
                var newfiltersplits = filter; 
                var filtersplits = newfiltersplits.Split('(', ')', ' '); 
                var filterfield = filtersplits[1]; 
                var filtervalue = filtersplits[3]; 
 
                if (filtersplits.Length == 5) 
                { 
                    if (filtersplits[1] == "tolower") 
                    { 
                        filterfield = filter.Split('(', ')', '\'')[2]; 
                        filtervalue = filter.Split('(', ')', '\'')[4]; 
                    } 
                } 
                if (filtersplits.Length != 5) 
                { 
                    filterfield = filter.Split('(', ')', '\'')[3]; 
                    filtervalue = filter.Split('(', ')', '\'')[5]; 
                } 
                switch (filterfield) 
                { 
                    case "CustomerID": 
                        data = (from cust in data 
                                where cust.CustomerID.ToLower().StartsWith(filtervalue.ToString()) 
                                select cust).ToList(); 
                        break; 
                } 
            } 
            // paging 
            int skip = Convert.ToInt32(queryString["$skip"]); 
            int take = Convert.ToInt32(queryString["$top"]); 
            if (auto == null) 
            { 
                return new { Items = data }; 
            } 
            else 
            { 
                return take != 0 ? new { Items = data.Skip(skip).Take(take).ToList(), Count = data.Count() } : new { Items = data, Count = data.Count() }; 
            } 
        } 
 
 
Regards, 
Pavithra S. 



AS ashimaz May 7, 2019 01:14 AM

Hi Pavithra,

Thank you for replying, my code is working now.

IQueryable < Apiroles > data = _context.Apiroles;

var queryString = Request.Query;
var skip = Convert.ToInt32(queryString["$skip"]);
var take = Convert.ToInt32(queryString["$top"]);
string filter = queryString["$filter"];
string sort = queryString["$orderby"];

if (filter != null) {
 var newfiltersplits = filter;
 var filtersplits = newfiltersplits.Split('(', ')', ' ');
 var filterfield = filtersplits[1];
 var filtervalue = filtersplits[3];

 if (filter.Contains("eq") || filter.Contains("ne")) {
  filterfield = filter.Split('(', ')', '\'')[2];
  filtervalue = filter.Split('(', ')', '\'')[4];
 }

 if (filter.Contains("substringof")) {
  filterfield = filter.Split('(', ')', '\'')[5];
  filtervalue = filter.Split('(', ')', '\'')[3];
 }

 if (filter.Contains("startswith") || filter.Contains("endswith")) {
  filterfield = filter.Split('(', ')', '\'')[3];
  filtervalue = filter.Split('(', ')', '\'')[5];
 }

 switch (filterfield) {
  case "Name":
   if (filter.Contains("startswith")) {
    data = data.Where(x => x.Name.StartsWith(filtervalue.ToString()));
   } else if (filter.Contains("endswith")) {
    data = data.Where(x => x.Name.EndsWith(filtervalue.ToString()));
   } else if (filter.Contains("substringof")) {
    data = data.Where(x => x.Name.Contains(filtervalue.ToString()));
   } else if (filter.Contains("eq")) {
    data = data.Where(x => x.Name == filtervalue.ToString());
   } else if (filter.Contains("ne")) {
    data = data.Where(x => x.Name != filtervalue.ToString());
   }
   break;
 }
}

if (sort != null) {
 int keys = sort.Split(new [] {
  " "
 }, StringSplitOptions.None).Count();
 if (keys > 1) {
  string[] key1 = sort.Split(new [] {
   " "
  }, StringSplitOptions.None);
  // Required linq dynamic
  data = data.OrderBy(key1[0] + " descending");

 } else {
  // Required linq dynamic
  data = data.OrderBy(sort);
 }
}

return take != 0 ? new {
 Items = data.Skip(skip).Take(take).ToList(), Count = data.Count()
} : new {
 Items = data.ToList(), Count = data.Count()
};


PS Pavithra Subramaniyam Syncfusion Team May 7, 2019 03:57 AM

Hi shimaz,  

Thanks for your update. 

We are happy to hear your issue has been resolved. 

Please contact us if you need any further assistance. As always, we will be happy to assist you.  

Regards,  
Pavithra S. 



GH Gabor Horovitz June 6, 2019 01:59 PM

I can not believe we are implementing this in custom code in 2019, version 17.  (filter, sort, take, skip)

To parse what grid sends should be supported in server side out the box (as a part of the support lib), to prevent reinvent the wheel, and copy and paste. This is so typical repeated for a paging grid. Kendo for ASP has it since more than 5 years... just one line call which takes the request parameter and outputs the IQueryable. Btw, calling the ToList() on IQueryable and after then applying the Skip and Take is a serious mistake, and all Syncfusion sample uses it.


AS ashimaz June 6, 2019 03:08 PM

Yes, going forward this has to be implemented by syncfusion team.
For now my code works.


PS Pavithra Subramaniyam Syncfusion Team June 10, 2019 01:25 AM

Hi shimaz, 
 
Thanks for your updates Gabor and shimaz. 
 
In Essential JavaScript 2, We already have an option called UrlAdaptor  which has an option to perform server side paging, filtering, sorting actions with inbuilt DataOperation and DataMangerRequest. You can use the UrlAdaptor of DataManager when binding data source from remote data. In the initial load of grid, data are fetched from remote data and bound to the grid using url property of DataManager. You handled paging, filtering actions like in the below code example. We have prepared a sample based on your requirement. Please find the below code example and sample for your reference. 
 
[code example] 
<ejs-grid id="GridOverview" allowFiltering="true" allowPaging="true"> 
    <e-data-manager url="/Home/UrlDataSource" adaptor="UrlAdaptor"></e-data-manager> 
    <e-grid-pagesettings pageSize="4"></e-grid-pagesettings> 
    <e-grid-filtersettings type="Menu"></e-grid-filtersettings> 
    <e-grid-columns> 
        <e-grid-column field="OrderID" headerText="Order ID" isPrimaryKey="true" textAlign="Right" width="100"></e-grid-column> 
        <e-grid-column field="CustomerID" headerText="Customer ID" type="string" width="120"></e-grid-column> 
        <e-grid-column field="Freight" headerText="Freight" textAlign="Right" format="C2" editType="numericedit" width="120"></e-grid-column> 
        <e-grid-column field="ShipCountry" headerText="Ship Country" width="150"></e-grid-column> 
    </e-grid-columns> 
</ejs-grid> 
 
... 
public class HomeController : Controller 
    { 
       [HttpPost] 
        public IActionResult UrlDatasource([FromBody]DataManagerRequest dm) 
        { 
            IEnumerable DataSource = OrdersDetails.GetAllRecords(); 
            DataOperations operation = new DataOperations(); 
            if (dm.Search != null && dm.Search.Count > 0) 
            { 
                DataSource = operation.PerformSearching(DataSource, dm.Search);  //Search 
            } 
            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting 
            { 
                DataSource = operation.PerformSorting(DataSource, dm.Sorted); 
            } 
            if (dm.Where != null && dm.Where.Count > 0)  
            { 
                DataSource = operation.PerformFiltering(DataSource, dm.Where, dm.Where[0].Operator);     //Filtering 
            } 
            int count = DataSource.Cast<OrdersDetails>().Count(); 
            if (dm.Skip != 0) 
            { 
                DataSource = operation.PerformSkip(DataSource, dm.Skip);   //Paging 
            } 
            if (dm.Take != 0) 
            { 
                DataSource = operation.PerformTake(DataSource, dm.Take); 
            } 
            return dm.RequiresCounts ? Json(new { result = DataSource, count = count }) : Json(DataSource); 
        } 
        ... 
   } 
... 
 
 
 
Please get back to us if you need further assistance. 
 
Regards, 
Pavithra S. 
 



TO Tomasz December 7, 2020 12:04 PM

And how to use it to filter data on database side? 
In Core we usually use EntityFramework repository. 
How to use Grid control with filtering, without loading thousands, or milions record from database, only to apply filtering?


AS ashimaz December 7, 2020 12:44 PM

Hi, have you tried using above code, it works for me.

Enable AllowFiltering="true" and use
<GridFilterSettings Type="Syncfusion.Blazor.Grids.FilterType.Menu" />


RR Rajapandi Ravi Syncfusion Team December 9, 2020 06:07 AM

Hi Tomasz, 

Thanks for the update 

We have analyzed your query and we could see that you like to use the Entity framework. Based on your query we have prepared a sample and achieved your requirement. Please refer the below code example and sample for more information. 

Index.cshtml 
<ejs-grid id="Grid" allowPaging="true" allowFiltering="true" toolbar="@(new List<string>() {  "Add", "Edit", "Delete", "Update", "Cancel" })"> 
    <e-grid-filterSettings type="Menu"></e-grid-filterSettings> 
    <e-grid-editSettings allowAdding="true" allowDeleting="true" allowEditing="true" mode="Normal" showConfirmDialog="true" showDeleteConfirmDialog="true"></e-grid-editSettings> 
    <e-data-manager url="/Home/TelecomDataSource" insertUrl="/Home/Insert" updateUrl="/Home/Update" removeUrl="/Home/Delete" adaptor="UrlAdaptor"></e-data-manager> 
    <e-grid-columns> 
        .  .  .  .  .  .  . 
        .  .  .  .  .  .  . 
   </e-grid-columns> 
</ejs-grid> 
 

HomeController.cs 

public ActionResult TelecomDataSource([FromBody]DataManagerRequest dataManager) 
        { 
            IEnumerable data = _context.Orders.ToList(); 
            DataOperations operation = new DataOperations(); 
            if (dataManager.Search != null && dataManager.Search.Count > 0) 
            { 
                data = operation.PerformSearching(data, dataManager.Search);  //Search  
            } 
            if (dataManager.Sorted != null && dataManager.Sorted.Count > 0) //Sorting  
            { 
                data = operation.PerformSorting(data, dataManager.Sorted); 
            } 
            if (dataManager.Where != null && dataManager.Where.Count > 0) 
            { 
                data = operation.PerformFiltering(data, dataManager.Where, dataManager.Where[0].Operator);     //Filtering  
            } 
            int count = data.Cast<Orders>().Count(); 
            if (dataManager.Skip != 0) 
            { 
                data = operation.PerformSkip(data, dataManager.Skip); 
            } 
            if (dataManager.Take != 0) 
            { 
                data = operation.PerformTake(data, dataManager.Take); 
            } 
            return dataManager.RequiresCounts ? Json(new { result = data, count = count }) : Json(data); 
        } 
 


Regards,
Rajapandi R 



TO Tomasz October 6, 2021 03:13 PM

Hi


In solution above, in line  

IEnumerable data = _context.Orders.ToList(); 

you are loading all data from database to server memory

What if there will be millions of records?

Can you filter data without loading it?

In EntityFramework, by using operations on IQueryable, not ToList(), so the query can be processed by database, not on www server?



RR Rajapandi Ravi Syncfusion Team October 7, 2021 10:32 AM

Hi Tomasz, 

Thanks for the update 

Based on your query you need to load the large amount of data in the Grid. So, we would like to suggest the “IQueryable” on handling the large data on the grid and use AllowPaging property in Grid to bind large data from a database, hence now the requested page data will alone be fetched and given to the grid component. Please refer the below code example, sample and Kb for your reference 

Code Example : 
Index.cshtml 
<ejs-grid id="Grid" height="273" allowPaging="true" allowFiltering="true"> 
    <e-data-manager url="/Home/UrlDataSource" adaptor="UrlAdaptor"></e-data-manager> 
    <e-grid-filterSettings type="Excel"></e-grid-filterSettings> 
    <e-grid-columns> 
      <e-grid-column field="CustomerID" headerText="Customer ID" isPrimaryKey="true" textAlign="Right" width="100"></e-grid-column> 
      <e-grid-column field="ContactName" headerText="Contact Name" type="string" width="120"></e-grid-column> 
      <e-grid-column field="ContactTitle" headerText="ContactTitle" textAlign="Right" width="120"></e-grid-column> 
      <e-grid-column field="City" headerText="Ship City" width="150"></e-grid-column> 
    </e-grid-columns> 
  </ejs-grid> 

We have added skip and take for server-side pagination 

[Homecontroller.cs]  
public IActionResult UrlDatasource([FromBody]DataManagerRequest dm) 
        { 
            EFDataContext db = new EFDataContext(); 
            IQueryable<Customers> DataSource = db.customers; 
            QueryableOperation operation = new QueryableOperation(); 
            if (dm.Where != null && dm.Where.Count > 0) //Filtering 
            { 
            DataSource = operation.PerformFiltering(DataSource, dm.Where, dm.Where[0].Operator); 
            } 
            int count = DataSource.Count(); 
            if (dm.Skip != 0) 
            { 
                DataSource = operation.PerformSkip(DataSource, dm.Skip);   //Paging 
            } 
            if (dm.Take != 0) 
            { 
                DataSource = operation.PerformTake(DataSource, dm.Take); 
            } 
            return dm.RequiresCounts ? Json(new { result = DataSource, count = count }) : Json(DataSource); 
        } 


This below article explains how to perform the data operation on the server-side for IQueryable data. we suggest you follow the below KB for reference. 


Please get back to us if you need further assistance with this. 

Regards, 
Rajapandi R 


Loader.
Live Chat Icon For mobile
Up arrow icon