Exception when calling First on a GroupBy Query

Description

When First is called on a GroupBy query invalid SQL code seems to be generated:

Console.WriteLine(session.Query<Employee>().GroupBy(x => x.Name).First());

This causes the following Exception:

NHibernate.Exceptions.GenericADOException was unhandled
  HResult=-2146232832
  Message=could not execute query
[ select TOP (1) employee0_.ID as ID0_, employee0_.Name as Name0_ from Employee employee0_ group by employee0_.Name ]
[SQL: select TOP (1) employee0_.ID as ID0_, employee0_.Name as Name0_ from Employee employee0_ group by employee0_.Name]
  Source=NHibernate
  SqlString=select TOP (1) employee0_.ID as ID0_, employee0_.Name as Name0_ from Employee employee0_ group by employee0_.Name
  StackTrace:
       bei NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer) in c:\Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:Zeile 1668.
       bei NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in c:\Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:Zeile 1644.
       bei NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in c:\Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:Zeile 1536.
       bei NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in c:\Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:Zeile 1531.
       bei NHibernate.Loader.Hql.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters) in c:\Projects\nhibernate-core\src\NHibernate\Loader\Hql\QueryLoader.cs:Zeile 289.
       bei NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters) in c:\Projects\nhibernate-core\src\NHibernate\Hql\Ast\ANTLR\QueryTranslatorImpl.cs:Zeile 110.
       bei NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results) in c:\Projects\nhibernate-core\src\NHibernate\Engine\Query\HQLQueryPlan.cs:Zeile 115.
       bei NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results) in c:\Projects\nhibernate-core\src\NHibernate\Impl\SessionImpl.cs:Zeile 634.
       bei NHibernate.Impl.AbstractSessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters) in c:\Projects\nhibernate-core\src\NHibernate\Impl\AbstractSessionImpl.cs:Zeile 124.
       bei NHibernate.Impl.AbstractQueryImpl2.List() in c:\Projects\nhibernate-core\src\NHibernate\Impl\AbstractQueryImpl2.cs:Zeile 81.
       bei NHibernate.Linq.DefaultQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery) in c:\Projects\nhibernate-core\src\NHibernate\Linq\DefaultQueryProvider.cs:Zeile 106.
       bei NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\DefaultQueryProvider.cs:Zeile 41.
       bei NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\DefaultQueryProvider.cs:Zeile 46.
       bei System.Linq.Queryable.First[TSource](IQueryable`1 source)
       bei NHibernateBasics.Program.Main() in C:\Users\dorud\Downloads\NHibernateBasics\NHibernateBasics\NHibernateBasics\Program.cs:Zeile 21.
       bei System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       bei System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       bei Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       bei System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       bei System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       bei System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       bei System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       bei System.Threading.ThreadHelper.ThreadStart()
  InnerException: System.Data.SqlClient.SqlException
       HResult=-2146232060
       Message=Column 'Employee.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
       Source=.Net SqlClient Data Provider
       ErrorCode=-2146232060
       Class=16
       LineNumber=1
       Number=8120
       Procedure=""
       Server=(Local)\ENTW11
       State=1
       StackTrace:
            bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
            bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
            bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
            bei System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
            bei System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
            bei System.Data.SqlClient.SqlDataReader.get_MetaData()
            bei System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
            bei System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
            bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
            bei System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
            bei System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
            bei System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
            bei System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
            bei NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) in c:\Projects\nhibernate-core\src\NHibernate\AdoNet\AbstractBatcher.cs:Zeile 228.
            bei NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) in c:\Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:Zeile 1288.
            bei NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer) in c:\Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:Zeile 436.
            bei NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer) in c:\Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:Zeile 252.
            bei NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer) in c:\Projects\nhibernate-core\src\NHibernate\Loader\Loader.cs:Zeile 1659.
       InnerException:

The same error is also causes with FirstOrDefault, SingleOrDefault, Single.

A different exception is thrown when an additional Select statement is added:

// Some comments here
Console.WriteLine(session.Query<Employee>().GroupBy(x => x.Name).Select(x => x).First());

System.NotImplementedException was unhandled
  HResult=-2147467263
  Message=Die Methode oder der Vorgang ist nicht implementiert.
  Source=NHibernate
  StackTrace:
       bei NHibernate.Linq.GroupBy.NonAggregatingGroupByRewriter.FlattenSubQuery(SubQueryExpression subQueryExpression, QueryModel queryModel) in c:\Projects\nhibernate-core\src\NHibernate\Linq\GroupBy\NonAggregatingGroupByRewriter.cs:Zeile 58.
       bei NHibernate.Linq.GroupBy.NonAggregatingGroupByRewriter.ReWrite(QueryModel queryModel) in c:\Projects\nhibernate-core\src\NHibernate\Linq\GroupBy\NonAggregatingGroupByRewriter.cs:Zeile 33.
       bei NHibernate.Linq.Visitors.QueryModelVisitor.GenerateHqlQuery(QueryModel queryModel, VisitorParameters parameters, Boolean root) in c:\Projects\nhibernate-core\src\NHibernate\Linq\Visitors\QueryModelVisitor.cs:Zeile 32.
       bei NHibernate.Linq.NhLinqExpression.Translate(ISessionFactoryImplementor sessionFactory, Boolean filter) in c:\Projects\nhibernate-core\src\NHibernate\Linq\NhLinqExpression.cs:Zeile 67.
       bei NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory) in c:\Projects\nhibernate-core\src\NHibernate\Hql\Ast\ANTLR\ASTQueryTranslatorFactory.cs:Zeile 24.
       bei NHibernate.Engine.Query.QueryExpressionPlan.CreateTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory) in c:\Projects\nhibernate-core\src\NHibernate\Engine\Query\QueryExpressionPlan.cs:Zeile 32.
       bei NHibernate.Engine.Query.QueryExpressionPlan..ctor(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory) in c:\Projects\nhibernate-core\src\NHibernate\Engine\Query\QueryExpressionPlan.cs:Zeile 13.
       bei NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters) in c:\Projects\nhibernate-core\src\NHibernate\Engine\Query\QueryPlanCache.cs:Zeile 67.
       bei NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow) in c:\Projects\nhibernate-core\src\NHibernate\Impl\AbstractSessionImpl.cs:Zeile 412.
       bei NHibernate.Impl.AbstractSessionImpl.CreateQuery(IQueryExpression queryExpression) in c:\Projects\nhibernate-core\src\NHibernate\Impl\AbstractSessionImpl.cs:Zeile 372.
       bei NHibernate.Linq.DefaultQueryProvider.PrepareQuery(Expression expression, IQuery& query, NhLinqExpression& nhQuery) in c:\Projects\nhibernate-core\src\NHibernate\Linq\DefaultQueryProvider.cs:Zeile 73.
       bei NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\DefaultQueryProvider.cs:Zeile 39.
       bei NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\DefaultQueryProvider.cs:Zeile 46.
       bei System.Linq.Queryable.First[TSource](IQueryable`1 source)
       bei NHibernateBasics.Program.Main() in C:\Users\dorud\Downloads\NHibernateBasics\NHibernateBasics\NHibernateBasics\Program.cs:Zeile 21.
       bei System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       bei System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       bei Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       bei System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       bei System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       bei System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       bei System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       bei System.Threading.ThreadHelper.ThreadStart()
  InnerException:

