Hi,

I have the following model:

MedewerkerEntity N->M UserGroups N->M WebFuncties

I would like to filter the MedewerkerEntity based on the WebFuncties name like this:

[TestMethod()]
public void TestNestedManyToMany()
{
    _linqMetaData.Medewerker
        .Where(med => med.UserGroups
            .Any(ug => ug.WebFuncties
                .Any(wf => wf.Naam == "FunctieNaam")))
    .ToList().ForEach(Console.WriteLine);
}

This code fails with the following output:

------ Test started: Assembly: CC.ECDGenie.Dal.UnitTests.dll ------

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[CC.ECDGenie.Dal.EntityClasses.MedewerkerEntity]).Where(med => med.UserGroups.Any(ug => ug.WebFuncties.Any(wf => (wf.Naam = String.Empty))))
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPLA_3].[Type] FROM [dbo].[tb_ECDGenie_WebFunctie] [LPLA_3] (nolock) WHERE ( [LPLA_2].[ID] = [UserGroupWebfunctie_].[UsergroupId] AND ( [LPLA_3].[Naam] = @Naam1))
    Parameter: @Naam1 : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: "".

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [LPLA_2].[GroupLevel] FROM [dbo].[tb_XGT_UserGroups] [LPLA_2] (nolock) WHERE ( [LPLA_1].[__guid] = [MedewerkerUsergroup_].[MedewerkerId] AND  EXISTS (SELECT [LPLA_3].[Type] FROM [dbo].[tb_ECDGenie_WebFunctie] [LPLA_3] (nolock) WHERE ( [LPLA_2].[ID] = [UserGroupWebfunctie_].[UsergroupId] AND ( [LPLA_3].[Naam] = @Naam1))))
    Parameter: @Naam1 : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: "".

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [LPLA_1].[__lastchange] AS [Lastchange], [LPLA_1].[__lastchange_account] AS [LastchangeAccount], [LPLA_1].[__create_date] AS [CreateDate], [LPLA_1].[__create_account] AS [CreateAccount], [LPLA_1].[__pub_date] AS [PublicationDate], [LPLA_1].[__arch_date] AS [ArchiveDate], [LPLA_1].[__status] AS [Status], [LPLA_1].[__guid] AS [Id], [LPLA_1].[__parent_guid_ref] AS [ParentGuidReference], [LPLA_1].[__parent_virtabid_ref] AS [ParentVirtabidReference], [LPLA_1].[__seq] AS [Seq], [LPLA_1].[__ticker] AS [Ticker], [LPLA_1].[__account] AS [Account], [LPLA_1].[__sort] AS [Sort], [LPLA_1].[__locale] AS [Locale], [LPLA_1].[__code] AS [VirtabCodeVeld], [LPLA_1].[__co_checkedout] AS [CoCheckedout], [LPLA_1].[__co_date] AS [CoDate], [LPLA_1].[__co_account] AS [CoAccount], [LPLA_1].[__title] AS [Title], [LPLA_1].[__descr] AS [Description], [LPLA_1].[Code_string] AS [ Code ], [LPLA_1].[Email_string] AS [Email], [LPLA_1].[Naam_string] AS [Naam], [LPLA_1].[Telefoonnummer_string] AS [Telefoonnummer] FROM [dbo].[gtb_Medewerker] [LPLA_1] (nolock) WHERE ( ( (  EXISTS (SELECT [LPLA_2].[GroupLevel] FROM [dbo].[tb_XGT_UserGroups] [LPLA_2] (nolock) WHERE ( [LPLA_1].[__guid] = [MedewerkerUsergroup_].[MedewerkerId] AND  EXISTS (SELECT [LPLA_3].[Type] FROM [dbo].[tb_ECDGenie_WebFunctie] [LPLA_3] (nolock) WHERE ( [LPLA_2].[ID] = [UserGroupWebfunctie_].[UsergroupId] AND ( [LPLA_3].[Naam] = @Naam1))))))))
    Parameter: @Naam1 : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: "".

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
TestCase 'CC.ECDGenie.Dal.UnitTests.LlblgenTests.TestNestedManyToMany'
failed: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "MedewerkerUsergroup_.MedewerkerId" could not be bound.
The multi-part identifier "UserGroupWebfunctie_.UsergroupId" 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 "MedewerkerUsergroup_.MedewerkerId" could not be bound.
    The multi-part identifier "UserGroupWebfunctie_.UsergroupId" 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 "MedewerkerUsergroup_.MedewerkerId" could not be bound.
    The multi-part identifier "UserGroupWebfunctie_.UsergroupId" 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)
    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.Execute()
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
    at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
    at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
    D:\Projects\dotNet\Net3.5\CC.ECDGenie\MainLine\Dal\CC.ECDGenie.Dal.UnitTests\LlblgenTests.cs(63,0): at CC.ECDGenie.Dal.UnitTests.LlblgenTests.TestNestedManyToMany()

Apologies for this many bug reports

Please try the updated ormsupport classes in your other bugreport. I think they're related (at least if any of your reported entities is an inheritance entity).

Otis wrote:

Please try the updated ormsupport classes in your other bugreport. I think they're related (at least if any of your reported entities is an inheritance entity).

Sorry it doesn't work yet

