How to filter data in grid with odata filter

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

</GridEditSettings>

</GridColumns>

</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!

Reply | Reply with quote

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>

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.

Reply | Reply with quote

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:

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

Reply | Reply with quote

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.

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,

Share with us a detailed description for your query.
Share with us a detailed explanation of your complete requirement.
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.

Reply | Reply with quote

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"

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:

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:

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

regards Manuel

Reply | Reply with quote

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

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.

Reply | Reply with quote

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

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.

Reply | Reply with quote

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

Reply | Reply with quote

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.

Reply | Reply with quote

Thread ID:	Created:	Updated:	Platform:	Replies:
146980	Aug 27,2019 02:23 PM UTC	Sep 5,2019 03:38 AM UTC	Blazor	8

Thread ID:

Created:

Updated:

Platform:

Replies:

Please sign in to access our forum

CONTACT US