10

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.

keithyip
  • 985
  • 7
  • 21
  • have you tried replicating the issue without NHibernate? – Neil Jul 30 '19 at 09:36
  • Yes, I discovered this problem when changing the code to use NH from EF. The in-memory version and EF gave the same result. NH gave a different result. I reduced the code to the above version. – keithyip Jul 31 '19 at 11:07
  • Could you provide what SQL query generate NHibernate? – Alexander I. Aug 05 '19 at 13:22
  • My SQL statement skill is very rusty. Giving one will probably redirect the solution to something weird. It seems that the solution should be something like `select * from A ??? join (select ??? from A groupby X ???)` – keithyip Aug 09 '19 at 11:36

1 Answers1

10

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();
Roman Artiukhin
  • 2,200
  • 1
  • 9
  • 19
  • Is it possible to do a join version? I get a NotSupported exception using LINQ. It seems that I have to use QueryOver but I still have no ideas after searching on the Internet. – keithyip Aug 09 '19 at 13:56
  • Not sure what you meant by "join version". But afaik joins on subquery are not supported in NHibernate LINQ/QueryOver. – Roman Artiukhin Aug 09 '19 at 16:22