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. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

How to filter data in grid with odata filter

Thread ID:

Created:

Updated:

Platform:

Replies:

146980 Aug 27,2019 02:23 PM UTC Sep 5,2019 03:38 AM UTC Blazor 8
loading
Tags: Grid
Manuel Reinacher
Asked On August 27, 2019 02:23 PM UTC


Hello,

I'm using the  following code:


<EjsGrid TValue="Address" ModelType="@aModel"
AllowPaging="true" AllowReordering="true" AllowFiltering="true" AllowGrouping="true"
AllowPdfExport="true" AllowExcelExport="true" AllowSorting="true"
GridLines="GridLine.Both"
ContextMenuItems="@(new List<object>() {  "Delete", "Group","PdfExport", "ExcelExport", "CsvExport" })"
Toolbar="@(new List<string>() { "Add", "Edit", "Delete" })"
Height="315">

<EjsDataManager Url="http://localhost:5000/api/address" Adaptor="Adaptors.ODataV4Adaptor"></EjsDataManager>

<GridFilterSettings Type="Syncfusion.EJ2.Blazor.Grids.FilterType.Menu"></GridFilterSettings>
<GridGroupSettings ShowDropArea="false" ShowUngroupButton="true"></GridGroupSettings>

<GridEditSettings AllowAdding="true" AllowEditing="true" AllowDeleting="true" Mode="EditMode.Dialog">
</GridEditSettings>

<GridColumns>
<GridColumn Field=@nameof(Address.Id) HeaderText="Id" IsPrimaryKey="true" IsIdentity="true" Visible="false"></GridColumn>
<GridColumn Field=@nameof(Address.Street) HeaderText="First Name"></GridColumn>
<GridColumn Field=@nameof(Address.City) HeaderText="Last Name"></GridColumn>
</GridColumns>

<GridFilterSettings></GridFilterSettings>

</EjsGrid>



When I'm using it, the browser is calling:
http://localhost:5000/api/address/?&$count=true&$skip=0&$top=12

and gets the data



How can I achieve, that there is a filter applied to the odata like this:

http://localhost:5000/api/address/?&$count=true&$skip=0&$top=12&$filter=id eq 20f16cae-0e41-40bd-82da-46c316d2d83e


I played around with Query and QueryString, but I could not get it run with Blazor.


Thank you very much!







Vignesh Natarajan [Syncfusion]
Replied On August 28, 2019 11:23 AM UTC

Hi Manuel,  

Greetings from Syncfusion support.  

Query: “How can I achieve, that there is a filter applied to the odata like this: 

From your query we understand that you need to apply filter during the initial rendering itself. We suggest you to achieve your requirement by defining the GridFilterSettings as below  

@{ 
    List<PredicateModel> FilterColumns = new List<PredicateModel> 
    (); 
    FilterColumns.Add(new PredicateModel() { Field = "CustomerID", MatchCase = false, Operator = "startswith", Predicate = "and", Value = "ANANTR" }); 
    } 
    <EjsGrid DataSource="@Orders" TValue="Order" AllowPaging="true" AllowFiltering="true"> 
        <EjsDataManager Url="https://services.odata.org/V4/Northwind/Northwind.svc/Orders/" Adaptor="Adaptors.ODataV4Adaptor"></EjsDataManager> 
        <GridFilterSettings Columns="@FilterColumns"></GridFilterSettings> 
        ……………………………… 
    </EjsGrid> 

Note: when initial filtering is applied, we generate our own query based on filter columns values.  

Refer our UG documentation for your reference 


Please get back to us if you have any other queries.     

Regards,
Vignesh Natarajan.  
 


Manuel Reinacher
Replied On August 28, 2019 04:25 PM UTC

Hello Vignesh,

Thank you very much for your answer.

I added an id based filter which is working fine.


1.

But let's say I want to do an more advaned query like this

http://localhost:5000/api/phonenumber?$filter=User/Id eq 1acfb089-b4dd-4b4e-8dbc-aab60138c0f7

