如何在NHibernate中查询每个组中的第一个条目 [英] How to query the first entry in each group in NHibernate

问题描述

以下在NHibernate中使用LINQ的代码返回的结果与内存中LINQ和EF LINQ不同.在NHibernate中执行此操作的正确方法是什么?如果LINQ版本确实损坏，则可以使用 QueryOver .

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
}

预期结果

1 2

实际结果

1 1

记录的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

完整代码位于错误报告将GroupBy与First一起使用时，结果不正确

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

更新2019-8-9

Update 2019-8-9

查询不应使用ID.我已将其更改为非唯一属性.如果解决方案仅查询一次SQLite，我将不胜感激.

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.

推荐答案

似乎最新的NHibernate 5.2 LINQ提供程序仅在 Select 中为"group"支持聚合函数(MIN，MAX，COUNT ...).通过"查询.

It seems latest NHibernate 5.2 LINQ provider supports only aggregate functions (MIN, MAX, COUNT...) in Select for "group by" query.

我相信在HQL中，您可以使用以下汇总函数来重写您的查询:

I believe in HQL your query can be rewritten using aggregate functions as:

session.CreateQuery(
    "select a from A a where a.Id in "
        + "(select min(ga.Id) from A ga group by ga.X)").List<A>();

但是不幸的是，由于此错误(在NHibernate 5.2中，按子查询分组会引发异常).因此，目前在LINQ中可以通过2个查询来完成:

But unfortunatly it can't be expressed in LINQ in one query due to this bug (in NHibernate 5.2 group by subquery throws exception). So for now in LINQ it can be done via 2 queries:

var subquery = session.Query<A>()
    .GroupBy(ga => ga.X)
    .Select(ga => ga.Min(a => a.Id))
    .ToList();//when bug is fixed this ToList call can be omitted and make results retrieved in single db call

var results = session.Query<A>().Where(a => subquery.Contains(a.Id)).ToList();

此外，似乎也可以避免子查询中的分组依据"，因此也可以使用单个查询LINQ版本:

Also it seems "group by" in subquery can be also avoided so single query LINQ version is also possible:

var results = session.Query<A>()
     .Where(a => a.Id == session.Query<A>().Where(sa => sa.X == a.X).Select(sa => sa.Id).Min())
     .ToList();

//or version closer to your original query:
var results = session.Query<A>()
     .Where(a => a == session.Query<A>().Where(sa => sa.X == a.X).OrderBy(sa => sa.Id).First())
     .ToList();

更新因此，在最新更新中，您仍然可以使用非LINQ分组版本:

Update So with your latest update you still can use non group by LINQ version:

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

这篇关于如何在NHibernate中查询每个组中的第一个条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！