How to filter data in grid with odata filter

[Manuel Reinacher]

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]

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 

UG: https://ej2.syncfusion.com/blazor/documentation/grid/filtering/#initial-filter  

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

Regards,
Vignesh Natarajan.  

 

[Manuel Reinacher]

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:

https://www.syncfusion.com/forums/146633/odata-v4-guids-are-in-the-wrong-format

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]

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.   

Feedback link : https://www.syncfusion.com/feedback/8182/filter-query-string-is-in-wrong-format-for-guid-with-odatav4-adaptor 

 

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> 

 

 

Documentation : https://ej2.syncfusion.com/blazor/documentation/grid/columns/#complex-data-binding 

 

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]

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"

http://docs.oasis-open.org/odata/odata/v4.01/csprd05/part1-protocol/odata-v4.01-csprd05-part1-protocol.html#_Toc14172826

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]

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 

https://ej2.syncfusion.com/blazor/documentation/grid/columns/#visibility 

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

Sample: https://www.syncfusion.com/downloads/support/forum/146980/ze/BlazorApp11221755279  

Please get back to us if you have further queries.   

Regards, 

Vignesh Natarajan. 

[Renjith Singh Rajendran]

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   

Nuget: https://www.nuget.org/packages/Syncfusion.EJ2.Blazor/17.2.0.49-beta  

 

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

Grid: https://ej2.syncfusion.com/blazor/documentation/release-notes/17.2.49/#grid  

 

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]

Hello,

Thank you very much for the bugfix and the explanation.

Now everything is working fine for me!

Thanks!

regards, Manuel

[Vignesh Natarajan]

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.