So I want to get all PhoneNumbers where User.Id == 1acfb089-b4dd-4b4e-8dbc-aab60138c0f7

(User is a complex object)

i.e. I tried this:

FilterColumns.Add(new PredicateModel() { Field = "Id", MatchCase = true, Operator = "equal", Predicate = "and", Value = "User/Id eq 1acfb089-b4dd-4b4e-8dbc-aab60138c0f7"});



But it will call

$filter=(Id eq guid'User/Id eq 1acfb089-b4dd-4b4e-8dbc-aab60138c0f7')


Insted of:

$filter=(User/Id eq 1acfb089-b4dd-4b4e-8dbc-aab60138c0f7)


It just substitutes the value and I don't see a way to edit the "Id" Value to "User/id".
When I change the Property "Field" to "User/id" it will not work - Because the information is lost which field I want to filter.


Btw: The guid syntax is wrong in this example, but this is a known issue:


2.
I assume that the grid can not automatically link an created object to another object via odata. Is that correct?


Thank you!


regards Manuel




Renjith Singh Rajendran [Syncfusion]
Replied On August 29, 2019 10:17 AM UTC

Hi Manuel, 

Thanks for contacting Syncfusion support. 

Query 1 : But it will call $filter=(Id eq guid'User/Id eq 1acfb089-b4dd-4b4e-8dbc-aab60138c0f7') Insted of: $filter=(User/Id eq 1acfb089-b4dd-4b4e-8dbc-aab60138c0f7) 
Yes. This is an already known issue. Thank you for taking  time to report this issue and helping us improve our product. At Syncfusion, we are committed to fixing all validated defects (subject to technological feasibility and Product Development Life Cycle ) and including the defect fix in our upcoming release which is scheduled to be rolled out on or before September 4, 2019. Until then we appreciate your patience. 

You can now track the current status of your request, review the proposed resolution timeline, and contact us for any further inquiries through this link.   
 
Once our fix is rolled out we suggest you to use your codes as like the below way to achieve your requirement. Set the “Field” and “Value” properties as like the below highlighted codes. Please use the code below, 

 
@{ 
   List<PredicateModel> FilterColumns = new List<PredicateModel>(); 
   FilterColumns.Add(new PredicateModel() { Field = "User.Id", MatchCase = false, Operator = "equal", Predicate = "and", Value = "1acfb089-b4dd-4b4e-8dbc-aab60138c0f7" });  
} 
 
    <EjsGrid ... AllowFiltering="true" ...> 
        <GridFilterSettings Columns="@FilterColumns"></GridFilterSettings> 
        <EjsDataManager Url="https://services.odata.org/V4/Northwind/Northwind.svc/Orders/" Adaptor="Adaptors.ODataV4Adaptor"></EjsDataManager> 
        <GridColumns> 
           <GridColumn Field="User.Id" HeaderText="ID" Width="90"></GridColumn>        @*Define a complex field column in Grid*@ 
        </GridColumns> 
    </EjsGrid> 
 
 
 
Query 2 : I assume that the grid can not automatically link an created object to another object via odata. Is that correct? 
We are not certain about this query and your exact requirement. We need more details to further proceed on this and provide you a solution as early as possible. Please get back to us with the following details for better assistance, 

  1. Share with us a detailed description for your query.
  2. Share with us a detailed explanation of your complete requirement.
  3. Share a screenshot or video demo explaining your requirement.

The provided information will help us analyze the problem, and provide you a solution as early as possible. 

Regards, 
Renjith Singh Rajendran. 


Manuel Reinacher
Replied On September 1, 2019 08:00 PM UTC

Hello Renjith Singh Rajendran,

Thanks for your answer.

I did not realize that there is already an documentation about Blazor -  I completely missed it ;)

Query 2:

I have i.e. a student which has n books and I want to display a list of books on an student's detail view.
The list of books contains all books which are referenced to the single student.

Now  I use filter from Query 1. - It displays the correct data.

