如何使用NHibernate编写含公共表表达式（CTE）的查询？

原SQL核心逻辑为：筛选出2023-06-08当天、ABC='foo'且XYZ=123的Items记录，按ItemGuid分组后取每组中Version最大的最新一条。以下提供几种NHibernate实现方式：

方法1：使用HQL（支持窗口函数，NHibernate 5.1+） #

NHibernate 5.1及以上版本支持窗口函数，可直接复刻原SQL逻辑：

var hql = @"
WITH MostRecentToday AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ItemGuid ORDER BY Version DESC) AS rn
    FROM Items
    WHERE Cast(Timestamp As date) = :dateFilter and ABC = :abcValue and XYZ = :xyzValue
)
SELECT *
FROM MostRecentToday
WHERE rn = 1";

var session = sessionFactory.OpenSession();
var result = session.CreateQuery(hql)
    .SetParameter("dateFilter", new DateTime(2023, 6, 8))
    .SetParameter("abcValue", "foo")
    .SetParameter("xyzValue", 123)
    .List<Item>();

方法2：使用QueryOver API #

若偏好强类型查询，可通过子查询实现等价逻辑（适配不支持窗口函数的旧版NHibernate）：

var session = sessionFactory.OpenSession();

// 子查询：获取每个ItemGuid对应的最大Version值
var maxVersionSubquery = QueryOver.Of<Item>()
    .Select(Projections.Max<Item>(i => i.Version))
    .Where(Restrictions.EqProperty("ItemGuid", "outer.ItemGuid"))
    .And(Restrictions.Eq("ABC", "foo"))
    .And(Restrictions.Eq("XYZ", 123))
    .And(Restrictions.Where<Item>(i => i.Timestamp.Date == new DateTime(2023, 6, 8)));

// 主查询：关联子查询筛选每组最新记录
var result = session.QueryOver<Item>("outer")
    .Where(Restrictions.Eq("ABC", "foo"))
    .And(Restrictions.Eq("XYZ", 123))
    .And(Restrictions.Where<Item>(i => i.Timestamp.Date == new DateTime(2023, 6, 8)))
    .WithSubquery.WhereProperty(i => i.Version).Eq(maxVersionSubquery)
    .List<Item>();

注意：此方式仅在ItemGuid与Version组合唯一时生效，若同一ItemGuid、同一Version存在多条记录，需额外添加排序字段的子查询处理。

方法3：使用原生SQL查询 #

若HQL窗口函数支持存在问题，可直接执行原生SQL并映射到实体：

var session = sessionFactory.OpenSession();
var sql = @"
WITH MostRecentToday AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ItemGuid ORDER BY Version DESC) AS rn
    FROM Items
    WHERE Cast(Timestamp As date) = @dateFilter and ABC = @abcValue and XYZ = @xyzValue
)
SELECT *
FROM MostRecentToday
WHERE rn = 1";

var result = session.CreateSQLQuery(sql)
    .AddEntity(typeof(Item))
    .SetParameter("dateFilter", new DateTime(2023, 6, 8))
    .SetParameter("abcValue", "foo")
    .SetParameter("xyzValue", 123)
    .List<Item>();

内容的提问来源于stack exchange，提问作者Mike