Hello,

I get an error in the following scenario:

.......................................................................Expense -- (M:1)--> ScopeFrequency ..............................................................................| Case -- (1:N) --> Recommendation -- (1:N)--> Proposal

The relations between the entities are:

Case-Recommendation (1:n) Recommendation-Proposal (1:n) Expense-ScopeFrequency (m:1)

Proposal is a subtype of Expense with the hierachy type being one target per entity type.

The following code raises an error:

DaoBase.ParameterisedPrefetchPathThreshold = 15;

CaseEntity c = new CaseEntity();

IPrefetchPath pp = new PrefetchPath((int)EntityType.CaseEntity);

pp.Add(CaseEntity.PrefetchPathRecommendation).
     SubPath.Add(RecommendationEntity.PrefetchPathProposal).
     SubPath.Add(ProposalEntity.PrefetchPathScopeFrequency);
        
c.FetchUsingPK(1, pp);

The number of Proposal-entities related to each Recommendation-entity exceeds the parameterisedprefetchpathtthreshold.

The error generated is this (copy-pasted from the console in VS2005)

Method Enter: DaoBase.PerformFetchEntityAction
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [TestDB].[dbo].[Case].[CaseID] AS [CaseId], [TestDB].[dbo].[Case].[CaseName] FROM [TestDB].[dbo].[Case]  WHERE ( [TestDB].[dbo].[Case].[CaseID] = @CaseId1)
    Parameter: @CaseId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

Method Exit: CreateSelectDQ
Method Enter: DaoBase.ExecuteSingleRowRetrievalQuery
'Home.Console.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'Home.Console.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.EnterpriseServices\2.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
Method Exit: DaoBase.ExecuteSingleRowRetrievalQuery
Method Enter: DaoBase.FetchPrefetchPath
Method Enter: DaoBase.PerformGetMultiAction
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [TestDB].[dbo].[Recommendation].[RecommendationID] AS [RecommendationId], [TestDB].[dbo].[Recommendation].[CaseID] AS [CaseId], [TestDB].[dbo].[Recommendation].[RecommendationName] FROM [TestDB].[dbo].[Recommendation]  WHERE ( ( ( ( [TestDB].[dbo].[Recommendation].[CaseID] = @CaseId1))))
    Parameter: @CaseId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: DaoBase.ExecuteMultiRowRetrievalQuery
Method Exit: DaoBase.ExecuteMultiRowRetrievalQuery
Method Exit: DaoBase.PerformGetMultiAction
Method Enter: DaoBase.MergeNormal
Method Exit: DaoBase.MergeNormal
Method Enter: DaoBase.FetchPrefetchPath
Method Enter: DaoBase.PerformGetMultiAction
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT DISTINCT [TestDB].[dbo].[Expense].[ExpenseID] AS [F0], [TestDB].[dbo].[Expense].[ExpenseName] AS [F1], [TestDB].[dbo].[Expense].[ScopeFrequencyID] AS [F2], [TestDB].[dbo].[Proposal].[ProposalID] AS [F3], [TestDB].[dbo].[Proposal].[ProposalName] AS [F4], [TestDB].[dbo].[Proposal].[RecommendationID] AS [F5] FROM ( [TestDB].[dbo].[Expense]  INNER JOIN [TestDB].[dbo].[Proposal]  ON  [TestDB].[dbo].[Expense].[ExpenseID]=[TestDB].[dbo].[Proposal].[ProposalID]) WHERE ( [TestDB].[dbo].[Proposal].[ProposalID] IS NOT NULL AND ( ( ( [TestDB].[dbo].[Proposal].[RecommendationID] = @RecommendationId1))))
    Parameter: @RecommendationId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: DaoBase.ExecuteMultiRowRetrievalQuery
Method Exit: DaoBase.ExecuteMultiRowRetrievalQuery
Method Exit: DaoBase.PerformGetMultiAction
Method Enter: DaoBase.MergeNormal
Method Exit: DaoBase.MergeNormal
Method Enter: DaoBase.FetchPrefetchPath
Method Enter: DaoBase.PerformGetMultiActionInternal
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [TestDB].[dbo].[Expense].[ScopeFrequencyID] AS [ScopeFrequencyId] FROM [TestDB].[dbo].[Expense]  WHERE ( ( ( ( [TestDB].[dbo].[Proposal].[RecommendationID] = @RecommendationId1))))
    Parameter: @RecommendationId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [TestDB].[dbo].[ScopeFrequency].[ScopeFrequencyID] AS [ScopeFrequencyId], [TestDB].[dbo].[ScopeFrequency].[ScopeFrequencyName] FROM [TestDB].[dbo].[ScopeFrequency]  WHERE ( ( [TestDB].[dbo].[ScopeFrequency].[ScopeFrequencyID] IN (SELECT [TestDB].[dbo].[Expense].[ScopeFrequencyID] AS [ScopeFrequencyId] FROM [TestDB].[dbo].[Expense]  WHERE ( ( ( ( [TestDB].[dbo].[Proposal].[RecommendationID] = @RecommendationId1)))))))
    Parameter: @RecommendationId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: DaoBase.ExecuteMultiRowRetrievalQuery
