How to query the first entry in each group in NHibernate

Question

The following code using LINQ in NHibernate returns a different result from in-memory LINQ and EF LINQ. What is the correct way to do this in NHibernate? It is fine to use QueryOver if the LINQ version is indeed broken.

using (var session = factory.OpenSession())
using (var transaction = session.BeginTransaction())
{
    for (int i = 0; i < 10; ++i)
    {
        session.Save(new A()
        {
            X = i % 2,
            Y = i / 2,
        });
    }
    transaction.Commit();
}
using (var session = factory.OpenSession())
using (var transaction = session.BeginTransaction())
{
    //=====================================
    var expected = session.Query<A>()
        .ToList() // <-- copy to memory
        .GroupBy(a => a.X)
        .Select(g => g.OrderBy(y => y.Y).First())
        .ToList();
    Console.WriteLine(string.Join(" ", expected.Select(a => a.Id)));
    //=====================================
    var actual = session.Query<A>()
        .GroupBy(a => a.X)
        .Select(g => g.OrderBy(y => y.Y).First())
        .ToList();
    Console.WriteLine(string.Join(" ", actual.Select(a => a.Id)));
}

public class A
{
    public int Id { get; set; }
    public int X { get; set; } // indexed
    public int Y { get; set; } // indexed
}

Expected results

1 2

Actual results

1 1

Logged SQL

NHibernate: select (select program_a0_.Id as id1_0_ from "A" program_a0_ order by program_a0_.Y asc limit 1) as col_0_0_ from "A" program_a0_ group by program_a0_.X

The full code is in the bug report Incorrect result when using GroupBy with First

---

Update 2019-8-9

The query should not use ID. I have changed it to a non-unique property. I would appreciate if the solution only query once to SQLite.

Answer 1

It seems latest NHibernate 5.3 LINQ provider supports only aggregate functions (MIN, MAX, COUNT...) in Select for "group by" query. Entity select is not supported in group by queries.

As a general solution you can rewrite your "group by" query with subquery using the following approach:

var results = session.Query<A>()
     .Where(a => a == session.Query<A>() // Subquery on same entity
                         .Where(sa => sa.X == a.X) // Group BY key is here
                         .OrderBy(sa => sa.Y) // Order By key is here
                         .First() // First entry in group
     ).ToList();

Original "group by" query for reference:

var results = session.Query<A>()
    .GroupBy(a => a.X)
    .Select(g => g.OrderBy(y => y.Y).First())
    .ToList();