Hi Frans,
We have a database model (see SQL attached, removed some unnecessary fields) like this:
Kamer -(1-n)-> Plaatsslot -(1-n)-> Plaatstoewijzing
We're trying it to query the structure like this:
public void LlblgenJoinTest()
{
var e = from k in _linqMetaData.Kamer
where k.Nummer == "B01"
from ps in k.Plaatssloten
from pt in ps.Plaatstoewijzingen
select pt;
}
For some reason this causes wrong SQL to be generated (a CROSS JOIN?).
I'm using the latest LLBLGen build (04-07-2008 ).
This is the traced output:
: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[CC.ECDGenie.Dal.EntityClasses.KamerEntity]).SelectMany(k => k.Plaatssloten, (k, ps) => new <>f__AnonymousType0`2(k = k, ps = ps)).SelectMany(<>h__TransparentIdentifier0 => <>h__TransparentIdentifier0.ps.Plaatstoewijzing, (<>h__TransparentIdentifier0, pt) => pt)
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query:
Query: SELECT DISTINCT [LPA_L3].[id] AS [Id], [LPA_L3].[plaatsslot_id] AS [PlaatsslotId] FROM ( [DEV_CC_ECDGenie].[dbo].[tb_ECDGenie_Kamer] [LPA_L1] CROSS JOIN [DEV_CC_ECDGenie].[dbo].[tb_ECDGenie_Plaatsslot] [LPA_L2] )
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
TestCase 'CC.ECDGenie.Dal.UnitTests.LlblgenTests.LlblgenJoinTest'
failed: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "LPA_L3.id" could not be bound.
The multi-part identifier "LPA_L3.plaatsslot_id" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "LPA_L3.id" could not be bound.
The multi-part identifier "LPA_L3.plaatsslot_id" 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 "LPA_L3.id" could not be bound.
The multi-part identifier "LPA_L3.plaatsslot_id" 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)
--- End of inner exception stack trace ---
at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
D:\Projects\dotNet\Net3.5\CC.ECDGenie\MainLine\Dal\DatabaseSpecific\DataAccessAdapter.User.cs(51,0): at CC.ECDGenie.Dal.DatabaseSpecific.DataAccessAdapter.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteEntityProjection(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.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
D:\Projects\dotNet\Net3.5\CC.ECDGenie\MainLine\Dal\CC.ECDGenie.Dal.UnitTests\LlblgenTests.cs(48,0): at CC.ECDGenie.Dal.UnitTests.LlblgenTests.LlblgenJoinTest()
0 passed, 1 failed, 0 skipped, took 1,75 seconds.
(I know I can rewrite the query in a different way so it works properly, so there's no rush
Attachments
Filename |
File size |
Added on |
Approval |
testdata.sql
|
4,992 |
08-Jul-2008 15:05.25 |
Approved |