Table Query with OrderBy fails

[marxxxx]

This comment suggested that OrderBy("RowKey") is now possible to sort data in a Table by RowKey achieving a similar behavior to what Storage Table offers.

However, trying a query with this feature failed.

I am using the NuGet Package Microsoft.Azure.Cosmos.Table 1.0.0 in a .NET Core 2.2 based console application. The Cosmos DB Table API instance i use for testing is located in West Europe. If required i could share the Endpoint via private message.

The source code in my sample is here:

`var account = CloudStorageAccount.Parse(ConnectionString); var tableClient = account.CreateCloudTableClient(); var table = tableClient.GetTableReference("telemetry");

        string startRowKey = "20190325095347586";
        string endRowKey = "20190325095347586";

        var where =
               TableQuery.CombineFilters(
                   TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.GreaterThanOrEqual,
                       startRowKey),
                   TableOperators.And,
                   TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.LessThanOrEqual,
                       endRowKey));

        var query = table.CreateQuery<TelemetryTableEntity>().Where(where)
            .OrderBy("RowKey");

        var result = table.ExecuteQuery<TelemetryTableEntity>(query);

        TableContinuationToken token = null;
        // Read entities from each query segment.
        do
        {
            TableQuerySegment<TelemetryTableEntity> segment = await table.ExecuteQuerySegmentedAsync(query, token);

            if (segment.RequestCharge.HasValue)
            {
                Console.WriteLine("Request Charge for Query Operation: " + segment.RequestCharge);
            }

            token = segment.ContinuationToken;
            foreach (TelemetryTableEntity entity in segment)
            {
                Console.WriteLine(entity);
            }
        }
        while (token != null);`

Full exception details:

