Hi

We have recently installed/upgraded to the latest version of LLBLGen and are experiencing problems with code that attempts to prefetch an entity collection via another entity. This is code that worked with the previous version of LLBLGen. Our config is as follows;

LLBLGen Pro version + buildnr: 2.5 Final ( 04Feb2008 ) Runtime library version: 2.5.7.1129 Template group: Adapter .NET version: .NET 2.0 Database type and version: SQL Server 2005

When the following method is called;

/// <summary>
        /// Returns an Delinquency Parameter for a specific ID.
        /// </summary>
        /// <param name="DelQParamId">ID of the Delinquency Parameter to return.</param>
        /// <returns>A single Deliquency Parameter entity.</returns>
        public DelQparamEntity GetDelQParamByDelQParamId(int DelQParamId, bool Prefetch)
        {
            try
            {
                IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.DelQparamEntity);

                prefetchPath.Add(DelQparamEntity.PrefetchPathCompany);
                prefetchPath.Add(DelQparamEntity.PrefetchPathDelQaction);
                prefetchPath.Add(DelQparamEntity.PrefetchPathDelQageGrp);
                prefetchPath.Add(DelQparamEntity.PrefetchPathDelQparamCharge);
                prefetchPath.Add(DelQparamEntity.PrefetchPathDelQparamMinimum);
                prefetchPath.Add(DelQparamEntity.PrefetchPathDelQstatus);
                prefetchPath.Add(DelQparamEntity.PrefetchPathDelQtype);
                prefetchPath.Add(DelQparamEntity.PrefetchPathNoteCat);
                prefetchPath.Add(DelQparamEntity.PrefetchPathWorkOrderCodeCollectionViaDelQparamCharge);
                prefetchPath.Add(DelQparamEntity.PrefetchPathRateCollectionViaDelQparamSelect);
                prefetchPath.Add(DelQparamEntity.PrefetchPathRouteCollectionViaDelQparamMinimum);
                prefetchPath.Add(DelQparamEntity.PrefetchPathServiceCollectionViaDelQparamCharge);
                prefetchPath.Add(DelQparamEntity.PrefetchPathServiceCollectionViaDelQparamSelect);
                prefetchPath.Add(DelQparamEntity.PrefetchPathSlip);
                prefetchPath.Add(DelQparamEntity.PrefetchPathWorkOrderAssignment);
                prefetchPath.Add(DelQparamEntity.PrefetchPathWorkOrderType);
                prefetchPath.Add(DelQparamEntity.PrefetchPathDelQparamSelect);


                //Get a DelQParam by ID
                DelQparamEntity delQParamEntity = new DelQparamEntity(DelQParamId);

                using (SD.LLBLGen.Pro.ORMSupportClasses.IDataAccessAdapter adapter = DataAccessAdapter.GetAdapter())
                {
                    if (Prefetch)
                    {
                        adapter.FetchEntity(delQParamEntity, prefetchPath);
                    }
                    else
                    {
                        //Fetch the entity from the database, prefetch no other entities.
                        adapter.FetchEntity(delQParamEntity);
                    }
                }

                return delQParamEntity;
            }
            catch (SqlException sqle)
            {
                throw new FacadeException(Utilities.GetExceptionMessage(sqle), sqle);
            }
            catch (Exception ex)
            {
                throw new FacadeException(ex.Message, ex);
            }
        }

the application throws the following exception;

