Unsupported $orderby command

jonicis commented 5 years ago

I have a simple Azure Function that connects to an Azure SQL database and exposes a table as OData API:

        [FunctionName("activity")]
        public static async Task<IActionResult> Run([HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = null)] HttpRequest req, ILogger log)
        {
            var connectionString = Environment.GetEnvironmentVariable("MockDatabaseConnectionString");
            var tableSpec = new TableSpec("pln", "Activities");

            return await req
                .OData(tableSpec)
                .GetResult(connectionString);
        }

When I try to use $orderby I get this error message:

Command is not defined.

The URL I tried is: http://localhost:7071/api/activity?$orderby=object_number And I am using the MsSql.RestApi nuget package version 0.5.1

JocaPC commented 5 years ago

Hi,

I don't see that you defined the columns in the TableSpec see this - https://github.com/JocaPC/sql-server-rest-api/blob/master/TestApp/Controllers/RestApiController.cs#L43 I see that this column has underscore - could you try to use order by with some other column without Here is another example of setup: https://github.com/JocaPC/sql-server-rest-api/blob/master/TestFunction/ODataResult.cs

Could you share the table schema (without data)?

Jovan

jonicis commented 5 years ago

Yes, listing the columns in the definition fixes the problem. It is confusing because I don't need to define the columns when using other operations like $select.

Now, I have a column with the name "database", if I include it in the $select, it returns:

Incorrect syntax near the keyword 'database'.

I have to use "[database]" instead, but it would be nice if the enclosing brackets are added when building the SQL query in all columns to avoid having to add them in the URL.

Thanks.

JocaPC / sql-server-rest-api

Unsupported $orderby command #9