Nhibernate not returning right result sometime

Question

Hi Guys, So here is my nhibernate query. I am just querying in database if there is latest entry by some number but with latest time stamp.

So like if I have

Row A   6     1/7/2010 2:55:59 PM
Row B   6     1/7/2010 2:56:33 PM

So According to the query it should return Row B but sometimes it is not happening. Can anyone point out the problem in my hibernate query.

DetachedCriteria subquery = DetachedCriteria.For(typeof(x));
        subquery.SetProjection(Projections.Max("Time"));


        X x = (X)_mapper.Run(delegate(ISession session, object[] arguments)
        {
            ICriteria criteria = session.CreateCriteria(typeof(X));
            criteria.Add(Expression.Eq("ID", ID));
            criteria.Add(Subqueries.PropertyEq("Time", subquery));
            return criteria.UniqueResult();
        }, true);

        return x;

Answer 1

Your subquery isn't filtered down by ID like the actual query - if the maximum time regardless of ID happens to be on a row with the ID in question, you'll get the row you want; if it doesn't, you won't. You need to add a filter where you create the subquery:

.Add( Expression.Eq("ID", ID) )