When a user creates a new entry with the EjsGrid, the new entry must be referenced to the student.


I assume that a current solution is to use the OnCellSaved Event or something and reference the new entry to the student after the odatav4 adapter sent the post to create the new value.
For my case this is ok, but it would be better to create and link a new entry in an single request.

in the protocol there is an section "11.4.2.1 Link to Related Entities When Creating an Entity"

Does the Odata Adaptor / Grid support this?

Also it may create an odata batch request to insert the new value and directly reference it to the parent object.


I assume to achieve this directly linking thing I must create an Custom Adaptor.
When I try to do that I use this loc:
    <EjsDataManager Adaptor="Adaptors.CustomAdaptor" AdaptorInstance="typeof(CustomDataAdaptor)"></EjsDataManager>

For this case it is a little bit bad, that I can not define an object for the AdaptorInstance value, because I could do something like that:
    <EjsDataManager Adaptor="Adaptors.CustomAdaptor" AdaptorInstance="new CustomDataAdaptor(Model.Student.Id)"></EjsDataManager>

Becasue then the CustomDataAdaptor could link the created value to the student


regards Manuel

Vignesh Natarajan [Syncfusion]
Replied On September 3, 2019 11:02 AM UTC

Hi Manuel,  

Query: “When a user creates a new entry with the EjsGrid, the new entry must be referenced to the student. 

From your query, we suspect that you need to value of master (parent) row value in the detail Grid while adding a record. We suggest you to achieve your requirement using DefaultValue property of GridColumns. Refer the below code example.  

<EjsGrid ModelType="@Model" DataSource="@Employees" Height="315px"> 
    <GridTemplates> 
        <DetailTemplate> 
            @{ 
                var employee = (context as EmployeeData); 
                <EjsGrid DataSource="@Orders" Toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Cancel", "Update" })" Query="@($"new ej.data.Query().where('EmployeeID', 'equal', {employee.EmployeeID})")"> 
                    <GridEditSettings Mode="EditMode.Batch" AllowAdding="true" AllowEditing="true" AllowDeleting="true"></GridEditSettings> 
                    <GridColumns> 
                        <GridColumn Field=@nameof(Order.EmployeeID) DefaultValue="employee.EmployeeID" Width="110"> </GridColumn> 
                        ……………………………… 
                    </GridColumns>                     
                </EjsGrid> 
            } 
        </DetailTemplate> 
    </GridTemplates> 
    <GridColumns> 
………………………………. 
    </GridColumns> 
</EjsGrid> 

Refer the below screenshot for the output (while adding a record in Grid) 

 

Note: if you do not want the EmployeeID column to be visible in grid, Kindly hide the column using Visibility property of Grid column. Refer the  below UG documentation 


For your convenience we have prepared a sample which can be downloaded from below  


Please get back to us if you have further queries.   

Regards, 
Vignesh Natarajan. 


Renjith Singh Rajendran [Syncfusion]
Replied On September 4, 2019 01:05 PM UTC

Hi Manuel,  

We are glad to inform that our latest Nuget package (17.2.0.49-beta) has been successfully rolled out. Please find the latest Nuget package from below   

 
Please find the release notes from below link for the changes and fixes we have included in this release  

 
In this release we have included the fix for the issue “Filter query string is in wrong format for GUID with OdataV4 adaptor”.  
 
Please get back to us if you have further queries. 
 
Regards, 
Renjith Singh Rajendran.  
 


Manuel Reinacher
Replied On September 4, 2019 02:15 PM UTC

Hello,

Thank you very much for the bugfix and the explanation.

Now everything is working fine for me!

Thanks!

regards, Manuel

Vignesh Natarajan [Syncfusion]
Replied On September 5, 2019 03:38 AM UTC

Hi Manuel,  
 
Thanks for the acknowledgement. 
 
We are glad to hear that you query has been resolved.  
 
Please get back to us if you have any other queries.  
 
Regards,
Vignesh Natarajan.
 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon