NHibernate分组查询

Question

我有一张这样的桌子

Id   |  Ref    |   Field1
1       Myles1     Blah
2       Myles1     Rubarb
3       Myles2     Custard
4       Ted        Cheese

我希望通过NHibernate会话查询它，以便返回包含给定搜索字符串的每个引用的第一行，因此，如果搜索项为'Myles‘，则结果集将为

Id   |  Ref    |   Field1
1       Myles1     Blah
3       Myles2     Custard

像这样的流畅映射

public class EntityClassMap : ClassMap<Entity>
{
    public EntityClassMap()
    {
        Id(x => x.Id);
        Map(x => x.Ref);
        Map(x => x.Field1);           
    }
}

Answer 1

有一个详细的描述，我们可以如何进行复杂的查询(使用HAVING__，GROUP BY__)：Query on HasMany reference

以防万一，我们想找出这样的实体，其中具有按Ref，分组的Min(Id)我们将需要subquery。它可能看起来像这样：

// alias for inner query
MyEntity inner = null;
// this alias is for outer query, and will be used in 
// inner query as a condition in the HAVING clause
MyEntity outer = null;

var minIdSubquery = QueryOver.Of<MyEntity>(() => inner)
    .SelectList(l => l
        .SelectGroup(() => inner.Ref) // here we GROUP BY
        .SelectMin(() => inner.Id)
    )
    // HAVING to get just Min(id) match
    .Where(Restrictions.EqProperty(
      Projections.Min<MyEntity>(i => i.Id),
      Projections.Property(() => outer.Id)
    ));

// outer query
var query = session.QueryOver<MyEntity>(() => outer)
    .WithSubquery
    // we can now use EXISTS, because we applied match in subquery
    .WhereExists(minIdSubquery);

在文档16.8. Subqueries或here中查看更多信息