Hi all,

we recently upgraded from LLBLGen Pro 4.0 to LLBLGen Pro 5.0 and are experiencing an issue with a Linq-to-LLBLGen query that works in 4.0 and doesn't work in 5.0. FYI: this bug occurs in VS 2013 as well as VS 2015.

Here is the query:

Aggregate TLD In linq.TLD _
  Join TLA In linq.TLA On TLA.TLA_Cod Equals TLD.TLA_Cod
 Where TLD.TCrd_Cod = "014" _
  AndAlso TLD.TLK_Peri = "201607" _
  AndAlso TLD.TLK_Typ = "L" _
  AndAlso {"4", "5"}.Contains(TLA.TTypLA_Cod) _
  AndAlso TLA.TLA_Foo
 Into Sum(TLD.TLD_Amount * If(TLA.TTypLA_Cod = "4", -1, 1))

This results in the following SQL Query (I removed unnecessary columns and replaced parameters by their values). The query fails with the following error (for obvious reasons):

The multi-part identifier "LPLA_1.TLD_Amount" could not be bound. The multi-part identifier "LPLA_2.TTypLA_Cod" could not be bound

SELECT TOP(1) SUM([LPA_L1].[LPAV_]) AS [LPAV_] 
FROM (SELECT ([LPLA_1].[TLD_Amount] 
               * CONVERT(DECIMAL, CASE WHEN CASE WHEN ( [LPLA_2].[TTypLA_Cod] = '4') 
                                                 THEN 1 
                                                 ELSE 0 
                                                 END
                                         = 1 
                                  THEN 1
                                  ELSE +1 
                                  END)
             ) AS [LPAV_] 
        FROM (SELECT [LPA_L3].[TLD_ID], 
                     [LPA_L3].[TCrd_Cod], 
                     ..., 
                     [LPA_L3].[TLD_Amount], 
                     ...,
                     1 AS [LPFA_3],  
                     [LPA_L4].[TLA_Cod] AS [TLA_Cod0], 
                     ..., 
                     [LPA_L4].[TTypLA_Cod], 
                     ..., 
                     1 AS [LPFA_4] 
               FROM ( [MyDb].[dbo].[TLD] [LPA_L3]  
                INNER JOIN [MyDb].[dbo].[TLA] [LPA_L4]  ON  [LPA_L3].[TLA_Cod] = [LPA_L4].[TLA_Cod]) 
               WHERE ( ( ( ( ( ( [LPA_L3].[TCrd_Cod] = '123') 
                                AND ( [LPA_L3].[TLK_Peri] = '201607')) 
                              AND ( [LPA_L3].[TLK_Typ] = 'L')) 
                            AND ( [LPA_L4].[TTypLA_Cod] IN ('3', '4'))) 
                          AND ( [LPA_L4].[TLA_Foo] = '1')))
               ) [LPA_L2]
    ) [LPA_L1]

There are several queries like this that fail. This is of course problematic, since we were not counting on going back to LLBLGen 4.0!

Here is the exception detail:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException Message: An exception was caught during the execution of a retrieval query: The multi-part identifier "LPLA_1.TLD_Amount" could not be bound. The multi-part identifier "LPLA_2.TTypLA_Cod" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. Source: SD.LLBLGen.Pro.ORMSupportClasses TargetSite: System.Data.Common.DbDataReader Execute(System.Data.CommandBehavior) LLBLGen RuntimeBuild: 5.0.5 LLBLGen RuntimeVersion: 5.0 QueryExecuted: Query: ... Parameter: @p2 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: @p3 : AnsiStringFixedLength. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: "4". Parameter: @p5 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: -1. Parameter: @p7 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: @p9 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: @p11 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: @p12 : AnsiString. Length: 9. Precision: 0. Scale: 0. Direction: Input. Value: "014". Parameter: @p13 : AnsiStringFixedLength. Length: 6. Precision: 0. Scale: 0. Direction: Input. Value: "201607". Parameter: @p14 : AnsiStringFixedLength. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: "L". Parameter: @p15 : AnsiStringFixedLength. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: "4". Parameter: @p16 : AnsiStringFixedLength. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: "5". Parameter: @p17 : AnsiStringFixedLength. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: "1".

LLBLGen RuntimeVersion: 5.0 Parameters List: @p2 @p3 @p5 @p7 @p9 @p11 @p12 @p13 @p14 @p15 @p16 @p17 DbSpecificExceptionInfo Dictionary: ErrorCode: -2146232060 Message: The multi-part identifier "LPLA_1.TLD_Amount" could not be bound. The multi-part identifier "LPLA_2.TTypLA_Cod" could not be bound. HelpLink: Nothing ErrorNumber: 4104 ErrorObjects: System.Data.SqlClient.SqlErrorCollection InvolvedEntity: Nothing StackTrace: at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 116 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 1238 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Boolean performValueProjectionsOnRawRow, Boolean postProcessDBNullValues, Dictionary2 typeConvertersToRun) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 1432 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 1349 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.<>c__DisplayClass18_0.<FetchProjection>b__0() in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 389 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteWithActiveRecoveryStrategy(Action toExecute) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 522 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 389 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\LLBLGenProProvider2.cs:line 127 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression, Type typeForPostProcessing) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\LLBLGenProProviderBase.cs:line 302 at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute[TResult](Expression expression) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Linq\LLBLGenProProviderBase.cs:line 774 at System.Linq.Queryable.Sum[TSource](IQueryable1 source, Expression1 selector) at .... Inner exception: System.Data.SqlClient.SqlException Message: The multi-part identifier "LPLA_1.TLD_Amount" could not be bound. The multi-part identifier "LPLA_2.TTypLA_Cod" could not be bound. Source: .Net SqlClient Data Provider TargetSite: Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action1[System.Action]) ErrorCode: -2146232060 Class: 16 ErrorCode: -2146232060 LineNumber: 1 Number: 4104 Server: X State: 1 Errors: System.Data.SqlClient.SqlError: The multi-part identifier "LPLA_1.TLD_Amount" could not be bound. System.Data.SqlClient.SqlError: The multi-part identifier "LPLA_2.TTypLA_Cod" could not be bound. Data: HelpLink.ProdName = Microsoft SQL Server HelpLink.ProdVer = 09.00.5057 HelpLink.EvtSrc = MSSQLServer HelpLink.EvtID = 4104 HelpLink.BaseHelpUrl = [http://go.microsoft.com/fwlink](http://go.microsoft.com/fwlink) HelpLink.LinkId = 20476 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() 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, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite) 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.ExecuteReader(CommandBehavior behavior) at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 102

Weird that the query worked in v4.0 as 'Aggregate' is a keyword we don't support in VB.NET linq queries: http://www.llblgen.com/Documentation/5.0/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Linq/gencode_linq_generalusage.htm#dont-use-the-aggregate-keyword

You can convert it however, see the above link. I hope that fixes it. If not, please provide a simple example on northwind / adventureworks and enable the trace switch Linqexpressionhandler: http://www.llblgen.com/Documentation/5.0/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_troubleshootingdebugging.htm#conventions

<add name="LinqExpressionHandler" value="3" />

to grab the expression tree in text, as that's key: the vb.net compiler in the past created weird / unexpected trees or worse: changed its course between versions (Which you ran into with your other issue which we'll hope to fix this week)

Ok thanks! We'll convert all the aggregate queries.