Invoke filter not working

Radzen Studio Angular
1

I have a simple datagrid that I am trying to filter based on user. The table has 3 columns, an Id (Identity, PK), Producer (ID related to another table for a many to many relationship), and UserId (stores the text value of a username that can be chosen in a drop down on creation.

I can invoke the getProducerUsers and return all items without an issue.

Works%20with%20no%20filter
Works with no filter.PNG1596×178 12.2 KB

If I add a filter with the query builder such as this.

filter
filter.PNG1080×357 4.87 KB

I get no result like below.

empty%20result
empty result.PNG1576×91 1.92 KB

Radzen is calling this OData.
dotnet: info: Microsoft.AspNetCore.Hosting.Internal.WebHost[1]
Request starting HTTP/1.1 GET http://localhost:5000/odata/Andipakt/ProducerUsers?$filter=UserId%20eq%20joshwilliam@gmail.com&$expand=Producer

If I browse to that URL I get this response.
{"error":{"code":"","message":"The query specified in the URI is not valid. Syntax error at position 31 in 'UserId eq joshwilliam@gmail.com'.","details":[],"innererror":{"message":"Syntax error at position 31 in 'UserId eq joshwilliam@gmail.com'.","type":"Microsoft.OData.ODataException","stacktrace":" at Microsoft.OData.UriParser.ExpressionLexer.ValidateToken(ExpressionTokenKind t)\r\n at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseExpressionText(String expressionText)\r\n at Microsoft.OData.UriParser.ODataQueryOptionParser.ParseFilterImplementation(String filter, ODataUriParserConfiguration configuration, ODataPathInfo odataPathInfo)\r\n at Microsoft.OData.UriParser.ODataQueryOptionParser.ParseFilter()\r\n at Microsoft.AspNet.OData.Query.FilterQueryOption.get_FilterClause()\r\n at Microsoft.AspNet.OData.Query.Validators.FilterQueryValidator.Validate(FilterQueryOption filterQueryOption, ODataValidationSettings settings)\r\n at Microsoft.AspNet.OData.Query.Validators.ODataQueryValidator.Validate(ODataQueryOptions options, ODataValidationSettings validationSettings)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.ValidateQuery(HttpRequest request, ODataQueryOptions queryOptions)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.<>c__DisplayClass1_0.b__3(ODataQueryContext queryContext)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.ExecuteQuery(Object responseValue, IQueryable singleResultCollection, IWebApiActionDescriptor actionDescriptor, Func2 modelFunction, IWebApiRequestMessage request, Func2 createQueryOptionFunction)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.OnActionExecuted(Object responseValue, IQueryable singleResultCollection, IWebApiActionDescriptor actionDescriptor, IWebApiRequestMessage request, Func2 modelFunction, Func2 createQueryOptionFunction, Action1 createResponseAction, Action3 createErrorAction)"}}}

I feel like this should work given the filter I setup but am not having luck. Am I doing something wrong here?

Thanks,

Josh

2

Hi Josh,

You should quote the filter value since it is string.

Best Regards,
Vladimir

3

Thanks,

That works.

0
0.PNG1542×108 7.05 KB

Now I am trying to filter a 2nd grid using the result of the first. I am using the InList operation to filter with.

1
1.PNG1109×653 41.8 KB

2
2.PNG1107×293 4.81 KB

My grid for this is again empty. Is this the correct usage for the InList operation?

Ultimately I am using these results to filter a grid to show a result set of items that only that specific user should see. I've simply dropped these datagrids on the page to see the results at each stage or in this case the lack of them.

Thanks for the help.

4

I've tried both ${UserProducer.Producer} and ${UserProducer.ProducerId}

5

Hi Josh,

The filer value for InList operation should be an array while in your case it looks like single value.

6

It should be an array. It is the result on the previous invoke getProducerUsers stored in a property UserProducer. This table could hold 1 or more records for any given user.

Josh

7

I am looking to use the returned ProducerId values as the filter for the getProducerAuctions invoke method.

8

Can you post the OData url for the InList filtering?

9

I'm actually not seeing it generate an odata call when I use that InList expression. I cleared the result page and reloaded. This is the complete output from the page load from Radzen. If I change the expression to test it and statically set the ID it does generate an odata call and returns the result I would expect.

dotnet: info: Microsoft.AspNetCore.Hosting.Internal.WebHost[1]
Request starting HTTP/1.1 OPTIONS http://localhost:5000/odata/Andipakt/ProducerUsers?$filter=UserId%20eq%20'joshwilliam@gmail.com'&$expand=Producer
info: Microsoft.AspNetCore.Cors.Infrastructure.CorsService[4]
CORS policy execution successful.
info: Microsoft.AspNetCore.Hosting.Internal.WebHost[2]
Request finished in 0.0788ms 204

dotnet: info: Microsoft.AspNetCore.Hosting.Internal.WebHost[1]
Request starting HTTP/1.1 GET http://localhost:5000/odata/Andipakt/ProducerUsers?$filter=UserId%20eq%20'joshwilliam@gmail.com'&$expand=Producer
info: Microsoft.AspNetCore.Cors.Infrastructure.CorsService[4]
CORS policy execution successful.

dotnet: info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[1]
Route matched with {action = "GetProducerUsers", controller = "ProducerUsers"}. Executing action Andipakt.Controllers.Andipakt.ProducerUsersController.GetProducerUsers (server)

dotnet: info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[1]
Executing action method Andipakt.Controllers.Andipakt.ProducerUsersController.GetProducerUsers (server) - Validation state: Valid
info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[2]
Executed action method Andipakt.Controllers.Andipakt.ProducerUsersController.GetProducerUsers (server), returned result Microsoft.AspNetCore.Mvc.ObjectResult in 0.0055ms.

dotnet: info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 2.2.3-servicing-35854 initialized 'AndipaktContext' using provider 'Microsoft.EntityFrameworkCore.SqlServer' with options: None

dotnet: info: Microsoft.AspNetCore.Mvc.Infrastructure.ObjectResultExecutor[1]
Executing ObjectResult, writing value of type 'Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1[[Microsoft.AspNet.OData.Query.Expressions.SelectExpandBinder+SelectAllAndExpand1[[Andipakt.Models.Andipakt.ProducerUser, server, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]], Microsoft.AspNetCore.OData, Version=7.1.0.21120, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]'.

dotnet: info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (2ms) [Parameters=[@__TypedProperty_0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT [$it].[Id], [$it].[ProducerId], [$it].[UserId], [$it.Producer].[Id], [$it.Producer].[BillToAddress], [$it.Producer].[BillToCity], [$it.Producer].[BillToState], [$it.Producer].[BillToZip], [$it.Producer].[CreatedBy], [$it.Producer].[CreatedOn], [$it.Producer].[ModfiedOn], [$it.Producer].[ModifiedBy], [$it.Producer].[Name]
FROM [dbo].[ProducerUsers] AS [$it]
INNER JOIN [dbo].[Producer] AS [$it.Producer] ON [$it].[ProducerId] = [$it.Producer].[Id]
WHERE [$it].[UserId] = @__TypedProperty_0

dotnet: info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[2]
Executed action Andipakt.Controllers.Andipakt.ProducerUsersController.GetProducerUsers (server) in 3.7913ms
info: Microsoft.AspNetCore.Hosting.Internal.WebHost[2]
Request finished in 4.2063ms 200 application/json; odata.metadata=minimal; odata.streaming=true; charset=utf-8

10

Can you check the browser network tab or console if the are errors?

11

There is the following error in the console.

core.js:15724 ERROR TypeError: Cannot read property 'ProducerId' of undefined
at ProducerActiveAuctionsComponent.push../src/app/producer-active-auctions/producer-active-auctions-generated.component.ts.ProducerActiveAuctionsGenerated.load (producer-active-auctions-generated.component.ts:116)
at SafeSubscriber._next (producer-active-auctions-generated.component.ts:98)
at SafeSubscriber.push.../../../../../../Program Files/Radzen/resources/node_modules/rxjs/_esm5/internal/Subscriber.js.SafeSubscriber.__tryOrUnsub (Subscriber.js:196)
at SafeSubscriber.push.../../../../../../Program Files/Radzen/resources/node_modules/rxjs/_esm5/internal/Subscriber.js.SafeSubscriber.next (Subscriber.js:134)
at Subscriber.push.../../../../../../Program Files/Radzen/resources/node_modules/rxjs/_esm5/internal/Subscriber.js.Subscriber._next (Subscriber.js:77)
at Subscriber.push.../../../../../../Program Files/Radzen/resources/node_modules/rxjs/_esm5/internal/Subscriber.js.Subscriber.next (Subscriber.js:54)
at BehaviorSubject.push.../../../../../../Program Files/Radzen/resources/node_modules/rxjs/_esm5/internal/BehaviorSubject.js.BehaviorSubject._subscribe (BehaviorSubject.js:22)
at BehaviorSubject.push.../../../../../../Program Files/Radzen/resources/node_modules/rxjs/_esm5/internal/Observable.js.Observable._trySubscribe (Observable.js:43)
at BehaviorSubject.push.../../../../../../Program Files/Radzen/resources/node_modules/rxjs/_esm5/internal/Subject.js.Subject._trySubscribe (Subject.js:89)
at BehaviorSubject.push.../../../../../../Program Files/Radzen/resources/node_modules/rxjs/_esm5/internal/Observable.js.Observable.subscribe (Observable.js:29)

12

In this case the request is not executed because of the error. If you want to use the result from previous request you should invoke the second request in Then of the first request.

13

I moved it around to execute that invoke in the "then" section after the first. It generates the request now but says it is bad.

GET http://localhost:5000/odata/Andipakt/ProducerAuctions?$filter=ProducerId%20in%20(undefined)&$expand=Producer,Auction 400 (Bad Request)

5
5.PNG1112×662 35.3 KB

14

The filter parameter value is not defined. It should be ${result.value} if you want to use the array returned from previous request.

15

I guess I don't follow this one. So the filter on the 2nd invoke should be ProducerID InList ${result.value}?

How would I specify I only want to match the result.ProducerId field from that set?

That is why I assumed I should use the property.fieldName I stored it in the first time.

16

You can select single property from the array like this:
${result.value.map(p => p.ProducerID)}

17

Closer I think. This is the Odata URL that was sent.

http://localhost:5000/odata/Andipakt/ProducerAuctions?$filter=ProducerId%20in%20,&$expand=Producer,Auction

When I browse to the URL this is the error displayed

{"error":{"code":"","message":"The query specified in the URI is not valid. Expression expected at position 14 in 'ProducerId in ,'.","details":[],"innererror":{"message":"Expression expected at position 14 in 'ProducerId in ,'.","type":"Microsoft.OData.ODataException","stacktrace":" at Microsoft.OData.UriParser.UriQueryExpressionParser.ParsePrimaryStart()\r\n at Microsoft.OData.UriParser.UriQueryExpressionParser.ParsePrimary()\r\n at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseUnary()\r\n at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseMultiplicative()\r\n at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseAdditive()\r\n at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseComparison()\r\n at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseLogicalAnd()\r\n at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseLogicalOr()\r\n at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseExpression()\r\n at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseExpressionText(String expressionText)\r\n at Microsoft.OData.UriParser.ODataQueryOptionParser.ParseFilterImplementation(String filter, ODataUriParserConfiguration configuration, ODataPathInfo odataPathInfo)\r\n at Microsoft.OData.UriParser.ODataQueryOptionParser.ParseFilter()\r\n at Microsoft.AspNet.OData.Query.FilterQueryOption.get_FilterClause()\r\n at Microsoft.AspNet.OData.Query.Validators.FilterQueryValidator.Validate(FilterQueryOption filterQueryOption, ODataValidationSettings settings)\r\n at Microsoft.AspNet.OData.Query.Validators.ODataQueryValidator.Validate(ODataQueryOptions options, ODataValidationSettings validationSettings)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.ValidateQuery(HttpRequest request, ODataQueryOptions queryOptions)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.<>c__DisplayClass1_0.b__3(ODataQueryContext queryContext)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.ExecuteQuery(Object responseValue, IQueryable singleResultCollection, IWebApiActionDescriptor actionDescriptor, Func2 modelFunction, IWebApiRequestMessage request, Func2 createQueryOptionFunction)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.OnActionExecuted(Object responseValue, IQueryable singleResultCollection, IWebApiActionDescriptor actionDescriptor, IWebApiRequestMessage request, Func2 modelFunction, Func2 createQueryOptionFunction, Action1 createResponseAction, Action3 createErrorAction)"}}}

18

Ok so that was my fault, it is ProducerId not ProducerID but is still didn't work.

The URL created is now:
http://localhost:5000/odata/Andipakt/ProducerAuctions?$filter=ProducerId%20in%202,3&$expand=Producer,Auction

The page displays this

{"error":{"code":"","message":"The query specified in the URI is not valid. Syntax error at position 16 in 'ProducerId in 2,3'.","details":[],"innererror":{"message":"Syntax error at position 16 in 'ProducerId in 2,3'.","type":"Microsoft.OData.ODataException","stacktrace":" at Microsoft.OData.UriParser.ExpressionLexer.ValidateToken(ExpressionTokenKind t)\r\n at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseExpressionText(String expressionText)\r\n at Microsoft.OData.UriParser.ODataQueryOptionParser.ParseFilterImplementation(String filter, ODataUriParserConfiguration configuration, ODataPathInfo odataPathInfo)\r\n at Microsoft.OData.UriParser.ODataQueryOptionParser.ParseFilter()\r\n at Microsoft.AspNet.OData.Query.FilterQueryOption.get_FilterClause()\r\n at Microsoft.AspNet.OData.Query.Validators.FilterQueryValidator.Validate(FilterQueryOption filterQueryOption, ODataValidationSettings settings)\r\n at Microsoft.AspNet.OData.Query.Validators.ODataQueryValidator.Validate(ODataQueryOptions options, ODataValidationSettings validationSettings)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.ValidateQuery(HttpRequest request, ODataQueryOptions queryOptions)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.<>c__DisplayClass1_0.b__3(ODataQueryContext queryContext)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.ExecuteQuery(Object responseValue, IQueryable singleResultCollection, IWebApiActionDescriptor actionDescriptor, Func2 modelFunction, IWebApiRequestMessage request, Func2 createQueryOptionFunction)\r\n at Microsoft.AspNet.OData.EnableQueryAttribute.OnActionExecuted(Object responseValue, IQueryable singleResultCollection, IWebApiActionDescriptor actionDescriptor, IWebApiRequestMessage request, Func2 modelFunction, Func2 createQueryOptionFunction, Action1 createResponseAction, Action3 createErrorAction)"}}}

19

That proves it is pulling the correct values. 2 and 3 are the correct ID's that it should be using to query with but something is not working right there.

20

Please wrap the array like this:
(${result.value.map(p => p.ProducerID)})

Here is how it looks with Orders -> OrderDetails from our Sample database:

image
image.png1920×1080 122 KB

http://localhost:5000/odata/Sample/OrderDetails?$filter=OrderId%20in%20(1,2,3)

image
image.png1920×1080 151 KB