NHibernate Grouping Query

Question

I have a table like so

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

I want to query it via an NHibernate session so that the first row for each Ref that contains a given search string is returned, so with a search term of 'Myles' the result set would be

Id   |  Ref    |   Field1
1       Myles1     Blah
3       Myles2     Custard

Fluent mapping like so

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

The first row meaning the first row from each group with a hit on the ref search. Actually, in this case it's doesn't have to be first as long as I only get on row for each ref — Myles McDonnell, Jul 16 '15 at 12:44
Rows in a relational database do not have a natural order, so there is no "first row" until you decide for an ordering in the query. — Stefan Steinegger, Jul 16 '15 at 13:03

score 1 · Answer 1 · edited May 23 '17 at 12:14

There is a detailed description how complex queries (with HAVING, GROUP BY) we can do: Query on HasMany reference

In case, we would like to find out such entities, which have the Min(Id) grouped by Ref, we would need subquery. It could look like this:

// 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);

Check more in the doc 16.8. Subqueries or here

I think there is no one-to-many in place. A simple Min(Field1), Group by Ref, Having blah would be sufficient. — Stefan Steinegger, Jul 16 '15 at 12:42
Correct, there is no one to many..will add the mapping class to the question. Can you provide an example please Stefan? Thanks — Myles McDonnell, Jul 16 '15 at 13:05

NHibernate Grouping Query

1 Answers1