1. Home
  2. Forum
  3. Blazor
  4. Grid with EF Core not filtering

Grid with EF Core not filtering

Grid is defined to use SfDataManager

<SfGrid TValue="Person" ID="StudentGrid" @ref="DefaultGrid" AllowPaging="true" AllowSorting="true" AllowExcelExport="true" AllowReordering="true"
        ShowColumnMenu="true" ColumnMenuItems="@menuItems" ShowColumnChooser="true" RowHeight="70" Height="100%" Toolbar="@toolbarItems">
<SfDataManager Url="api/people" Adaptor="Adaptors.WebApiAdaptor"/>
<GridEvents OnRecordDoubleClick="RecordDoubleClickHandler" TValue="Person" OnActionFailure="@ActionFalure"></GridEvents>
<GridPageSettings PageSize="50" PageSizes="new int[] {50, 100, 150, 200}"></GridPageSettings>

Controller is as follows:

[HttpGet]
[ProducesResponseType(200, Type = typeof(IQueryable<PersonDto>))]

public IActionResult GetPeople()
{
    var objList = _peopleRepository.GetPeople();

    var objDto = new List<PersonDto>();
    foreach (var obj in objList)
    {
        objDto.Add(_mapper.Map<PersonDto>(obj));
    }

    var queryableDto = objDto.AsQueryable();
    
    var count = objDto.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]) : objDto.Count();
        
        return new JsonResult(new {Items = queryableDto.Skip(skip).Take(top), Count = count});
    }
    else if (queryString.Keys.Contains($"$count"))
    {
        return new JsonResult(new {Count = count});
    }

    return Ok(queryableDto);
}

When I enter a search in the search box, in Postman I can see the request sent to the endpoint. Eg:
(substringof(%27smith%27,Id))%20or%20(substringof(%27smith%27,FirstName))%20or%20(substringof(%27smith%27,FirstName))%20or%20(substringof(%27smith%27,LastName))%20or%20(substringof(%27smith%27,Telephones))%20or%20(substringof(%27smith%27,EmailAddresses))%20or%20(substringof(%27smith%27,Telephones))%20or%20(substringof(%27smith%27,BirthDate))%20or%20(substringof(%27smith%27,BirthDate))%20or%20(substringof(%27smith%27,DateStarted))%20or%20(substringof(%27smith%27,DateLastAttended))

The entire set of people is returned.

What am I missing?




3 Replies 1 reply marked as answer
RN Rahul Narayanasamy Syncfusion Team March 8, 2021 01:00 PM UTC

Hi Judi, 

Greetings from Syncfusion. 

Query: Grid with EF Core not filtering 

We have validated your query and we might suspect that you are facing complexities while performing data operation(sorting, paging, filtering) in WebApiAdaptor.  

When using the WebAPI services, you need to handle these data operation actions(search/filter/sort/paging actions) at server side based on the querystring you get using the “Request.Query” from the request in Get method.  

So we suggest you to ensure to handle the filtering/sorting actions at server side, based on the “Request.Query” you get from the request to perform filter/sort actions when bind WebApi services to Grid.  

[HttpGet] 
        public async Task<object> Get(int? code) 
        { 
            if (order.Count == 0) 
            { 
                BindDataSource(); 
            } 
            var data = order.AsQueryable(); 
            var queryString = Request.Query; 
            string grid = queryString["ej2grid"]; 
            string sort = queryString["$orderby"];   //sorting       
            string filter = queryString["$filter"]; 
            string auto = queryString["$inlineCount"]; 
            if (filter != null) // to handle filter opertaion 
            { 
                if (filter.Contains("substring"))//searching  
                { 
                    . . . 
                } 
                else 
                { 
                    var newfiltersplits = filter; 
                    . . . 
 
                    switch (filterfield) 
                    { 
                        case "OrderID": 
                            data = (from cust in data 
                                    where cust.OrderID.ToString() == filtervalue.ToString() 
                                    select cust); 
                            break; 
                        . . . 
                    } 
                } 
            } 
            . . . 
            if (queryString.Keys.Contains("$inlinecount")) 
            { 
                . . . 
                 return new { Items = data.Skip(skip).Take(top), Count = count };                
            } 
            else 
            { 
                return data; 
            } 
        } 

Here, we have prepared a simple sample for performing data operations(filtering, sorting) in WebApiAdaptor. Find the below sample for your reference. 


Please let us know if you have any concerns. 

Regards, 
Rahul 


Marked as answer
JS Judi Smith March 10, 2021 02:29 PM UTC

Rahul, that was very helpful. Just what I needed. Thank you.

RN Rahul Narayanasamy Syncfusion Team March 11, 2021 05:12 AM UTC

Hi Judi, 
 
Thanks for the update. 
 
We are happy to hear that the provided solution was helpful to achieve your requirement. Please get back to us if you need further assistance. 
 
Regards, 
Rahul  


Loader.
Up arrow icon