Hi,

I am running against the version 2.6.8.1001 of the linq support classes. The code all runs against NorthWind database.

The query that breaks is

                var results = (from o in mdata.Employees
                                    group o by 1 into g
                                    select new {City = g.Key, CityCount = g.Sum(a=> a.EmployeeId > 2 ? a.EmployeeId : 0) });

The exception:

POC.LinqToLLBL.HelloLinq.MyFirstTest: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException : An exception was caught during the execution of a retrieval query: Invalid column name 'LPAV'. The multi-part identifier "LPLA_1.EmployeeID" could not be bound. The multi-part identifier "LPLA_1.EmployeeID" could not be bound. The multi-part identifier "LPLA_5.LPAV" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ----> System.Data.SqlClient.SqlException : Invalid column name 'LPAV'. The multi-part identifier "LPLA_1.EmployeeID" could not be bound. The multi-part identifier "LPLA_1.EmployeeID" could not be bound. The multi-part identifier "LPLA_5.LPAV" could not be bound. at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) at NW26.DatabaseSpecific.DataAccessAdapter.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in C:\dev\Mainline\POC\DataAccess\NW\DatabaseSpecific\DataAccessAdapter.cs:line 292 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary2 typeConvertersToRun) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() at POC.LinqToLLBL.HelloLinq.MyFirstTest() in C:\dev\Mainline\POC\LINQToLLBL\HelloLinq.cs:line 29 --SqlException at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)

Here is the trace:

Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSubQuery Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSubQuery Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSubQuery Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT @LO14 AS [LPFA_2], [LPLA_1].[LPAV_] FROM [Northwind].[dbo].[Employees] [LPLA_1] Parameter: @LO14 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

Method Exit: CreateSelectDQ Method Exit: CreateSubQuery Generated Sql query: Query: SELECT DISTINCT [LPA_L4].[LPFA_2], CASE WHEN CASE WHEN ( [LPLA_1].[EmployeeID] > @EmployeeId2) THEN 1 ELSE 0 END=1 THEN [LPLA_1].[EmployeeID] ELSE @LO03 END AS [LPAV] FROM (SELECT @LO14 AS [LPFA_2], [LPLA_1].[LPAV] FROM [Northwind].[dbo].[Employees] [LPLA_1] ) [LPA_L4] Parameter: @EmployeeId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2. Parameter: @LO03 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0. Parameter: @LO14 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

Method Exit: CreateSelectDQ Method Exit: CreateSubQuery Generated Sql query: Query: SELECT DISTINCT [LPA_L3].[LPFA_2], SUM([LPLA_5].[LPAV]) AS [LPAV] FROM (SELECT DISTINCT [LPA_L4].[LPFA_2], CASE WHEN CASE WHEN ( [LPLA_1].[EmployeeID] > @EmployeeId2) THEN 1 ELSE 0 END=1 THEN [LPLA_1].[EmployeeID] ELSE @LO03 END AS [LPAV] FROM (SELECT @LO14 AS [LPFA_2], [LPLA_1].[LPAV] FROM [Northwind].[dbo].[Employees] [LPLA_1] ) [LPA_L4]) [LPA_L3] GROUP BY [LPA_L3].[LPFA_2] Parameter: @EmployeeId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2. Parameter: @LO03 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0. Parameter: @LO14 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

Method Exit: CreateSelectDQ Method Exit: CreateSubQuery Generated Sql query: Query: SELECT @LO11 AS [LPFA_7], [LPA_L1].[LPAV] AS [CityCount] FROM (SELECT DISTINCT [LPA_L3].[LPFA_2], SUM([LPLA_5].[LPAV]) AS [LPAV_] FROM (SELECT DISTINCT [LPA_L4].[LPFA_2], CASE WHEN CASE WHEN ( [LPLA_1].[EmployeeID] > @EmployeeId2) THEN 1 ELSE 0 END=1 THEN [LPLA_1].[EmployeeID] ELSE @LO03 END AS [LPAV] FROM (SELECT @LO14 AS [LPFA_2], [LPLA_1].[LPAV] FROM [Northwind].[dbo].[Employees] [LPLA_1] ) [LPA_L4]) [LPA_L3] GROUP BY [LPA_L3].[LPFA_2]) [LPA_L1] Parameter: @LO11 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: @EmployeeId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2. Parameter: @LO03 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0. Parameter: @LO14 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ: no paging.

If the query groups by a column in the table, it works.

                var results = (from o in mdata.Employees
                                    group o by o.City into g
                                    select new {City = g.Key, CityCount = g.Sum(a=> a.EmployeeId > 2 ? a.EmployeeId : 0) });

Thanks for your help.

Strange, as we have tests with grouping on constant, as it's required by for example DevExpress' LinqDataSource control .

We'll check it out. In the meantime, please use the latest runtime lib build to see if it helps.

It's the 'a=> a.EmployeeId > 2 ? a.EmployeeId : 0' which causes the problems. using 'a=> a.EmployeeId' instead also works.

The conditional operator causes the introduction of a case statement but apparently it goes wrong somewhere combined with the constant group by...

Looking into it.

Please use the attached linq support classes build to fix the issue.

Thank you Otis! I will try this one out.