A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll
Method Exit: DaoBase.ExecuteMultiRowRetrievalQuery
SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "TestDB.dbo.Proposal.RecommendationID" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.Data.SqlClient.SqlException: The multi-part identifier "TestDB.dbo.Proposal.RecommendationID" could not be bound.
   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)

The configuration is: LLBLGen V2.0.0.0, Selfservicing, Build: 09282006, .NET2.0, SQL2005

Thanks in advance,

Niels Johansen, Rehfeld

Please read the guidelines: post REAL code. You seem to have altered the queries: SELECT [TestDB].[dbo]..[Bid]

see the missing table name? Or is that what's been generated? What do 'A', 'B' etc. mean, please use the real names of the entities. (unless you're not allowed to by NDA for example) Using these A, B etc. is very cumbersome, and you might have made a mistake here and there ( as you did with the queries apparently)

Hello,

Thanks for your response.

This is real code produced from a simple program exhibiting the error. I have just copy-pasted the code into the editor - not altered the queries. However, I can also see than something is missing when comparing the queries with the ones generated in the console. I will correct it and also give the entities more meaningful names..

I have edited the initial post - giving the entities the same names as in our production code.

Hope this helps on the readability....

(In the inital example the [ B ] was intepreted as the bold-tag i the editor. Therefore, it looked like the queries were manually altered)..

/niels

System.Data.SqlClient.SqlException: The multi-part identifier "TestDB.dbo.Proposal.RecommendationID" could not be bound.

Looks like this expression is bombing - are you sure your production and test db's are the same?

Looks like this expression is bombing - are you sure your production and test db's are the same?

Yes, the schemas are similar. It seems to me that the problem is in the last query:

FROM [TestDB].[dbo].[Expense] WHERE ( ( ( ( [TestDB].[dbo].[Proposal].[RecommendationID] = @RecommendationId1)))))))

where the prefetch logic omits the join to the sub-class entity, - that in this particular situation is needed - as the WHERE-clause refers to an attribute of the sub-class entity.

Would you please try the latest release of the runtimeLibraries?

Walaa wrote:

Would you please try the latest release of the runtimeLibraries?

Thanks for your response.

We are already using the latest release of the runtime libraries released 30.sept.2006. Build 09302006 (release-info from the download-section on the web-site)

The assembly-fileversion of the ORMSupportClasses-assembly in that release has build-number: 2.0.0.060928

The following code returns the Build-number: 09282006

SD.LLBLGen.Pro.ORMSupportClasses.RuntimeLibraryVersion.Build

(removed my previous post, didn't make sense)

(edit). Ok it's this query:

SELECT [TestDB].[dbo].[ScopeFrequency].[ScopeFrequencyID] AS [ScopeFrequencyId], [TestDB].[dbo].[ScopeFrequency].[ScopeFrequencyName] FROM [TestDB].[dbo].[ScopeFrequency] WHERE ( ( [TestDB].[dbo].[ScopeFrequency].[ScopeFrequencyID] IN (SELECT [TestDB].[dbo].[Expense].[ScopeFrequencyID] AS [ScopeFrequencyId] FROM [TestDB].[dbo].[Expense] WHERE ( ( ( ( [TestDB].[dbo].[Proposal].[RecommendationID] = @RecommendationId1)))))))

Which refers to RecommendationID in proposal, but doesn't join subtypes into the list.

The subquery doesn't take into account a type being a supertype apparently.... will look into it.

Reproduced. (companycar -> boardmember -> WorksForDepartment. That last node is inherited from employee by boardmember, and runs into the same issue. )

Parameterized version of the fetch works, it fails when it has to revert to subqueries due to an exceeding of the threshold. The strange thing is that it does add the inheritance relations to the subquery...

Ok, it happens when the inheritance using node is fetched using a parameterized fetch. I set my threshold to 1, and I had one companycar, so the boardmember was fetched using the parameterized version, then that filter was passed on to the fetch for department, which failed as the filter didn't contain the inheritance hierarchy.

When I set the threshold to 0, it did and everything worked.

So workaround: set threshold to 0 for this query, it then will succeed. I'm looking into a way to fix this.

Ok, I've found the error cause: The parameterized path used the public fetchentitycollection /GetMulti(), and the subquery version uses the internal routine. The public routine uses a wrapper around filter/relations, which means the inheritance relations aren't propagated into the filter. The internal version makes sure of that. All tests pass for adapter, I'm now checking if selfservicing tests also work with this fix.

SVN Blame suggests the code was written in early 2005, and only updated in january 2006 for the subquery one, so the difference isn't related to a bugfix, I think it's caused by a lack of a proper unittest which illustrates this issue so this wasn't found earlier.

(edit). Fixed in next build.