Get the latest results only with nHibernate

Question

I have an nHibernate request like this

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

The problem is that users are adding results all the time, and I want to show a table of all the latest results for all the different ResultTypes that I have.

The MyResult class has a ResultDate property. My question is: what am I adding to the query in order to get it in order to return only the last result for this type of result. Needless to say, the results will be in order of date in the database.

Thanks,

Mark

Answer 1

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

This means that for each type of result, he wants to see only one row, and this is the result that was added last for this type.

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

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

This class should not be displayed, but NHibernate should know that this class exists. Therefore, we will have to import it:

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

We can then create an ICriteria that will populate MyResultOverview instances (and which will also generate the most efficient SQL query 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 the MyResultOverview instances that are the MyResults you are looking for. Then, to extract MyResult yourself, you can simply do this by extracting an instance of MyResult for this particalur ResultId, which you also received.

I have not tested this or compiled it, but this is the path that I will follow.

Answer 2

You can order the result by ResultDate using the AddOrder method, as shown 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 that you will return, you can use the SetMaxResults method, for example:

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

Answer 3

Order ResultDate (descending) and select the top one that will be convenient for you.

Answer 4

In HQL, it can work:

 select item, tag from MyItem item join item.Tags tag where tag.Id = ( select max(tag2.Id) from MyItem item2 join item2.Tags tag2 where item2.Id = item.Id group by item2.Id )