However, calling ToList does not cause an exception:

Console.WriteLine(session.Query<Employee>().GroupBy(x => x.Name).Select(x => x).ToList().First());

This would generate the following SQL code:

select employee0_.ID as ID0_, employee0_.Name as Name0_ from Employee employee0_

Using Take causes the same error as First:

Console.WriteLine(session.Query<Employee>().GroupBy(x => x.Name).Select(x => x).Take(1).ToList().First());

Environment

Windows 8.1 SQL Server 2012 Visual Studio 2010

Attachments

NHibernateBasics.zip

10 years ago

Activity

Show:

Laurent NL

March 24, 2016 at 9:05 AM

related to https://nhibernate.jira.com/browse/NH-3027?

Dorian Rudolph

March 16, 2016 at 9:03 AM

Calling OrderBy on a GroupBy query yields yet another exception:

var t = session.Query<Employee>().GroupBy(x => x.Name).OrderBy(x => x.Key).ToList();

NHibernate.HibernateException was unhandled
  HResult=-2146232832
  Message=Query Source could not be identified: ItemName = x, ItemType = System.Linq.IGrouping`2[System.String,NHibernateBasics.Employee], Expression = from IGrouping`2 x in {value(NHibernate.Linq.NhQueryable`1[NHibernateBasics.Employee]) => GroupBy([x].Name, [x])}
  Source=NHibernate
  StackTrace:
       bei NHibernate.Linq.QuerySourceNamer.GetName(IQuerySource querySource) in c:\Projects\nhibernate-core\src\NHibernate\Linq\QuerySourceNamer.cs:Zeile 30.
       bei NHibernate.Linq.Visitors.HqlGeneratorExpressionTreeVisitor.VisitQuerySourceReferenceExpression(QuerySourceReferenceExpression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\Visitors\HqlGeneratorExpressionTreeVisitor.cs:Zeile 200.
       bei NHibernate.Linq.Visitors.HqlGeneratorExpressionTreeVisitor.VisitExpression(Expression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\Visitors\HqlGeneratorExpressionTreeVisitor.cs:Zeile 116.
       bei NHibernate.Linq.Visitors.HqlGeneratorExpressionTreeVisitor.VisitMemberExpression(MemberExpression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\Visitors\HqlGeneratorExpressionTreeVisitor.cs:Zeile 381.
       bei NHibernate.Linq.Visitors.HqlGeneratorExpressionTreeVisitor.VisitExpression(Expression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\Visitors\HqlGeneratorExpressionTreeVisitor.cs:Zeile 92.
       bei NHibernate.Linq.Visitors.HqlGeneratorExpressionTreeVisitor.Visit(Expression expression, VisitorParameters parameters) in c:\Projects\nhibernate-core\src\NHibernate\Linq\Visitors\HqlGeneratorExpressionTreeVisitor.cs:Zeile 23.
       bei NHibernate.Linq.Visitors.QueryModelVisitor.VisitOrderByClause(OrderByClause orderByClause, QueryModel queryModel, Int32 index) in c:\Projects\nhibernate-core\src\NHibernate\Linq\Visitors\QueryModelVisitor.cs:Zeile 258.
       bei Remotion.Linq.Clauses.OrderByClause.Accept(IQueryModelVisitor visitor, QueryModel queryModel, Int32 index)
       bei Remotion.Linq.QueryModelVisitorBase.VisitBodyClauses(ObservableCollection`1 bodyClauses, QueryModel queryModel)
       bei Remotion.Linq.QueryModelVisitorBase.VisitQueryModel(QueryModel queryModel)
       bei NHibernate.Linq.Visitors.QueryModelVisitor.Visit() in c:\Projects\nhibernate-core\src\NHibernate\Linq\Visitors\QueryModelVisitor.cs:Zeile 122.
       bei NHibernate.Linq.Visitors.QueryModelVisitor.GenerateHqlQuery(QueryModel queryModel, VisitorParameters parameters, Boolean root) in c:\Projects\nhibernate-core\src\NHibernate\Linq\Visitors\QueryModelVisitor.cs:Zeile 68.
       bei NHibernate.Linq.NhLinqExpression.Translate(ISessionFactoryImplementor sessionFactory, Boolean filter) in c:\Projects\nhibernate-core\src\NHibernate\Linq\NhLinqExpression.cs:Zeile 67.
       bei NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory.CreateQueryTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 filters, ISessionFactoryImplementor factory) in c:\Projects\nhibernate-core\src\NHibernate\Hql\Ast\ANTLR\ASTQueryTranslatorFactory.cs:Zeile 24.
       bei NHibernate.Engine.Query.QueryExpressionPlan.CreateTranslators(IQueryExpression queryExpression, String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory) in c:\Projects\nhibernate-core\src\NHibernate\Engine\Query\QueryExpressionPlan.cs:Zeile 32.
       bei NHibernate.Engine.Query.QueryExpressionPlan..ctor(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory) in c:\Projects\nhibernate-core\src\NHibernate\Engine\Query\QueryExpressionPlan.cs:Zeile 13.
       bei NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow, IDictionary`2 enabledFilters) in c:\Projects\nhibernate-core\src\NHibernate\Engine\Query\QueryPlanCache.cs:Zeile 67.
       bei NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(IQueryExpression queryExpression, Boolean shallow) in c:\Projects\nhibernate-core\src\NHibernate\Impl\AbstractSessionImpl.cs:Zeile 412.
       bei NHibernate.Impl.AbstractSessionImpl.CreateQuery(IQueryExpression queryExpression) in c:\Projects\nhibernate-core\src\NHibernate\Impl\AbstractSessionImpl.cs:Zeile 372.
       bei NHibernate.Linq.DefaultQueryProvider.PrepareQuery(Expression expression, IQuery& query, NhLinqExpression& nhQuery) in c:\Projects\nhibernate-core\src\NHibernate\Linq\DefaultQueryProvider.cs:Zeile 73.
       bei NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\DefaultQueryProvider.cs:Zeile 39.
       bei NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression) in c:\Projects\nhibernate-core\src\NHibernate\Linq\DefaultQueryProvider.cs:Zeile 46.
       bei Remotion.Linq.QueryableBase`1.GetEnumerator()
       bei System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
       bei System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
       bei NHibernateBasics.Program.Main() in C:\Users\dorud\Downloads\NHibernateBasics\NHibernateBasics\NHibernateBasics\Program.cs:Zeile 21.
       bei System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       bei System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       bei Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       bei System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       bei System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       bei System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       bei System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       bei System.Threading.ThreadHelper.ThreadStart()
  InnerException:

Resize issue view side panel

Details

Assignee

Unassigned

Reporter

Dorian Rudolph

Labels

Components

Linq Provider

Affects versions

4.0.4

Priority

Major

Who's Looking?

Open Who's Looking?

Created March 15, 2016 at 2:26 PM

Updated March 24, 2016 at 9:05 AM

Who's Looking?