Only get latest results using nHibernate

Question

I have a nHibernate query like this

ICriteria query = session.CreateCriteria(typeof(MyResult))
            .Add(Expression.Eq("ResultTypeId", myResult.ResultTypeId))

Problem is that users add results all the time and I want to show a table of all the latest results for all the diferent ResultTypes I have.

The MyResult class has a property ResultDate. My question is, what do I add to the query to get it to only return the latest result for the given result type. There is nothing to say that the results will be in date order in the database.

Thanks,

Mark

Answer 1

Order by ResultDate (descending) and select top whatever you feel appropriate.

Answer 2

You can order the result by ResultDate using the AddOrder method, as below:

ICriteria query = session.CreateCriteria(typeof(MyResult))
        .Add(Expression.Eq("ResultTypeId", myResult.ResultTypeId))
        .AddOrder(Order.Desc("ResultDate"))
        .List<MyResult>();

If you want to limit the number of MyResult instances you get back, you can use the SetMaxResults method, like so:

ICriteria query = session.CreateCriteria(typeof(MyResult))
        .Add(Expression.Eq("ResultTypeId", myResult.ResultTypeId))
        .AddOrder(Order.Desc("ResultDate"))
        .SetMaxResults(20)
        .List<MyResult>();

Answer 3

If I understand the question well, Mark wants to see an overview of all the last results for each type.

Which means that, for every result type, he only wants to see only one row, and that is the Result which has last been added for that type.

I think that, the easiest way to achieve this, would be to create an additional class, which we can call 'MyResultOverview' for instance:

public class MyResultOverview
{
    public int ResultId {get; set;}
    public int ResultTypeId {get; set;}
    public DateTime ResultDate {get; set;}
}

This class should not be mapped, but NHibernate should be aware that this class exists. Therefore, we'll have to import it:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" >
   <import class="MyResultOverview" />
</hibernate-mapping>

Then, we can create an ICriteria which will populate instances of MyResultOverview (and which will also generate the most efficient SQL Query in order to get this overview). It should look something like this:

ICriteria criteria = session.CreateCritera (typeof(MyResult));

criteria.SetProjection (Projections.ProjectionList ()
                           .Add (Projections.Property("Id"), "ResultId")
                           .Add (Projections.Property("ResultType"), "ResultType")
                           .Add (Projections.Max("ResultDate"), "ResultDate"));

criteria.SetResultTransformer (Transformers.AliasToBean (typeof(MyResultOverview)));

IList<MyResultOverview> results = criteria.List<MyResultOverview>();

This should give you a list of MyResultOverview instances which represent the MyResults that you're looking for. Then, in order to retrieve the MyResult itself, you can simply do this by retrieving the MyResult instance for that particalur ResultId that you've retrieved as well.

I haven't tested this, nor did i compile it, but this is the path that I would follow to achieve this.