Issue when PrefetchPath assembly after converting from LLBLGen Pro v2.5 to LLBLGen Pro v5.9

Posts   
 
    
Posts: 7
Joined: 31-Aug-2022
# Posted on: 12-Oct-2022 07:50:56   

Important details:

LLBLGen Prov5.9 File version is v5.9.2.0 Originally an LLBLGen v2.5 project that has been converted using the v2xToV5 Migration template.

Hi Guys,

I've made some good progress in the update from v2.5 to 5.9, however I've now encountered another issue that I can't seem to resolve. The following function was being used in our application with the v2.5 LLBLGenPro model code:

  public OperatorEntity FetchWithAddresses(int id)
        {
            IPrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.OperatorEntity);
            IPrefetchPathElement2 modulePath = prefetchPath.Add(OperatorEntity.PrefetchPathOperatorModule);
            modulePath.SubPath.Add(OperatorModuleEntity.PrefetchPathModule);

            IPrefetchPathElement2 pathElement1 = prefetchPath.Add(PartyEntity.PrefetchPathAddress);
            IPrefetchPathElement2 pathElement2 = prefetchPath.Add(PartyEntity.PrefetchPathAddress_);
            prefetchPath.Add(PartyEntity.PrefetchPathDriverLicence);
            prefetchPath.Add(PartyEntity.PrefetchPathEmail);
            prefetchPath.Add(PartyEntity.PrefetchPathPhone);

            IPrefetchPathElement2 contactPath = prefetchPath.Add(PartyEntity.PrefetchPathPartyRelationship_)
                .SubPath.Add(PartyRelationshipEntity.PrefetchPathParty);
            contactPath.SubPath.Add(PartyEntity.PrefetchPathAddress)
              .SubPath.Add(AddressEntity.PrefetchPathState);
            contactPath.SubPath.Add(PartyEntity.PrefetchPathAddress_)
                .SubPath.Add(AddressEntity.PrefetchPathState);
            contactPath.SubPath.Add(PartyEntity.PrefetchPathPhone);
            contactPath.SubPath.Add(PartyEntity.PrefetchPathEmail);
            contactPath.SubPath.Add(PartyEntity.PrefetchPathDriverLicence);
            contactPath.SubPath.Add(PartyEntity.PrefetchPathPartyRelationship_);

            var notesSort = new SortExpression();
            notesSort.Add(new SortClause(NotesFields.CreatedDate, null, SortOperator.Descending));
            prefetchPath.Add(PartyEntity.PrefetchPathNotes).Sorter = notesSort;

            pathElement1.SubPath.Add(AddressEntity.PrefetchPathState);
            pathElement2.SubPath.Add(AddressEntity.PrefetchPathState);


            OperatorEntity operatorEntity = Fetch(id, prefetchPath);
            if (operatorEntity != null)
            {
                operatorEntity.CorrectAddressReferences();
            }

            return operatorEntity;
        }

With the implementation of the updated v5.9 model code, this function now returns the following error when trying to execute the Fetch (This is occurring at runtime).