Microsoft.Azure.Cosmos.Table.StorageException: Message: {"errors":[{"severity":"Error","location":{"start":26,"end":28},"code":"SC1001","message":"Syntax error, incorrect syntax near 'by'."}]} ActivityId: 03088207-a91e-4ec2-b1c3-8294167e4b6a, Microsoft.Azure.Documents.Common/2.2.0.0, Windows/10.0.17134 documentdb-netcore-sdk/2.1.3 ---> Microsoft.Azure.Documents.DocumentClientException: Message: {"errors":[{"severity":"Error","location":{"start":26,"end":28},"code":"SC1001","message":"Syntax error, incorrect syntax near 'by'."}]} ActivityId: 03088207-a91e-4ec2-b1c3-8294167e4b6a, Microsoft.Azure.Documents.Common/2.2.0.0, Windows/10.0.17134 documentdb-netcore-sdk/2.1.3 at Microsoft.Azure.Documents.Client.ClientExtensions.ParseResponseAsync(HttpResponseMessage responseMessage, JsonSerializerSettings serializerSettings) at Microsoft.Azure.Documents.GatewayStoreModel.<>c__DisplayClass20_0.<<InvokeAsync>b__0>d.MoveNext() --- End of stack trace from previous location where exception was thrown --- at Microsoft.Azure.Documents.BackoffRetryUtility1.<>cDisplayClass1_0.<b0>d.MoveNext() --- End of stack trace from previous location where exception was thrown --- at Microsoft.Azure.Documents.BackoffRetryUtility1.ExecuteRetryAsync(Func1 callbackMethod, Func3 callShouldRetry, Func1 inBackoffAlternateCallbackMethod, TimeSpan minBackoffForInBackoffCallback, CancellationToken cancellationToken, Action1 preRetryCallback) at Microsoft.Azure.Documents.ShouldRetryResult.ThrowIfDoneTrying(ExceptionDispatchInfo capturedException) at Microsoft.Azure.Documents.BackoffRetryUtility1.ExecuteRetryAsync(Func1 callbackMethod, Func3 callShouldRetry, Func1 inBackoffAlternateCallbackMethod, TimeSpan minBackoffForInBackoffCallback, CancellationToken cancellationToken, Action1 preRetryCallback) at Microsoft.Azure.Documents.BackoffRetryUtility1.ExecuteAsync(Func1 callbackMethod, IRetryPolicy retryPolicy, CancellationToken cancellationToken, Action`1 preRetryCallback) at Microsoft.Azure.Documents.GatewayStoreModel.InvokeAsync(DocumentServiceRequest request, ResourceType resourceType, CancellationToken cancellationToken) at Microsoft.Azure.Documents.GatewayStoreModel.ProcessMessageAsync(DocumentServiceRequest request, CancellationToken cancellationToken) at Microsoft.Azure.Documents.Client.DocumentClient.ExecuteQueryAsync(DocumentServiceRequest request, CancellationToken cancellationToken) at Microsoft.Azure.Documents.Query.DocumentQueryClient.ExecuteQueryAsync(DocumentServiceRequest request, CancellationToken cancellationToken) at Microsoft.Azure.Documents.Query.DocumentQueryExecutionContextBase.ExecuteQueryRequestInternalAsync(DocumentServiceRequest request, CancellationToken cancellationToken) at Microsoft.Azure.Documents.Query.DocumentQueryExecutionContextBase.ExecuteQueryRequestAsync(DocumentServiceRequest request, CancellationToken cancellationToken) at Microsoft.Azure.Documents.Query.DocumentQueryExecutionContextBase.ExecuteRequestAsync(DocumentServiceRequest request, CancellationToken cancellationToken) at Microsoft.Azure.Documents.Query.DefaultDocumentQueryExecutionContext.ExecuteOnceAsync(IDocumentClientRetryPolicy retryPolicyInstance, CancellationToken cancellationToken) at Microsoft.Azure.Documents.Query.DefaultDocumentQueryExecutionContext.<>cDisplayClass9_0.<b0>d.MoveNext() --- End of stack trace from previous location where exception was thrown --- at Microsoft.Azure.Documents.BackoffRetryUtility1.<>c__DisplayClass1_0.<<ExecuteAsync>b__0>d.MoveNext() --- End of stack trace from previous location where exception was thrown --- at Microsoft.Azure.Documents.BackoffRetryUtility1.ExecuteRetryAsync(Func1 callbackMethod, Func3 callShouldRetry, Func1 inBackoffAlternateCallbackMethod, TimeSpan minBackoffForInBackoffCallback, CancellationToken cancellationToken, Action1 preRetryCallback) at Microsoft.Azure.Documents.ShouldRetryResult.ThrowIfDoneTrying(ExceptionDispatchInfo capturedException) at Microsoft.Azure.Documents.BackoffRetryUtility1.ExecuteRetryAsync(Func1 callbackMethod, Func3 callShouldRetry, Func1 inBackoffAlternateCallbackMethod, TimeSpan minBackoffForInBackoffCallback, CancellationToken cancellationToken, Action1 preRetryCallback) at Microsoft.Azure.Documents.BackoffRetryUtility1.ExecuteAsync(Func1 callbackMethod, IRetryPolicy retryPolicy, CancellationToken cancellationToken, Action1 preRetryCallback) at Microsoft.Azure.Documents.Query.DefaultDocumentQueryExecutionContext.ExecuteInternalAsync(CancellationToken cancellationToken) at Microsoft.Azure.Documents.Query.DocumentQueryExecutionContextBase.ExecuteNextAsync(CancellationToken cancellationToken) at Microsoft.Azure.Documents.Query.ProxyDocumentQueryExecutionContext.ExecuteNextAsync(CancellationToken token) at Microsoft.Azure.Documents.Linq.DocumentQuery1.ExecuteNextPrivateAsync[TResponse](CancellationToken cancellationToken) at Microsoft.Azure.Cosmos.Table.Extensions.TableExtensionQueryHelper.QueryDocumentsAsync[TResult](Nullable1 maxItemCount, String filterString, IList1 selectColumns, TableContinuationToken token, CloudTableClient client, CloudTable table, EntityResolver1 resolver, TableRequestOptions requestOptions, OperationContext operationContext, Boolean isLinqExpression, IList1 orderByItems) at Microsoft.Azure.Cosmos.Table.Extensions.TableExtensionExecutor.<>c__DisplayClass19_02.<b0>d.MoveNext() --- End of inner exception stack trace --- at Microsoft.Azure.Cosmos.Table.Extensions.TableExtensionExecutor.<>cDisplayClass19_02.<<ExecuteQuerySegmentedInternalAsync>b__0>d.MoveNext() --- End of stack trace from previous location where exception was thrown --- at Microsoft.Azure.Cosmos.Table.Extensions.TableExtensionRetryPolicy.ExecuteUnderRetryPolicy[TResult](Func1 executionMethod, CancellationToken cancellationToken, OperationContext operationContext, TableRequestOptions requestOptions) at CosmosDbTableClientDemo.Program.ExecuteCrossPartitionQueryWithOrderBy() in C:\Dev\Test\CosmosDbTableClientDemo\CosmosDbTableClientDemo\Program.cs:line 96 at CosmosDbTableClientDemo.Program.Main(String[] args) in C:\Dev\Test\CosmosDbTableClientDemo\CosmosDbTableClientDemo\Program.cs:line 15 Request Information RequestID:03088207-a91e-4ec2-b1c3-8294167e4b6a RequestCharge:0 RequestDate: StatusMessage:BadRequest ErrorCode: ErrorMessage:Message: {"errors":[{"severity":"Error","location":{"start":26,"end":28},"code":"SC1001","message":"Syntax error, incorrect syntax near 'by'."}]} ActivityId: 03088207-a91e-4ec2-b1c3-8294167e4b6a, Microsoft.Azure.Documents.Common/2.2.0.0, Windows/10.0.17134 documentdb-netcore-sdk/2.1.3`

The structure of TelemetryTableEntity looks like this:

public enum SensorDataType
    {
        Number = 10,
        String = 20,
        Bool = 30,
        BoolArray = 31
    }

    public enum SensorClassification
    {
        Raw = 0,
        Min = 10,
        Avg = 20,
        Max = 30
    }

    public class TelemetryTableEntity : TableEntity
    {
        public DateTime MeasurementTimestamp { get; set; }

        public string SensorId { get; set; }

        public SensorDataType DataType { get; set; }

        public SensorClassification Classification { get; set; }

        public double NumValue { get; set; }
        public string StringValue { get; set; }
        public bool BoolValue { get; set; }
        public string BoolArrayValue { get; set; }

    }

As soon as i remove .OrderBy("RowKey") the query executes without exception.

[wmengmsft]

@marxxxx, the issue is that table.CreateQuery<TelemetryTableEntity>() returns a TableQuery object that is intended for Linq expression based queries. It cannot be used with fluent style .Where() based query pattern.

In previous versions of Table SDK this would throw an error (NotSupportedException). In this version we have a bug where this validation is not happening so the behavior wasn't correct. We will fix this ASAP.

If you modify the code to do a new TableQuery<CustomerEntity>().Where(...) as in the sample, this should work.

[marxxxx]

@wmengmsft Thank you for your response! I can confirm that the other approach, using new TableQuery<T> worked. Are linq expression based querys already supported? I could not find an example for it in the Sample-Repository.

[donghexu]

Hi @marxxxx no actually, linq expression right now doesn't support order by. Closing the issue.