I have an SQL Server query of the kind:
WITH MostRecentToday AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY ItemGuid ORDER BY Version DESC) AS rn
FROM Items
WHERE Cast(Timestamp As date) = '2023-06-08' and ABC = 'foo' and XYZ = 123
)
SELECT *
FROM MostRecentToday
WHERE rn = 1
How can I write this as an NHibernate query? There doesn't seem to be much information publically available that indicates how to write an NHibernate query which uses Common Table Expressions.