Max date in an nHibernate HQL query

hqlicriterianhibernate

I am having some difficultly writing an hql query that will search a table for all the rows where certain criteria are met but I only want to return the row with the max date.

For example the where clause of the query would be something like:

column1 = input1 and column2 = input2 and date_column < date_input

This might return several rows with different dates but I am only interested in the one with the latest date. I also need it to return the entire row.

Presently, I am executing the query with the criteria above ordered descending by date and then fetching the first item in C#. I would like to have an hql query or iCriteria solution that can do this in one step if possible.

Best Answer

You just need to use a subquery. Select the max date from the qualifying rows and then select out all records which match the max date. My HQL is a little rusty but you get the idea.

from TableA a 
where a.Date in (
    select max(b.Date) 
    from TableA b 
    where b.Something="You get the idea"
)

Subqueries, Aggregate Function

Related Solutions

C# – NHibernate Projections and “Having” clause

You can access the ProjectionCriteria from the Criteria object.

...
c.SetProjection(projections)
.ProjectionCriteria
.Add(Restrictions.Ge("Units", 0));

EDIT: This solution doesn't currently work, however it should work in NHibernate 2.1.0

C# – NHibernate Criteria with multiple results

I would presume that the answer lies in prefetching the associations that you want to. That way you can fetch the needed part of your object graph in a single shot. You would do this in criteria queries like so.

ICriteria query = session.CreateCriteria(typeof (Cat))
.SetFetchMode("Mate", FetchMode.Join)
.SetFetchMode("Kittens", FetchMode.Join);

IList<Cat> results = query.List<Cat>();

This will give you back a list of cats with both the Mate and the Kittens prepopulated. You can then navigate to these properties without incurring an N+1 penalty. If you need a more flattened result I'd do it using linq, perhaps like this.

var results = query.List<Cat>()
  .Select((c) => new {mother = c, mate = c.Mate, offspr = c.Kittens});

This will give you back a flattened list of anonymous types with the given properties. This will work if all you need is prefetching the object graph. However if you need to prefetch things such as counts or sums then you will need to examine the Projections and Alias parts of Criteria queries.

One more thing. If you are trying to exactly duplicate your above query, you can do so like this.

ICriteria query = session.CreateCriteria(typeof (Cat), "mother")
.CreateAlias("Mate", "mate", JoinType.InnerJoin)
.CreateAlias("Kittens", "offspr", JoinType.LeftOuterJoin)
.SetResultTransformer(Transformers.AliasToEntityMap);

This will return you basically the same as your hql query, however instead of using an indexed list it will use a dictionary that maps the alias to the entity.

Best Answer

Related Solutions

C# – NHibernate Projections and “Having” clause

C# – NHibernate Criteria with multiple results

Related Topic