NHibernate Subquery

For example, to get the max id:

Classic way:

Session.CreateCriteria(typeof(T))
 .SetProjection(Projections.Max("Id"))
 .UniqueResult()

Linq2Nhibernate seems doesn’t does support sub-query, while doing Session.Linq<Email>().Max(x => x.Id) generated max in sql query, but I got null ref exception when using generic in my base repository syntax like  Linq<T>().Max(x => x.Id),    I was using Linq2NHibernate version 1.1.0.1001.

System.NullReferenceException : Object reference not set to an instance of an object.
at NHibernate.Loader.Criteria.CriteriaQueryTranslator.GetEntityName(ICriteria subcriteriaString propertyName)
at NHibernate.Loader.Criteria.CriteriaQueryTranslator.GetType(ICriteria subcriteriaString propertyName)
at NHibernate.Criterion.AggregateProjection.GetTypes(ICriteria criteriaICriteriaQuery criteriaQuery)
at NHibernate.Loader.Criteria.CriteriaQueryTranslator.get_ProjectedTypes()
at NHibernate.Loader.Criteria.CriteriaJoinWalker..ctor(IOuterJoinLoadable persisterCriteriaQueryTranslator translatorISessionFactoryImplementor factoryICriteria criteriaString rootEntityNameIDictionary`2 enabledFilters)
at NHibernate.Loader.Criteria.CriteriaLoader..ctor(IOuterJoinLoadable persisterISessionFactoryImplementor factoryCriteriaImpl rootCriteriaString rootEntityNameIDictionary`2 enabledFilters)
at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteriaIList results)
at NHibernate.Impl.CriteriaImpl.List(IList results)
at NHibernate.Impl.CriteriaImpl.UniqueResult()
at NHibernate.Linq.Visitors.ImmediateResultsVisitor`1.HandleAggregateCall(MethodCallExpression call) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\ImmediateResultsVisitor.cs: line 153
at NHibernate.Linq.Visitors.ImmediateResultsVisitor`1.VisitMethodCall(MethodCallExpression call) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\ImmediateResultsVisitor.cs: line 65
at NHibernate.Linq.Visitors.ExpressionVisitor.Visit(Expression exp) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\ExpressionVisitor.cs: line 70
at NHibernate.Linq.Visitors.NHibernateExpressionVisitor.Visit(Expression exp) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\NHibernateExpressionVisitor.cs: line 29
at NHibernate.Linq.Visitors.ImmediateResultsVisitor`1.GetResults(MethodCallExpression expr) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\ImmediateResultsVisitor.cs: line 35
at NHibernate.Linq.Visitors.RootVisitor.HandleImmediateResultsCall(MethodCallExpression call) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\RootVisitor.cs: line 202
at NHibernate.Linq.Visitors.RootVisitor.VisitMethodCall(MethodCallExpression expr) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\RootVisitor.cs: line 92
at NHibernate.Linq.Visitors.ExpressionVisitor.Visit(Expression exp) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\ExpressionVisitor.cs: line 70
at NHibernate.Linq.Visitors.NHibernateExpressionVisitor.Visit(Expression exp) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\NHibernateExpressionVisitor.cs: line 29
at NHibernate.Linq.Visitors.NHibernateQueryTranslator.TranslateInternal(Expression expression) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\NHibernateQueryTranslator.cs: line 48
at NHibernate.Linq.Visitors.NHibernateQueryTranslator.Translate(Expression expressionQueryOptions queryOptions) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\Visitors\NHibernateQueryTranslator.cs: line 32
at NHibernate.Linq.NHibernateQueryProvider.TranslateExpression(Expression expression) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\NHibernateQueryProvider.cs: line 56
at NHibernate.Linq.NHibernateQueryProvider.Execute(Expression expression) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\NHibernateQueryProvider.cs: line 63
at NHibernate.Linq.QueryProvider.System.Linq.IQueryProvider.Execute(Expression expression) in d:\OSS\nhcontrib\src\NHibernate.Linq\src\NHibernate.Linq\QueryProvider.cs: line 29
at System.Linq.Queryable.Max(IQueryable`1 sourceExpression`1 selector)

It might be a bug in linq2NH or in my base Repository, Linq2NH is deprecated anyway.

In NHibernate 3.0, we can take advantage of the new built-in Linq provider in NH: QueryOver syntax.

 session.QueryOver<Customer>()
.SelectList(list => list.SelectMax(c => c.Id))
.List<int>().FirstOrDefault();
 

Update: I thought using Session.Query<T>.Max(x => x.Id) will only do the in-memory collection max operation, in fact it does generate the max query over database. Thanks to Phillip Haydon for pointing out.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s