CODEWITHSUNDEEP
sqlnhibernatequeryover
Myles McDonnell
Myles McDonnell

Reputation: 13335

NHibernate Grouping Query

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

Upvotes: 0

Views: 114

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123861

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

Upvotes: 1

Related Questions