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”)
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.