Using CreateCriteria and selecting row based on maxvalue

Question

Hi,

I want to select a row from one table in the database using CreateCriteria where I have a know value for a column, not primary key so there will be multiple rows matching. The table contains update information so we have a column that contains a version number for each row based on which along with my primary question gives a single unique row.

I have tried something along the following lines which works but it seems that I should be able to use something bette. It would be great if there were some kind of Restriction.Max("Avtal")

var result = Session.CreateCriteria(typeof(Avtal))
.Add(Restrictions.Eq("Avtal", avtal))
.Add(Restrictions.Eq("Versionsnummer", 
Session.CreateCriteria((typeof(Avtal)))
.Add(Restrictions.Eq("Avtal", avtal))
.SetProjection(Projections.Max("version"))
.UniqueResult<int>()))
.List<Avtal>();

Any pointer in the right direction will be very helpful and NO I do not want to use CreateQuery, write HQL directly or something like that, we use Critierias and want to stick to that.

Thanks

Answer 1

I ran across your question when looking for the same thing, then ran across this later on:

var result = Session.CreateCriteria(typeof(Avtal)) .Add(Order.Desc("Versionsnummer")) // sort it and take the first one .UniqueResult())) .List();