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

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

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

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

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)

Thanks! The project can be found here:

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));

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