Unknown error occurred: SQL Error 1013 - The objects "RAVS.dbo.Party" and "RAVS.dbo.Party" in the FROM clause have the same exposed names. Use correlation names to distinguish them.  
   at Mrwa.Vls.Services.Framework.BaseEntityService`1.Fetch(Int32 aKey, IPrefetchPath2 aPrefetchPath, ExcludeIncludeFieldsList excludedFields) in E:\Users\c9646-adm\Source\Repos\RAVS\Services\ServicesFramework\BaseEntityService.cs:line 86
   at Mrwa.Vls.Services.Framework.BaseEntityService`1.Fetch(Int32 aKey, IPrefetchPath2 aPrefetchPath) in E:\Users\c9646-adm\Source\Repos\RAVS\Services\ServicesFramework\BaseEntityService.cs:line 67
   at Mrwa.Vls.Services.Framework.Entity.OperatorEntityService.FetchWithAddresses(Int32 id) in E:\Users\c9646-adm\Source\Repos\RAVS\Services\ServicesFramework\Entity\OperatorEntityService.cs:line 119
   at Mrwa.Vls.Win.PermitModule.Views.Operator.Fetcher.MaintainOperatorNormalFetcher.FetchOperatorWithAddresses(Int32 operatorPartyId) in E:\Users\c9646-adm\Source\Repos\RAVS\Vls Win\Source\PermitModule\PermitModule\Views\Operator\Fetcher\MaintainOperatorNormalFetcher.cs:line 46
   at Mrwa.Vls.Win.PermitModule.Views.Operator.MaintainOperatorPresenter.SetupOperator(Nullable`1 value) in E:\Users\c9646-adm\Source\Repos\RAVS\Vls Win\Source\PermitModule\PermitModule\Views\Operator\MaintainOperatorPresenter.cs:line 650
   at Mrwa.Vls.Win.PermitModule.Views.Operator.MaintainOperatorPresenter.InitData() in E:\Users\c9646-adm\Source\Repos\RAVS\Vls Win\Source\PermitModule\PermitModule\Views\Operator\MaintainOperatorPresenter.cs:line 552
   at Mrwa.Vls.Win.PermitModule.Views.Operator.MaintainOperatorPresenter.OnViewReady() in E:\Users\c9646-adm\Source\Repos\RAVS\Vls Win\Source\PermitModule\PermitModule\Views\Operator\MaintainOperatorPresenter.cs:line 150
   at Mrwa.Vls.Win.PermitModule.Views.Operator.MaintainOperatorView.OnLoad(EventArgs e) in E:\Users\c9646-adm\Source\Repos\RAVS\Vls Win\Source\PermitModule\PermitModule\Views\Operator\MaintainOperatorView.cs:line 56
   at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
   at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
   at System.Windows.Forms.Control.CreateControl()
   at System.Windows.Forms.Control.WmShowWindow(Message& m)
   at System.Windows.Forms.Control.WndProc(Message& m)
   at DevExpress.XtraEditors.XtraForm.WndProc(Message& msg)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)</StackTrace><ExceptionString>Mrwa.Vls.Services.Framework.Exceptions.BusinessModelException: Unknown error occurred: SQL Error 1013 - The objects "RAVS.dbo.Party" and "RAVS.dbo.Party" in the FROM clause have the same exposed names. Use correlation names to distinguish them. ---&amp;gt; SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The objects "RAVS.dbo.Party" and "RAVS.dbo.Party" in the FROM clause have the same exposed names. Use correlation names to distinguish them.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---&amp;gt; System.Data.SqlClient.SqlException: The objects "RAVS.dbo.Party" and "RAVS.dbo.Party" in the FROM clause have the same exposed names. Use correlation names to distinguish them.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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&amp;amp; 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, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task&amp;amp; task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task&amp;amp; task, Boolean&amp;amp; usedCache, Boolean asyncWrite, Boolean inRetry)
   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 SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.TagAndExecuteCommand[TReturn](Func`1 toExecute) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 352
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 110
   --- End of inner exception stack trace ---
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 119
   at SD.LLBLGen.Pro.ORMSupportClasses.EntityMaterializerBase.Materialize(Func`4 valueReadErrorHandler, String&amp;amp; failureErrorText) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\EntityMaterializerBase.cs:line 150
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 635
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollectionInternal(QueryParameters parameters) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 4844
   at SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPathFetcher.FetchPrefetchPath(QueryParameters rootNodeParameters, Boolean forceParameterizedPPath, ITransaction transactionToUse, Int32 parameterisedPrefetchPathThreshold, Action`1 fetchNodeFunc, Action`4 mergeManyToManyFunc) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\PrefetchPathFetcher.cs:line 169
   at SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore.FetchPrefetchPath(QueryParameters rootNodeParameters, Boolean forceParameterizedPPath, ITransaction transactionToUse, Int32 parameterisedPrefetchPathThreshold, Action`1 fetchNodeFunc, Action`4 mergeManyToManyFunc) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\PersistenceCore.cs:line 104
   at SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPathFetcher.FetchPrefetchPath(QueryParameters rootNodeParameters, Boolean forceParameterizedPPath, ITransaction transactionToUse, Int32 parameterisedPrefetchPathThreshold, Action`1 fetchNodeFunc, Action`4 mergeManyToManyFunc) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\PrefetchPathFetcher.cs:line 192
   at SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore.FetchPrefetchPath(QueryParameters rootNodeParameters, Boolean forceParameterizedPPath, ITransaction transactionToUse, Int32 parameterisedPrefetchPathThreshold, Action`1 fetchNodeFunc, Action`4 mergeManyToManyFunc) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\PersistenceCore.cs:line 104
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.&amp;lt;&amp;gt;c__DisplayClass13_0.&amp;lt;FetchPrefetchPath&amp;gt;b__0() in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 311
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchAdditionalPrefetchPath(IPrefetchPath2 prefetchPath, Context contextToUse, IEntity2 fetchedEntity, IRelationPredicateBucket filterToUse) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 5465
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityUsingFilter(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, IRelationPredicateBucket filter, ExcludeIncludeFieldsList excludedIncludedFields) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 5010
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, ExcludeIncludeFieldsList excludedIncludedFields) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 1406
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.&amp;lt;&amp;gt;c__DisplayClass9_0.&amp;lt;FetchEntity&amp;gt;b__0() in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 235
   at Mrwa.Vls.Services.Framework.BaseEntityService`1.Fetch(Int32 aKey, IPrefetchPath2 aPrefetchPath, ExcludeIncludeFieldsList excludedFields) in E:\Users\c9646-adm\Source\Repos\RAVS\Services\ServicesFramework\BaseEntityService.cs:line 78
   --- End of inner exception stack trace ---
   at Mrwa.Vls.Services.Framework.BaseEntityService`1.Fetch(Int32 aKey, IPrefetchPath2 aPrefetchPath, ExcludeIncludeFieldsList excludedFields) in E:\Users\c9646-adm\Source\Repos\RAVS\Services\ServicesFramework\BaseEntityService.cs:line 86
   at Mrwa.Vls.Services.Framework.BaseEntityService`1.Fetch(Int32 aKey, IPrefetchPath2 aPrefetchPath) in E:\Users\c9646-adm\Source\Repos\RAVS\Services\ServicesFramework\BaseEntityService.cs:line 67
   at Mrwa.Vls.Services.Framework.Entity.OperatorEntityService.FetchWithAddresses(Int32 id) in E:\Users\c9646-adm\Source\Repos\RAVS\Services\ServicesFramework\Entity\OperatorEntityService.cs:line 119
   at Mrwa.Vls.Win.PermitModule.Views.Operator.Fetcher.MaintainOperatorNormalFetcher.FetchOperatorWithAddresses(Int32 operatorPartyId) in E:\Users\c9646-adm\Source\Repos\RAVS\Vls Win\Source\PermitModule\PermitModule\Views\Operator\Fetcher\MaintainOperatorNormalFetcher.cs:line 46
   at Mrwa.Vls.Win.PermitModule.Views.Operator.MaintainOperatorPresenter.SetupOperator(Nullable`1 value) in E:\Users\c9646-adm\Source\Repos\RAVS\Vls Win\Source\PermitModule\PermitModule\Views\Operator\MaintainOperatorPresenter.cs:line 650
   at Mrwa.Vls.Win.PermitModule.Views.Operator.MaintainOperatorPresenter.InitData() in E:\Users\c9646-adm\Source\Repos\RAVS\Vls Win\Source\PermitModule\PermitModule\Views\Operator\MaintainOperatorPresenter.cs:line 552
   at Mrwa.Vls.Win.PermitModule.Views.Operator.MaintainOperatorPresenter.OnViewReady() in E:\Users\c9646-adm\Source\Repos\RAVS\Vls Win\Source\PermitModule\PermitModule\Views\Operator\MaintainOperatorPresenter.cs:line 150
   at Mrwa.Vls.Win.PermitModule.Views.Operator.MaintainOperatorView.OnLoad(EventArgs e) in E:\Users\c9646-adm\Source\Repos\RAVS\Vls Win\Source\PermitModule\PermitModule\Views\Operator\MaintainOperatorView.cs:line 56
   at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
   at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
   at System.Windows.Forms.Control.CreateControl()
   at System.Windows.Forms.Control.WmShowWindow(Message&amp;amp; m)
   at System.Windows.Forms.Control.WndProc(Message&amp;amp; m)
   at DevExpress.XtraEditors.XtraForm.WndProc(Message&amp;amp; msg)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)</ExceptionString><InnerException><ExceptionType>SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException, SD.LLBLGen.Pro.ORMSupportClasses, Version=5.9.0.0, Culture=neutral, PublicKeyToken=ca73b74ba4e3ff27</ExceptionType><Message>An exception was caught during the execution of a retrieval query: The objects "RAVS.dbo.Party" and "RAVS.dbo.Party" in the FROM clause have the same exposed names. Use correlation names to distinguish them.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.</Message><StackTrace>   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 119
   at SD.LLBLGen.Pro.ORMSupportClasses.EntityMaterializerBase.Materialize(Func`4 valueReadErrorHandler, String&amp;amp; failureErrorText) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\EntityMaterializerBase.cs:line 150
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 635
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollectionInternal(QueryParameters parameters) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 4844
   at SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPathFetcher.FetchPrefetchPath(QueryParameters rootNodeParameters, Boolean forceParameterizedPPath, ITransaction transactionToUse, Int32 parameterisedPrefetchPathThreshold, Action`1 fetchNodeFunc, Action`4 mergeManyToManyFunc) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\PrefetchPathFetcher.cs:line 169
   at SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore.FetchPrefetchPath(QueryParameters rootNodeParameters, Boolean forceParameterizedPPath, ITransaction transactionToUse, Int32 parameterisedPrefetchPathThreshold, Action`1 fetchNodeFunc, Action`4 mergeManyToManyFunc) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\PersistenceCore.cs:line 104
   at SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPathFetcher.FetchPrefetchPath(QueryParameters rootNodeParameters, Boolean forceParameterizedPPath, ITransaction transactionToUse, Int32 parameterisedPrefetchPathThreshold, Action`1 fetchNodeFunc, Action`4 mergeManyToManyFunc) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\PrefetchPathFetcher.cs:line 192
   at SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore.FetchPrefetchPath(QueryParameters rootNodeParameters, Boolean forceParameterizedPPath, ITransaction transactionToUse, Int32 parameterisedPrefetchPathThreshold, Action`1 fetchNodeFunc, Action`4 mergeManyToManyFunc) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\PersistenceCore.cs:line 104
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.&amp;lt;&amp;gt;c__DisplayClass13_0.&amp;lt;FetchPrefetchPath&amp;gt;b__0() in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 311
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchAdditionalPrefetchPath(IPrefetchPath2 prefetchPath, Context contextToUse, IEntity2 fetchedEntity, IRelationPredicateBucket filterToUse) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 5465
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityUsingFilter(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, IRelationPredicateBucket filter, ExcludeIncludeFieldsList excludedIncludedFields) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 5010
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, ExcludeIncludeFieldsList excludedIncludedFields) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 1406
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.&amp;lt;&amp;gt;c__DisplayClass9_0.&amp;lt;FetchEntity&amp;gt;b__0() in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 235
   at Mrwa.Vls.Services.Framework.BaseEntityService`1.Fetch(Int32 aKey, IPrefetchPath2 aPrefetchPath, ExcludeIncludeFieldsList excludedFields) in E:\Users\c9646-adm\Source\Repos\RAVS\Services\ServicesFramework\BaseEntityService.cs:line 78</StackTrace><ExceptionString>SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The objects "RAVS.dbo.Party" and "RAVS.dbo.Party" in the FROM clause have the same exposed names. Use correlation names to distinguish them.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---&amp;gt; System.Data.SqlClient.SqlException: The objects "RAVS.dbo.Party" and "RAVS.dbo.Party" in the FROM clause have the same exposed names. Use correlation names to distinguish them.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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&amp;amp; 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, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task&amp;amp; task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task&amp;amp; task, Boolean&amp;amp; usedCache, Boolean asyncWrite, Boolean inRetry)
   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 SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.TagAndExecuteCommand[TReturn](Func`1 toExecute) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 352
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 110
   --- End of inner exception stack trace ---
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 119
   at SD.LLBLGen.Pro.ORMSupportClasses.EntityMaterializerBase.Materialize(Func`4 valueReadErrorHandler, String&amp;amp; failureErrorText) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\EntityMaterializerBase.cs:line 150
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 635
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityCollectionInternal(QueryParameters parameters) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 4844
   at SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPathFetcher.FetchPrefetchPath(QueryParameters rootNodeParameters, Boolean forceParameterizedPPath, ITransaction transactionToUse, Int32 parameterisedPrefetchPathThreshold, Action`1 fetchNodeFunc, Action`4 mergeManyToManyFunc) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\PrefetchPathFetcher.cs:line 169
   at SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore.FetchPrefetchPath(QueryParameters rootNodeParameters, Boolean forceParameterizedPPath, ITransaction transactionToUse, Int32 parameterisedPrefetchPathThreshold, Action`1 fetchNodeFunc, Action`4 mergeManyToManyFunc) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\PersistenceCore.cs:line 104
   at SD.LLBLGen.Pro.ORMSupportClasses.PrefetchPathFetcher.FetchPrefetchPath(QueryParameters rootNodeParameters, Boolean forceParameterizedPPath, ITransaction transactionToUse, Int32 parameterisedPrefetchPathThreshold, Action`1 fetchNodeFunc, Action`4 mergeManyToManyFunc) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\PrefetchPathFetcher.cs:line 192
   at SD.LLBLGen.Pro.ORMSupportClasses.PersistenceCore.FetchPrefetchPath(QueryParameters rootNodeParameters, Boolean forceParameterizedPPath, ITransaction transactionToUse, Int32 parameterisedPrefetchPathThreshold, Action`1 fetchNodeFunc, Action`4 mergeManyToManyFunc) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\PersistenceCore.cs:line 104
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.&amp;lt;&amp;gt;c__DisplayClass13_0.&amp;lt;FetchPrefetchPath&amp;gt;b__0() in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 311
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchAdditionalPrefetchPath(IPrefetchPath2 prefetchPath, Context contextToUse, IEntity2 fetchedEntity, IRelationPredicateBucket filterToUse) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 5465
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntityUsingFilter(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, IRelationPredicateBucket filter, ExcludeIncludeFieldsList excludedIncludedFields) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 5010
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse, ExcludeIncludeFieldsList excludedIncludedFields) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterCore.cs:line 1406
   at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.&amp;lt;&amp;gt;c__DisplayClass9_0.&amp;lt;FetchEntity&amp;gt;b__0() in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 235
   at Mrwa.Vls.Services.Framework.BaseEntityService`1.Fetch(Int32 aKey, IPrefetchPath2 aPrefetchPath, ExcludeIncludeFieldsList excludedFields) in E:\Users\c9646-adm\Source\Repos\RAVS\Services\ServicesFramework\BaseEntityService.cs:line 78</ExceptionString><InnerException><ExceptionType>System.Data.SqlClient.SqlException, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>The objects "RAVS.dbo.Party" and "RAVS.dbo.Party" in the FROM clause have the same exposed names. Use correlation names to distinguish them.</Message><StackTrace>   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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&amp;amp; 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, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task&amp;amp; task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task&amp;amp; task, Boolean&amp;amp; usedCache, Boolean asyncWrite, Boolean inRetry)
   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 SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.TagAndExecuteCommand[TReturn](Func`1 toExecute) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 352
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 110</StackTrace><ExceptionString>System.Data.SqlClient.SqlException (0x80131904): The objects "RAVS.dbo.Party" and "RAVS.dbo.Party" in the FROM clause have the same exposed names. Use correlation names to distinguish them.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 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&amp;amp; 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, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task&amp;amp; task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task&amp;amp; task, Boolean&amp;amp; usedCache, Boolean asyncWrite, Boolean inRetry)
   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 SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.TagAndExecuteCommand[TReturn](Func`1 toExecute) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 352
   at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in C:\Myprojects\VS.NET Projects\LLBLGen Pro v5.9\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 110

