NHibernate Select Max value with filter

filter, max, nhibernate

Solution

Using the criteria syntax:

var criteria = session.CreateCriteria<MyTable>();
criteria.Add(Restrictions.Eq("ItemId", itemId));
criteria.SetProjection(Projections.Max("Sequence"));
var max = criteria.UniqueResult<int>();

Using the query over syntax:

var max = session.QueryOver<MyTable>().Where(x => x.ItemId.Equals(itemId)).Select(
                Projections.Max<MyTable>(x => x.Sequence)).SingleOrDefault<int>();

Problem

I am trying to get the max value from a table, doing something like this: ``` SELECT max(re.Sequence) FROM MyTable re WHERE re.ItemId = :itemId ``` So i can get for each itemId the maximum value for the column Sequence. Tried with createQuery but didn´t worked: ``` string hql = @"SELECT new Int32(max(re.Sequence) FROM MyTable re WHERE re.Item.Id = :itemId"; List<Int32> lista = session .CreateQuery(hql) .SetParameter("itemId", idItem) .List<Int32>() .ToList(); ``` Any help will be appreciated. Best Regards.

Original source