Sorting by list of integers in RavenDB

Question

I'm attempting to retrieve documents in RavenDB where I'm ordering by a list of int.

Queryable = Queryable.OrderBy(dto => SearchIds.IndexOf(dto.PropertyId));

The code above is a solution I've found from this post.

However when the client needs to resolve the LINQ to RQL it gives me back an error of.

System.InvalidOperationException: Cannot understand how to translate value(infrastructure.DataAccess.HolidayHome.Filtering.PropertyInformationFilteringService).SearchIds.IndexOf(dto.PropertyId)
   at Raven.Client.Documents.Linq.LinqPathProvider.GetPath(Expression expression) in C:\Builds\RavenDB-Stable-5.0\50009\src\Raven.Client\Documents\Linq\LinqPathProvider.cs:line 114
   at Raven.Client.Documents.Linq.RavenQueryProviderProcessor`1.GetMemberDirect(Expression expression) in C:\Builds\RavenDB-Stable-5.0\50009\src\Raven.Client\Documents\Linq\RavenQueryProviderProcessor.cs:line 554
   at Raven.Client.Documents.Linq.RavenQueryProviderProcessor`1.VisitOrderBy(LambdaExpression expression, Boolean descending) in C:\Builds\RavenDB-Stable-5.0\50009\src\Raven.Client\Documents\Linq\RavenQueryProviderProcessor.cs:line 2263
   at Raven.Client.Documents.Linq.RavenQueryProviderProcessor`1.VisitQueryableMethodCall(MethodCallExpression expression) in C:\Builds\RavenDB-Stable-5.0\50009\src\Raven.Client\Documents\Linq\RavenQueryProviderProcessor.cs:line 1914
   at Raven.Client.Documents.Linq.RavenQueryProviderProcessor`1.VisitMethodCall(MethodCallExpression expression) in C:\Builds\RavenDB-Stable-5.0\50009\src\Raven.Client\Documents\Linq\RavenQueryProviderProcessor.cs:line 1204
   at Raven.Client.Documents.Linq.RavenQueryProviderProcessor`1.VisitExpression(Expression expression) in C:\Builds\RavenDB-Stable-5.0\50009\src\Raven.Client\Documents\Linq\RavenQueryProviderProcessor.cs:line 202
   at Raven.Client.Documents.Linq.RavenQueryProviderProcessor`1.VisitQueryableMethodCall(MethodCallExpression expression) in C:\Builds\RavenDB-Stable-5.0\50009\src\Raven.Client\Documents\Linq\RavenQueryProviderProcessor.cs:line 1802
   at Raven.Client.Documents.Linq.RavenQueryProviderProcessor`1.VisitMethodCall(MethodCallExpression expression) in C:\Builds\RavenDB-Stable-5.0\50009\src\Raven.Client\Documents\Linq\RavenQueryProviderProcessor.cs:line 1204
   at Raven.Client.Documents.Linq.RavenQueryProviderProcessor`1.VisitExpression(Expression expression) in C:\Builds\RavenDB-Stable-5.0\50009\src\Raven.Client\Documents\Linq\RavenQueryProviderProcessor.cs:line 202
   at Raven.Client.Documents.Linq.RavenQueryProviderProcessor`1.VisitQueryableMethodCall(MethodCallExpression expression) in C:\Builds\RavenDB-Stable-5.0\50009\src\Raven.Client\Documents\Linq\RavenQueryProviderProcessor.cs:line 1808
   at Raven.Client.Documents.Linq.RavenQueryProviderProcessor`1.VisitMethodCall(MethodCallExpression expression) in C:\Builds\RavenDB-Stable-5.0\50009\src\Raven.Client\Documents\Linq\RavenQueryProviderProcessor.cs:line 1204
   at Raven.Client.Documents.Linq.RavenQueryProviderProcessor`1.VisitExpression(Expression expression) in C:\Builds\RavenDB-Stable-5.0\50009\src\Raven.Client\Documents\Linq\RavenQueryProviderProcessor.cs:line 202
   at Raven.Client.Documents.Linq.RavenQueryProviderProcessor`1.GetAsyncDocumentQueryFor(Expression expression, Action`1 customization) in C:\Builds\RavenDB-Stable-5.0\50009\src\Raven.Client\Documents\Linq\RavenQueryProviderProcessor.cs:line 3518
   at Raven.Client.Documents.Linq.RavenQueryProvider`1.ToAsyncDocumentQuery[TResult](Expression expression) in C:\Builds\RavenDB-Stable-5.0\50009\src\Raven.Client\Documents\Linq\RavenQueryProvider.cs:line 258
   at Raven.Client.Documents.LinqExtensions.ToListAsync[T](IQueryable`1 source, CancellationToken token) in C:\Builds\RavenDB-Stable-5.0\50009\src\Raven.Client\Documents\LinqExtensions.cs:line 313
   at infrastructure.DataAccess.HolidayHome.Filtering.PropertyInformationFilteringService.FilterBySearchCriteria() in C:\Users\Alex\git\search-service\infrastructure\DataAccess\HolidayHome\Filtering\PropertyInformationFilteringService.cs:line 31
   at infrastructure.DataAccess.HolidayHome.Commands.Property.PropertyInfoCommands.GetPropertyDtos(SearchResultCriteria searchCriteria, List`1 searchIds) in C:\Users\Alex\git\search-service\infrastructure\DataAccess\HolidayHome\Commands\Property\PropertyInfoCommands.cs:line 29
   at webapi.Controllers.v1.SearchController.RetrieveSearch(SearchResultCriteria searchResultCriteria) in C:\Users\Alex\git\search-service\web-api\Controllers\v1\SearchController.cs:line 265
   at webapi.Controllers.v1.SearchController.Get(SearchResultCriteria searchResultCriteria) in C:\Users\Alex\git\search-service\web-api\Controllers\v1\SearchController.cs:line 67
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)

Is there a better way of ordering by a list of integers?

score 2 · Answer 1 · answered Feb 03 '21 at 14:45

2

See the following example to sort query results:
https://demo.ravendb.net/demos/csharp/queries/sorting-query-results

You define a query with OrderByDescending or OrderBy()
See: https://demo.ravendb.net/demos/csharp/queries/sorting-query-results#step-3

A secondary sort is also available:
See: https://demo.ravendb.net/demos/csharp/queries/sorting-query-results#step-4

answered Feb 03 '21 at 14:45

Danielle

3,324
2
18
31

The problem is that none of the "base" order by operations in ravendb support ordering by a list of ids. – Alex Pedersen Feb 03 '21 at 16:23
you can create a **static index**, index the 'id' property, and then **query the index**, and use `orderBy` in that query on the id field – Danielle Feb 03 '21 at 17:05
1

I don't need to order by low to high or high to low. I need to order by another list of integers. – Alex Pedersen Feb 04 '21 at 08:30

score 2 · Answer 2 · answered Feb 04 '21 at 09:26

If you have some list of ids then using session's Load method for multiple Ids should bring you them in the the same order of list.

using (var s = store.OpenSession())
{
    var list = new List<string> {"users/2-A", "users/3-A", "users/6-A"};
    var res = session.Load<User>(list);
}

Sorting by list of integers in RavenDB

2 Answers2