It is worth noting that The OperatorEntity is a sub-type of the PartyEntity in both the v2.5 and v5.9 versions of the project file, this function also ran as expected using the earlier version.

If you guys could provide some advice on how to resolve this one I would be forever grateful.

Please let me know if you require any further info.

Regards,

Michael Hennessey

Michael Hennessey
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39560
Joined: 17-Aug-2003
# Posted on: 12-Oct-2022 12:23:14   

The table name RAVS.dbo.Party is in the SQL query's FROM clause multiple times (e.g. in joins) and it requires an alias. So when you check the SQL query that's actually generated (you can do that either by using ORM Profiler https://www.llblgen.com/Pages/ormprofiler.aspx which is free for people with an active subscription or using tracing (https://www.llblgen.com/Documentation/5.9/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_troubleshootingdebugging.htm#dynamic-query-engine-tracing , or you can use the SQL Server profiler) you'll see it joins the table twice without using an alias.

A prefetch path fetch is going wrong, and to see which one, you have to dig up which sql query is produced which is going wrong. When the sql query is known we can see what exactly might be the problem.

(The stacktrace is containing the same parts twice too, not sure if that's a copy/paste error or a result of how the code works... )

Frans Bouma | Lead developer LLBLGen Pro
Posts: 7
Joined: 31-Aug-2022
# Posted on: 17-Oct-2022 05:08:20   

Hi Otis,

Thanks for the prompt response. The output from the SQL server profiler is as follows:


exec sp_executesql
N'SELECT DISTINCT 
    [xxx].[dbo].[Party].[Id] AS [F13_0], 
    [xxx].[dbo].[Party].[IsPerson] AS [F13_1], 
    [xxx].[dbo].[Party].[FirstName] AS [F13_2], 
    [xxx].[dbo].[Party].[LastName] AS [F13_3],     
    [xxx].[dbo].[Party].[OtherNames] AS [F13_4], 
    [xxx].[dbo].[Party].[BusinessName] AS [F13_5], 
    [xxx].[dbo].[Party].[ABN] AS [F13_6], 
    [xxx].[dbo].[Party].[ACN] AS [F13_7], 
    [xxx].[dbo].[Party].[RBN] AS [F13_8], 
    [xxx].[dbo].[Party].[Website] AS [F13_9], 
    [xxx].[dbo].[Party].[ResidentialAddressId] AS [F13_10], 
    [xxx].[dbo].[Party].[PostalAddressId] AS [F13_11], 
    [xxx].[dbo].[Party].[DriverLicenceId] AS [F13_12], 
    [xxx].[dbo].[Party].[IsActive] AS [F13_13], 
    [xxx].[dbo].[Party].[PartyWorkflowInstanceId] AS [F13_14], 
    [xxx].[dbo].[Party].[CreatedBy] AS [F13_15], 
    [xxx].[dbo].[Party].[CreatedDate] AS [F13_16], 
    [xxx].[dbo].[Party].[ModifiedBy] AS [F13_17], 
    [xxx].[dbo].[Party].[ModifiedDate] AS [F13_18], 
    [xxx].[dbo].[Party].[LogicallyDeletedBy] AS [F13_19], 
    [xxx].[dbo].[Party].[LogicallyDeletedDate] AS [F13_20], 
    [xxx].[dbo].[Party].[LastModified] AS [F13_21], 
    [xxx].[dbo].[Party].[PartyName] AS [F13_22], 
    [xxx].[dbo].[Pilot].[PartyId] AS [F14_23], 
    [xxx].[dbo].[Pilot].[PilotNumber] AS [F14_24], 
    [xxx].[dbo].[Pilot].[PreferredGeneralCommunicationTypeId] AS [F14_25], 
    [xxx].[dbo].[Pilot].[TrimFileNumber] AS [F14_26], 
    [xxx].[dbo].[Pilot].[InitialDate] AS [F14_27], 
    [xxx].[dbo].[Pilot].[IssueDate] AS [F14_28], 
    [xxx].[dbo].[Pilot].[ExpiryDate] AS [F14_29], 
    [xxx].[dbo].[Pilot].[GazettedDate] AS [F14_30], 
    [xxx].[dbo].[Pilot].[RemindedDate] AS [F14_31], 
    [xxx].[dbo].[Pilot].[PilotLicenceStatusId] AS [F14_32], 
    [xxx].[dbo].[Pilot].[IsTrafficEscortWarden] AS [F14_33], 
    [xxx].[dbo].[Pilot].[Duration] AS [F14_34], 
    [xxx].[dbo].[Pilot].[TrainingDocumentationAcceptedDate] AS [F14_35], 
    [xxx].[dbo].[Pilot].[ExpiryLetterSentDate] AS [F14_36], 
    [xxx].[dbo].[Pilot].[DateSetToExpired] AS [F14_37], 
    [xxx].[dbo].[Pilot].[WorkflowInstanceId] AS [F14_38] 
FROM (
    [xxx].[dbo].[Party] 
    INNER JOIN [xxx].[dbo].[Party] 
        ON [xxx].[dbo].[Party].[Id]=[xxx].[dbo].[Party].[Id]) 
    WHERE ( ( ( [xxx].[dbo].[Pilot].[WorkflowInstanceId] = @p1)) 
    AND ( [xxx].[dbo].[Pilot].[PartyId] IS NOT NULL))'
    

Appreciate the help.

Regards

Michael Hennessey
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39560
Joined: 17-Aug-2003
# Posted on: 17-Oct-2022 09:52:05   

It joins party twice as you can see, but it's unclear why. Please cut down the query in C# to the bare minimum that still reproduces the problem and provide more info so we can try to reproduce it. You use custom code so I have no idea what has changed on your end. It fails with the Pilot entity it seems.

Frans Bouma | Lead developer LLBLGen Pro