Fetching paged data when doing a Left join using OrmLite's [Reference]

ServiceStack Auto Query
1

I’m trying to fetch paged data from a table that left joins many tables. I am using OrmLite’s [Reference] tag in the dto to get data of the left joined tables in my response. However, It throws an exception whenever I send value for Skip & Take but it works fine when no values are sent for Skip & Take. So, I ran SQL profiler and was able to zero in on the problem.
The issue seems to be with middle query (plz see the query or picture below)- the SELECT * applied on top of the inner most query is selecting 2 columns (OperatingSystemid and RowNum) and the outer query is only expecting one.There is really no need for RowNum to be returned as it’s not used in the outer query. I tried replacing * with OperatingSystemId and the query worked fine after that.
The Error message is: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Including the query as well as a screen shot. Please let me know if any other info is required.
SELECT “Id”, “ClientId”, “Display”, “Name”, “OperatingSystemTypeId”, “OperatingSystemVendor”,
“OperatingSystemVersion”, “OperatingSystemPatchLevel”, “Description”, “CreatedOn”, “CreatedBy”,
“UpdatedOn”, “UpdatedBy”
FROM “OperatingSystem”
WHERE “Id” IN
(SELECT * FROM (SELECT “SourceDevice”.“OperatingSystemId”, ROW_NUMBER() OVER (
ORDER BY “SourceDevice”.“Id”) As RowNum
FROM “SourceDevice” LEFT JOIN “SourceDeviceCategory” ON
(“SourceDeviceCategory”.“Id” = “SourceDevice”.“SourceDeviceCategoryId”) LEFT JOIN “OperatingSystem” ON
(“OperatingSystem”.“Id” = “SourceDevice”.“OperatingSystemId”) LEFT JOIN “SourceDeviceCategory_Type” ON
(“SourceDeviceCategory_Type”.“Id” = “SourceDevice”.“SourceDeviceCategory_TypeId”) LEFT JOIN “SourceDeviceEnvironmentType” ON
(“SourceDeviceEnvironmentType”.“Id” = “SourceDevice”.“SourceDeviceEnvironmentTypeId”) LEFT JOIN “MoveGroup” ON
(“MoveGroup”.“Id” = “SourceDevice”.“MoveGroupId”) LEFT JOIN “VirtualServer_VMWare” ON
(“VirtualServer_VMWare”.“Id” = “SourceDevice”.“VirtualServer_VMWareId”) LEFT JOIN “SourceDeviceType” ON
(“SourceDeviceType”.“Id” = “SourceDevice”.“SourceDeviceTypeId”) LEFT JOIN “SourceDeviceServerType” ON
(“SourceDeviceServerType”.“Id” = “SourceDevice”.“SourceDeviceServerTypeId”) LEFT JOIN “SourceDevicePatchingGroup” ON
(“SourceDevicePatchingGroup”.“Id” = “SourceDevice”.“SourceDevicePatchingGroupId”) LEFT JOIN “SourceDeviceDecommissionStatus” ON
(“SourceDeviceDecommissionStatus”.“Id” = “SourceDevice”.“SourceDeviceDecommissionStatusId”)) AS RowConstrainedResult WHERE RowNum > 1 AND RowNum <= 15)

AQ Paging Issue.jpg1240×298 247 KB

2

Please provide a stand-alone Repro we can run locally to see the issue.

5

Please let me know how to upload the stand-alone project and database? I have it ready.

6

You can push the project to github and post a link here to it (it’s preffered way) or use any file service (dropbox, google drive etc)

7

Thanks! The project can be found here:

8

I can’t really run your example without any Test data but I can see that you’re using SqlServer which has some limitations when it comes to paging since it has to try use a native windowing function hack in older versions of Sql Server.

SQL Server added native paging support from 2012, so if you have SQL Server 2012+ you can utilize the native paging support by using the SqlServer2012Dialect.Provider instead, e.g:

container.Register<IDbConnectionFactory>(c => 
    new OrmLiteConnectionFactory(connectionString, SqlServer2012Dialect.Provider));
9

I provided the bacpac file for the database, all the tables has data required to run the sample. But i guess it’s not needed anymore as your answer is spot on. I used SqlServer2016Dialect.Provider and it worked like a charm! I didn’t pay attention to the DbConnectionFactory as I inherited the code from another developer. Thanks a lot for your help!

1 Like