------ Test started: Assembly: CC.ECDGenie.Dal.UnitTests.dll ------

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[CC.ECDGenie.Dal.EntityClasses.MedewerkerEntity]).Where(med => med.UserGroups.Any(ug => ug.WebFuncties.Any(wf => (wf.Naam = String.Empty))))
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [LPLA_3].[Type] FROM [dbo].[tb_ECDGenie_WebFunctie] [LPLA_3] (nolock) WHERE ( [LPLA_2].[ID] = [UserGroupWebfunctie_].[UsergroupId] AND ( [LPLA_3].[Naam] = @Naam1))
    Parameter: @Naam1 : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: "".

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [LPLA_2].[GroupLevel] FROM [dbo].[tb_XGT_UserGroups] [LPLA_2] (nolock) WHERE ( [LPLA_1].[__guid] = [MedewerkerUsergroup_].[MedewerkerId] AND  EXISTS (SELECT [LPLA_3].[Type] FROM [dbo].[tb_ECDGenie_WebFunctie] [LPLA_3] (nolock) WHERE ( [LPLA_2].[ID] = [UserGroupWebfunctie_].[UsergroupId] AND ( [LPLA_3].[Naam] = @Naam1))))
    Parameter: @Naam1 : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: "".

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [LPLA_1].[__lastchange] AS [Lastchange], [LPLA_1].[__lastchange_account] AS [LastchangeAccount], [LPLA_1].[__create_date] AS [CreateDate], [LPLA_1].[__create_account] AS [CreateAccount], [LPLA_1].[__pub_date] AS [PublicationDate], [LPLA_1].[__arch_date] AS [ArchiveDate], [LPLA_1].[__status] AS [Status], [LPLA_1].[__guid] AS [Id], [LPLA_1].[__parent_guid_ref] AS [ParentGuidReference], [LPLA_1].[__parent_virtabid_ref] AS [ParentVirtabidReference], [LPLA_1].[__seq] AS [Seq], [LPLA_1].[__ticker] AS [Ticker], [LPLA_1].[__account] AS [Account], [LPLA_1].[__sort] AS [Sort], [LPLA_1].[__locale] AS [Locale], [LPLA_1].[__code] AS [VirtabCodeVeld], [LPLA_1].[__co_checkedout] AS [CoCheckedout], [LPLA_1].[__co_date] AS [CoDate], [LPLA_1].[__co_account] AS [CoAccount], [LPLA_1].[__title] AS [Title], [LPLA_1].[__descr] AS [Description], [LPLA_1].[Code_string] AS [ Code ], [LPLA_1].[Email_string] AS [Email], [LPLA_1].[Naam_string] AS [Naam], [LPLA_1].[Telefoonnummer_string] AS [Telefoonnummer] FROM [dbo].[gtb_Medewerker] [LPLA_1] (nolock) WHERE ( ( (  EXISTS (SELECT [LPLA_2].[GroupLevel] FROM [dbo].[tb_XGT_UserGroups] [LPLA_2] (nolock) WHERE ( [LPLA_1].[__guid] = [MedewerkerUsergroup_].[MedewerkerId] AND  EXISTS (SELECT [LPLA_3].[Type] FROM [dbo].[tb_ECDGenie_WebFunctie] [LPLA_3] (nolock) WHERE ( [LPLA_2].[ID] = [UserGroupWebfunctie_].[UsergroupId] AND ( [LPLA_3].[Naam] = @Naam1))))))))
    Parameter: @Naam1 : String. Length: 100. Precision: 0. Scale: 0. Direction: Input. Value: "".

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
TestCase 'CC.ECDGenie.Dal.UnitTests.LlblgenTests.TestNestedManyToMany'
failed: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "MedewerkerUsergroup_.MedewerkerId" could not be bound.
The multi-part identifier "UserGroupWebfunctie_.UsergroupId" 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 "MedewerkerUsergroup_.MedewerkerId" could not be bound.
    The multi-part identifier "UserGroupWebfunctie_.UsergroupId" 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 "MedewerkerUsergroup_.MedewerkerId" could not be bound.
    The multi-part identifier "UserGroupWebfunctie_.UsergroupId" 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)
    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.Execute()
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
    at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
    at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
    D:\Projects\dotNet\Net3.5\CC.ECDGenie\MainLine\Dal\CC.ECDGenie.Dal.UnitTests\LlblgenTests.cs(62,0): at CC.ECDGenie.Dal.UnitTests.LlblgenTests.TestNestedManyToMany()

Do the entities involved have an inheritance hierarchy? that's important for me to be able to reproduce it. (also with hierarchy type please and if they share a common parent)

Also, if time permits, please check the fix I added to the other thread to see if that bug is fixed now (I can't repro it anymore)

Reproduced...

var q = metaData.Customer.Where(c => c.EmployeeCollectionViaOrder.Any(
                    e => e.TerritoryCollectionViaEmployeeTerritory.Any(
                        t => t.RegionId == 1)));

so ... I'll look into it!

(edit) relation propagation error (relations to reach a related element aren't added to the main query. There's also something else wrong with this, which I'll look into as well.

For example, where extra relations aren't required, it works:

var q = metaData.Customer.Where(c => c.Orders.Any(o => o.OrderDetails.Any(od => od.ProductId == 1)));

(edit) the m:n stuff is here a bit broken I think. the "Any/All" handler assumes both involved entities are related directly, which isn't the case. it therefore picks the wrong entities to formulate a correlation filter and forgets to add the start-intermediate relation.

Got it 1 line of code makes all the difference. pfew

(cool way to test for things btw, I never thought about these).

See attached linq provider.

Otis wrote:

Got it 1 line of code makes all the difference. pfew

(cool way to test for things btw, I never thought about these).

See attached linq provider.

Works all fine now! Also the other issue about subtypes is solved... Thanks!