search

JocaPC / sql-server-rest-api

Other
67 stars 16 forks source link

$expand parameter has error if without any advanced queries against the sub-entities. #11

Closed Rich-AU closed 4 years ago

Rich-AU commented 4 years ago

I donwloaded the TestAPP project and run it under Visual Studiao 2019, everything worked fine except for the $expand parameter, when I sent a query with the $expand parameter without any advanced queries against the sub-entities, such as - "http://localhost:53025/odata?$filter=PersonID eq 21&$expand=Orders", I got an error message "{"code":-1,"message":{"lang":"en","value":"The parse operation was cancelled.","innererror":"Exception of type 'Antlr4.Runtime.InputMismatchException' was thrown."}}".

However, if I sent the query with any advanced queries against the sub-entities, such as "http://localhost:53025/odata?$filter=PersonID eq 21&$expand=Orders($top=2)" or "http://localhost:53025/odata?$filter=PersonID eq 21&$expand=Invoices($orderby=InvoiceID)", then everything worked fine.

Environement: Windows 10 / SQL Server 2019 Express /Visual Studio 2019 (all in one machine)

Thanks.

By the way, this is a great tool and so easy to use, thanks for your effort, JocaPC!

JocaPC commented 4 years ago

Hi @Rich-AU. Thanks for reporting this and sorry for delayed response. I worked on some re-factoring because I want to enable this library to use multiple data access libraries Belgrade and Dapper. I completed refactoring and made several changes/bug fixes.

Could you please try to use new NuGet packages Dapper.TSql.RestApi and Belgrade.TSql.RestApi and try again? I will deprecate the current nuget because Belgrade.TSql.RestApi is improved version that I will keep maintaining. See example of OData service that supports $expand option in this sample: https://github.com/JocaPC/sql-server-rest-api/tree/dapper-odata-api

Rich-AU commented 4 years ago

Great, definetly will give it a go!

Rich-AU commented 4 years ago

Tried the new library and can confirm this bug has been fixed, thanks!

And just to share my experience of using a few different Odata libarary which shows this library is way faster then others I tried.

I have a SQL server table with about 1.1 million rows, 15 columns. Using this library to query the first 100 rows (by using $top=100) of this table, the results were: This libary: 20-40ms Another libary built on MS odata.net: 1010ms -1200ms An open source Enterprice level intergration middleware (java) provided Odata service (based on jdbc and Apache Olingo): Out of memory then crash! (I guess it needs get all the rows first).