{"The multi-part identifier \"Promis-Fujitsu.dbo.tblDelQParam.DelQParamID\" could not be bound."} " at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)\r\n
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)\r\n
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)\r\n
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)\r\n
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()\r\n
at System.Data.SqlClient.SqlDataReader.get_MetaData()\r\n
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)\r\n
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)\r\n
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)\r\n
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)\r\n
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)\r\n
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)\r\n
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)\r\n
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)\r\n
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IFieldPersistenceInfo fieldsPersistenceInfo)\r\n
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)\r\n
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, Boolean allowDuplicates)\r\n
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.MergeManyToMany(IPrefetchPathElement2 currentElement, IRelationPredicateBucket elementFilter, Int64 maxNumberOfItemsToReturn, IEntityCollection2 rootEntities)\r\n
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath)\r\n
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath)\r\n
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityUsingFilter(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, IRelationPredicateBucket filter, ExcludeIncludeFieldsList excludedIncludedFields)\r\n
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, ExcludeIncludeFieldsList excludedIncludedFields)\r\n
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath)\r\n
at Promis.Server.Facade.CodeLibraryFacade.GetDelQParamByDelQParamId(Int32 DelQParamId, Boolean Prefetch) in C:\\Promis.net\\Server\\PromisServer\\Promis.Server.Facade\\CodeLibraryFacade.cs:line 2794"

If we examine the SQL output, the application appears to get as far as the first call to a prefetch that uses "PrefetchSomeCollectionViaSomeEntity" i.e. prefetchPath.Add(DelQparamEntity.PrefetchPathWorkOrderCodeCollectionViaDelQparamCharge);

The SQL itself appears to be incorrect;

SELECT DISTINCT tblWorkOrderCode.WorkOrderCodeID AS WorkOrderCodeId, tblWorkOrderCode.CompanyID AS CompanyId, tblWorkOrderCode.WorkOrderCode, 
tblWorkOrderCode.WorkOrderCost, tblWorkOrderCode.GLCodeID AS GlcodeId, 
tblWorkOrderCode.WorkOrder, tblWorkOrderCode.MeterDispositionID AS MeterDispositionId, tblWorkOrderCode.TaxID AS TaxId, 
tblWorkOrderCode.IsActive, tblWorkOrderCode.EffectiveDate, tblWorkOrderCode.ExpiryDate, tblWorkOrderCode.DateAltered, 
tblWorkOrderCode.UserAltered, tblWorkOrderCode.Version_No AS VersionNo 
FROM (( tblDelQParam LPA_D1  
INNER JOIN tblDelQParamCharge LPA_D2  ON  LPA_D1.DelQParamID=LPA_D2.DelQParamID) 
INNER JOIN tblWorkOrderCode  ON  tblWorkOrderCode.WorkOrderCodeID=LPA_D2.WorkOrderCodeID) WHERE ( ( ( LPA_D2.DelQParamID = @DelQparamId1)))

SELECT DISTINCT tblDelQParam.DelQParamID AS DelQparamId0, tblWorkOrderCode.WorkOrderCodeID AS WorkOrderCodeId1 
FROM (( tblDelQParam LPA_D1  
INNER JOIN tblDelQParamCharge LPA_D2  ON  LPA_D1.DelQParamID=LPA_D2.DelQParamID) 
INNER JOIN tblWorkOrderCode  ON  tblWorkOrderCode.WorkOrderCodeID=LPA_D2.WorkOrderCodeID) WHERE ( ( ( ( LPA_D2.DelQParamID = @DelQparamId1))))

You'll notice in the second query that it attempts to reference a table called tblDelQParam to get DelQParamID but the table has been aliased in the FROM clause with a different name.

It would be great if we could find a resolution to this since there are naturally multiple methods that will be affected in a similar way. Your assistance would be greatly appreciated.

Thanks

LLBLGen Pro version + buildnr: 2.5 Final ( 04Feb2008 ) Runtime library version: 2.5.7.1129

RTL version is older than the designer version.

There were some bug fixes that might be related to this issue, would you please make sure you are using the latest runtime library version available, with the latest Templates.

You may download and install The latest full LLBLGen Pro installer, which should contain the latest of everything. And then you should make sure your generated code and application code both reference the latest RTLs.

Thanks.

Hi Walaa

My apologies! Thank you for pointing that out - I should have noticed that when I first copied the version numbers into my message

Although I had replaced the RTL's with the latest version, the build process was copying the old ones back. Replacing the RTL's in the build source has sorted that out now.

Cheers