NHibernate Criteria - How to use a group according to the statement?

Question

I have a problem's. I need to select from database entries for people with identifiers that are in the externalIds list. After that, I need to select for each person only 1 entry with the latest StartTime. I tried, for example, with SetProjection (GroupProperty and Max property), but as a result, it returns me only the StartTime list when I need the PersonnelPresence list. My method looks like this:

public IList<PersonnelPresence> GetLastPersonnelPresencesForPeopleExternalIds(IList<string> externalIds) { ICriteria criteria = Session.CreateCriteria(typeof(PersonnelPresence), "pp").CreateCriteria("pp.Person", "p") .Add(Restrictions.In("p.ExternalId", externalIds.ToList())) .SetProjection(Projections.ProjectionList() .Add(Projections.GroupProperty("p.Id")) .Add(Projections.Max("pp.StartTime"))); return criteria.List<Object>() as List<PersonnelPresence>; } 

Does anyone know how to solve my problem? Thanks in advance.

Answer 1

It has been a long time, but still I would like to help a little.

With such complex queries, I find that it helps to type SQL first; if you have an SQL statement that gives you the desired results, it’s easier to translate the query into ICriteria.

If possible, I recommend using the Query (not QueryOver) FluentNHibernate syntax as it is much easier to maintain and more flexible in complex cases like yours.

Answer 2

I had a similar problem, so if someone wanted something like this, I managed to solve by dividing the request into two.

Something like that:

 public IList<PersonnelPresence> GetLastPersonnelPresencesForPeopleExternalIds(IList<string> externalIds) { var criteriaPersonnelPresenceNewest = DetachedCriteria.For<PersonnelPresence>("PP_") .Add(Restrictions.In("PP_.ExternalId", externalIds.ToArray()())) .Add(Restrictions.EqProperty("PP_.ExternalId", "PP2_.ExternalId"))//to compare with the query down below .SetProjection(Projections.ProjectionList() .Add(Projections.Max("PP_.StartTime")) ); var criteriaPersonnelPresence = Session.CreateCriteria<PersonnelPresence>("PP2_") .Add(Subqueries.PropertyEq("PP2_.StartTime", criteriaPersonnelPresenceNewest)) .SetProjection(Projections.ProjectionList() .Add(Projections.Property("PP2_.Id")) .Add(Projections.Property("PP2_.StartTime")) ) .ToList<PersonnelPresence>(); return criteriaPersonnelPresence; }