Filtering data with entity frqamework

[Walter Martin]

I'm using Blazor server 18.4 and I need to read data from SqlServer table to fill a sfgrid using EntityFramworkCore 

I used your sample below from the documentation to create the controller for the datamanager and everything is working fine but how can I handle the several filter types provided by the sfgrid features? I don't find any sample for that

  [Route("api/[controller]")]
    [ApiController]
    public class DefaultController : ControllerBase
    {
        OrderDataAccessLayer db = new OrderDataAccessLayer();
        [HttpGet]
        public object Get()
        {
            IQueryable<Order> data = db.GetAllOrders().AsQueryable();
            var count = data.Count();
            var queryString = Request.Query;
            if (queryString.Keys.Contains("$inlinecount"))
            {
                StringValues Skip;
                StringValues Take;
                int skip = (queryString.TryGetValue("$skip", out Skip)) ? Convert.ToInt32(Skip[0]) : 0;
                int top = (queryString.TryGetValue("$top", out Take)) ? Convert.ToInt32(Take[0]) : data.Count();
                return new { Items = data.Skip(skip).Take(top), Count = count };
            }
            else
            {
                return data;
            }
        }
    }

[Vignesh Natarajan]

Hi Walter,  

 

Thanks for contacting Syncfusion support.  

 

Query: “I used your sample below from the documentation to create the controller for the datamanager and everything is working fine but how can I handle the several filter types provided by the sfgrid features?” 

 

We have analyzed your query and we would like to inform that while using WebAPI adaptor, we will send only the current action details like (filtering, sorting) to server as a querystring. Based on the querystring value, we have perform these actions on our own. Kindly refer the below code example to perform the filtering action using Excel filter type. 

 

[HttpGet] 

       public object Get() 

       { 

           try 

           { 

               Microsoft.AspNetCore.Http.IQueryCollection queryString = Request.Query; 

               if (queryString == null) 

                   return null; 

               IQueryable<Order> dataSource = db.GetAllOrders(); 

               int countAll = dataSource.Count();  

               StringValues sSkip, sTake, sFilter, sSort; 

               string filter = (queryString.TryGetValue("$filter", out sFilter)) ? sFilter[0] : null;    //filter query 

               string sort = (queryString.TryGetValue("$orderby", out sSort)) ? sSort[0] : null;         //sort query  

               List<DynamicLinqExpression.Filter> listFilter = ParsingFilterFormula.PrepareFilter(filter); 

               if (listFilter.Count() > 0) 

               { 

                   Expression<Func<Order, bool>> deleg = DynamicLinqExpression.ExpressionBuilder.GetExpressionFilter<Order>(listFilter); 

                   dataSource = dataSource.Where(deleg); 

               }                

               if (queryString.Keys.Contains("$inlinecount")) 

                   return new { Items = dataSource, Count = countFiltered }; 

               else 

                   return dataSource; 

           } 

           catch (Exception ex) 

           { 

               return null; 

           } 

       } 

 

Kindly refer the below sample to filter the data in WebAPI controller using Excel filter.  

 

Sample: https://www.syncfusion.com/downloads/support/forum/162148/ze/EFHostedCRUDGrid-ExcelFilter1005570674  

 

Note: Change the Northwnd.MDF file connectionstring in OrderContext.cs file in shared project 

 

Please get back to us if you have further queries.  

 

Regards, 

Vignesh Natarajan 

 

[Walter Martin]

thanks this solution works

[Renjith Singh Rajendran]

Hi Walter, 

We are glad to hear that your requirement has been achieved. 

Please get back to us if you need further assistance. 

Regards, 

Renjith R 

[Mason Channer]

What about the search box, this solution does not work when adding the search.

[Renjith Singh Rajendran]

Hi Mason, 

When using the WebAPI services, you need to handle these data operation actions(sort, filter, search, paging etc.) at controller side based on the “Request.Query” you get from corresponding action requests. When performing search action in grid, the $filter query will be send to webapi controller. So, based on this $filter query send to WebApi controller for search action, we suggest you to handle the $filter query for search in the HttpGet method, as like what we have done for other data operations(like sort, filter, paging etc.).  

Please refer the codes below, 

 

        [HttpGet] 

        public object Get() 

        { 

            try 

            { 

                Microsoft.AspNetCore.Http.IQueryCollection queryString = Request.Query; 

                if (queryString == null) 

                    return null; 

                ...  

                StringValues sSkip, sTake, sFilter, sSort; 

                ... 

                //handle this filter query  

                string filter = (queryString.TryGetValue("$filter", out sFilter)) ? sFilter[0] : null;    //filter or search query 

               string sort = (queryString.TryGetValue("$orderby", out sSort)) ? sSort[0] : null;         //sort query

               ... 

        } 

 

 

 

Please get back to us if you need further assistance. 

Regards, 

Renjith R