NHibernate中如何正确查询每组第一条数据？LINQ结果异常求解

这个问题我之前碰到过，NHibernate的LINQ Provider在处理GroupBy后嵌套OrderBy+First的逻辑时，确实会生成不符合预期的SQL——就像你看到的，它没有把分组条件带入子查询，导致子查询总是取整个表的第一条数据，而不是每组的第一条，所以才会出现实际结果重复的情况。

下面给你几种能正确实现“查询每组第一条数据”的方案，都能满足单次查询SQLite的要求：

方案一：用窗口函数（推荐，高效且直观） #

如果你的SQLite版本在3.25及以上（支持窗口函数），可以用NHibernate LINQ提供的RowNumber()扩展来实现，这是最简洁高效的方式：

using (var session = factory.OpenSession())
using (var transaction = session.BeginTransaction())
{
    var actual = session.Query<A>()
        .Select(a => new 
        {
            Entity = a,
            RowNum = NHibernate.Linq.SqlExtension.RowNumber().Over()
                .PartitionBy(a.X) // 按X分组
                .OrderBy(a.Y)     // 每组内按Y升序排序
        })
        .Where(result => result.RowNum == 1) // 取每组的第一条
        .Select(result => result.Entity)
        .ToList();

    Console.WriteLine(string.Join(" ", actual.Select(a => a.Id)));
}

这个写法会生成带ROW_NUMBER()窗口函数的SQL，正确按X分组并取每组排序后的第一条记录，完全符合你的预期。

方案二：子查询关联（兼容旧版本SQLite） #

如果你的SQLite版本不支持窗口函数，可以用子查询找到每组的最小Y值（因为你按Y排序取第一条），再关联原表获取对应记录：

using (var session = factory.OpenSession())
using (var transaction = session.BeginTransaction())
{
    var actual = session.Query<A>()
        .Where(a => a.Y == session.Query<A>()
            .Where(a2 => a2.X == a.X)
            .Min(a2 => a2.Y))
        .ToList();

    Console.WriteLine(string.Join(" ", actual.Select(a => a.Id)));
}

注意 #

如果同一组内存在多个Y值相同的记录，这个写法会返回所有符合条件的记录。如果需要确保每组只返回一条，可以再加一个条件（比如取最小的Id）：

var actual = session.Query<A>()
    .Where(a => 
        a.Y == session.Query<A>().Where(a2 => a2.X == a.X).Min(a2 => a2.Y) &&
        a.Id == session.Query<A>().Where(a2 => a2.X == a.X && a2.Y == a.Y).Min(a2 => a2.Id))
    .ToList();

方案三：使用QueryOver实现 #

如果你更习惯用QueryOver（NHibernate的强类型查询API），这里也提供两种写法：

写法一：子查询关联 #

using (var session = factory.OpenSession())
using (var transaction = session.BeginTransaction())
{
    A alias = null;
    A subAlias = null;

    var actual = session.QueryOver<A>(() => alias)
        .WithSubquery.WhereProperty(() => alias.Y)
            .Eq(QueryOver.Of<A>(() => subAlias)
                .Where(() => subAlias.X == alias.X)
                .Select(Projections.Min(() => subAlias.Y)))
        .List();

    Console.WriteLine(string.Join(" ", actual.Select(a => a.Id)));
}

写法二：窗口函数（兼容支持窗口函数的数据库） #

using (var session = factory.OpenSession())
using (var transaction = session.BeginTransaction())
{
    var rowNumber = Projections.SqlFunction(
        "row_number",
        NHibernateUtil.Int32,
        Projections.Property<A>(a => a.X),
        Projections.Order<A>(a => a.Y).Asc
    );

    var actual = session.QueryOver<A>()
        .Select(Projections.ProjectionList()
            .Add(Projections.Id(), "Id")
            .Add(Projections.Property<A>(a => a.X), "X")
            .Add(Projections.Property<A>(a => a.Y), "Y")
            .Add(rowNumber, "RowNum"))
        .Where(Restrictions.Eq("RowNum", 1))
        .TransformUsing(Transformers.AliasToBean<A>())
        .List();

    Console.WriteLine(string.Join(" ", actual.Select(a => a.Id)));
}

为什么原写法会出错？ #

你原来的LINQ写法在内存LINQ和EF里能正常工作，但NHibernate的LINQ Provider对这种分组后直接取排序后第一条的逻辑解析有缺陷——生成的SQL中，子查询没有关联外层的分组条件X，导致子查询总是返回整个表中Y最小的那条记录（也就是Id=1的那条），最终结果自然重复。

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