1. Home
  2. Forum
  3. Blazor
  4. Filtering data with entity frqamework

Filtering data with entity frqamework

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;
            }
        }
    }

5 Replies 1 reply marked as answer
VN Vignesh Natarajan Syncfusion Team February 4, 2021 06:03 AM UTC

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.  
 
 
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 
 


Marked as answer
WM Walter Martin February 11, 2021 07:52 PM UTC

thanks this solution works


RS Renjith Singh Rajendran Syncfusion Team February 12, 2021 04:18 AM UTC

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 




MC Mason Channer replied to Renjith Singh Rajendran August 18, 2021 10:22 PM UTC

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


RS Renjith Singh Rajendran Syncfusion Team August 19, 2021 01:46 PM UTC

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 


Loader.
Up arrow icon