CODEWITHSUNDEEP
sql-serverservicestackormlite-servicestack
kaptan
kaptan

Reputation: 3149

servicestack ormlite throws "The ORDER BY clause is invalid ..." sql exception when using orderby with References

I have models like:

class Request
{
   public int RequestId {get;set;}
   [Reference]
   public List<Package> Packages {get;set;}
}

class Package 
{
   public int PackageId {get;set;}
   public inst RequestId {get;set;}
}

if I run: 

db.LoadSelect<Request>(q => q.OrderBy(x => x.RequestId));

OrmLite will generate sqls like:

SELECT "RequestId"  FROM "Request" ORDER BY "RequestId" ASC

SELECT "PackageId", "RequestId" FROM "Package"
WHERE "RequestId" IN (SELECT "Request"."RequestId" FROM "Request" ORDER BY "RequestId" ASC)

which will raise the following sql error:

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

and the reason is obviously the ORDER BY in the subquery of the second query.

So there are two points here:

  1. Is this a bug in OrmLite Sql Provider?
  2. How to write ormlite queries to load models with references and at the same time sort them?

Upvotes: 0

Views: 515

Answers (1)

mythz
mythz

Reputation: 143349

Hmmm, not being able to use ORDER BY in Sub Selects seems to be an Sql Server specific limitation. But as it shouldn't affect the behavior, I've cleared the ORDER BY term used in Load References sub selects in this commit.

This change is available from v4.0.33+ that's now available on MyGet.

Upvotes: 1

Related Questions