Got "ORDER BY" error after upgrade from v4 to v5.1

My application works quite well with v4.
After upgraded to v5.1 OrmLite, this error occurs when get a simple list:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.


So my table relationships: [Person].[ContactId] -> [Contact].[Id]

My codes look like:

string[] include = null; // in real application, this will be passed in from method parameters
var personQuery = db.From<Person>();
personQuery.OrderByFields("Id");
db.LoadSelectAsync(personQuery, include);

The generated error sql from SQL Profiler:

SELECT “FirstName”, “LastName”, “Name”, “DateOfBirth”, “Title”, “Position”, “Gender”, “JobTitle”, “Phone”, “Mobile”, “Fax”, “Email”, “Id” FROM “Contact” WHERE “Id” IN (SELECT “Person”.“ContactId”
FROM “Person”
ORDER BY “Id”)


Please help to fix. Thanks.

What RDBMS are you using and what v4.x version did you move from?

I’ve added a LoadSelectAsync test with an Order By on a table with a 1:1 and 1:M Reference in this commit but I’m unable to reproduce the issue.

I’ve tested this with every SQL Server Dialect provider but neither of them are including the ORDER BY on the subselect, e.g:

SQL: SELECT "Id", "CustomerId", "AddressLine1", "AddressLine2", "City", "State", "Country" FROM "CustomerAddress" WHERE "CustomerId" IN (SELECT "Customer"."Id" 
FROM "Customer")
SQL: SELECT "Id", "CustomerId", "LineItem", "Qty", "Cost" FROM "Order" WHERE "CustomerId" IN (SELECT "Customer"."Id" 
FROM "Customer")

Can you please include a complete example with all the Source code of your classes used including OrmLite DB Factory registration with DialectProvider used which I can run to repro the issue.

My previous version of OrmLite is 4.5.14. I upgraded to latest 5.1 through Nuget the day before yesterday.

I am creating a sample to reproduce the error later today.

My development DB is SQL Server 2017, and using SqlServer2016Dialect.Provider in application.

Hi mythz,

I just created a simple solution, the error is simply reproduced.
Please have a look. Thanks

Download address:
https://ufile.io/hr6b5

Codes in HomeController.cs, when browse the Home page, error occurs.

Thanks for the repro, this should be resolved from this commit.

This change is available now from v5.1.1 on MyGet.

It fixed now. Thank you very much.

1 Like