OrmLite OrderByDescending for DateTime field with SelectMulti

ServiceStack.OrmLite
1

I am trying to sort an OrmLite expression by a DateTime field CreatedDate descending but the logged SQL shows that DESC isn’t being included. Switching to sorting by the Id field the SQL is correct. I’m using the SqlExpression in a call to SelectMulti() but if I use Select() then DESC will be included correctly.

Is there something wrong with the way I create the SqlExpression?

var q = db.From<Notification>()
	.Join<UserAuth>()
	.Join<UserAuth, UserAuthRole>()
	.Where<UserAuthRole>(r => r.RefId == companyId && r.RefIdStr == nameof(Company))
	.OrderByDescending<Notification>(n => n.CreatedDate);
return db.SelectMulti<Notification, UserAuth>(q);

Here is the resulting SQL from the log;

SELECT "Notification".*, 0 EOT, "UserAuth".*, 0 EOT 
FROM "Notification" INNER JOIN "UserAuth" ON
("UserAuth"."Id" = "Notification"."UserAuthId") INNER JOIN "UserAuthRole" ON
("UserAuth"."Id" = "UserAuthRole"."UserAuthId")
WHERE (("UserAuthRole"."RefId" = @0) AND ("UserAuthRole"."RefIdStr" = @1))
ORDER BY "Notification"."CreatedDate"
PARAMS: @0=1, @1=Company

Which works with Id;

SELECT "Notification".*, 0 EOT, "UserAuth".*, 0 EOT 
FROM "Notification" INNER JOIN "UserAuth" ON
("UserAuth"."Id" = "Notification"."UserAuthId") INNER JOIN "UserAuthRole" ON
("UserAuth"."Id" = "UserAuthRole"."UserAuthId")
WHERE (("UserAuthRole"."RefId" = @0) AND ("UserAuthRole"."RefIdStr" = @1))
ORDER BY "Notification"."Id" DESC
PARAMS: @0=1, @1=Company

Or when not selecting multi;

SELECT "Notification"."UserAuthId", "Notification"."NotificationTemplateId", "Notification"."DataValues", "Notification"."Message", "Notification"."Html", "Notification"."Text", "Notification"."Result", "Notification"."Id", "Notification"."CreatedBy", "Notification"."ModifiedBy", "Notification"."CreatedDate", "Notification"."ModifiedDate" 
FROM "Notification" INNER JOIN "UserAuth" ON
("UserAuth"."Id" = "Notification"."UserAuthId") INNER JOIN "UserAuthRole" ON
("UserAuth"."Id" = "UserAuthRole"."UserAuthId")
WHERE (("UserAuthRole"."RefId" = @0) AND ("UserAuthRole"."RefIdStr" = @1))
ORDER BY "Notification"."CreatedDate" DESC
PARAMS: @0=1, @1=Company
2

Sorry, not sure what I’ve been looking at but the sorting issue was in the UI not the DAL.

I was sure the log supported my assumptions but having refactored to use LoadSelect() and decorated Notification with some Reference attributes I found the real issue. Curiously I went back to my original code and the logging now shows DESC appearing.

Best stop for now for the holidays …

1 Like