I am having difficulty setting aliases for Prefetch paths where I need to supply a predicate expression where a collection entity exists for a many to many relationship. The resulting SQL shows the problem. The aliases from the predicate expression - there are none - do not match those produced by the prefetch path relation.

As the SetAliases method provides only a start and end alias, how can I set aliases for all relations in a prefetch path which could have more than 2 relations defined.

    filterArgs.PrefetchPath.Add
            (MyTblDocumentEntity.PrefetchPathTblFileCollectionViaTblDocumentEvent, _
                0, New RelationPredicateBucket( _
             (RegisterHelper.TblFileFields.Status =  "Added") And _
             (RegisterHelper.TblDocumentEventFields.EventName = 
                                                        "Active")).PredicateExpression)

The resulting SQL generated for this is as follows:

SELECT DISTINCT 
[Register].[dbo].[tblFile].[FileID] AS [FileId], 
[Register].[dbo].[tblFile].[RevID] AS [RevId], 
[Register].[dbo].[tblFile].[FileName], 
[Register].[dbo].[tblFile].[Extension], 
[Register].[dbo].[tblFile].[Status] 
FROM (( [Register].[dbo].[tblDocument] [LPA__1]  
INNER JOIN [Register].[dbo].[tblDocumentEvent] 
    [LPA_T2]  
ON  [LPA__1].[DocID]=[LPA_T2].[DocID]) 
INNER JOIN [Register].[dbo].[tblFile]  
ON  
    [Register].[dbo].[tblFile].[FileID]=[LPA_T2].[FileID]) 
WHERE ( ( ( 
[LPA_T2].[DocID] = 4118)) 
AND 
( ( 
[Register].[dbo].[tblFile].[Status] = 'File Added' 
AND [Register].[dbo].[tblDocumentEvent].[EventName] = 'Active'
)))

Are you getting an exception or just empty results? Could you please further explain what you're trying to accomplish?

Thanks for your response.

I get an exception message: ".Net SqlClient Data Provider" "The multi-part identifier "Register.dbo.tblDocumentEvent.EventName" could not be bound."

What I am trying to achieve is to filter the prefetch path data by setting the filter in the constructor and in this example the prefetch path is a many to many relationship. The resulting SQL shows 3 related entities.

When I add my filter condition the aliases are not the same as those generated for the entities. I understand what the error means, I just can't see how to get around ensuring that the aliases are correctly set I've tried the SetObjectAlias method on the filter fields but to no avail as shown below

MyTblDocumentEntity.PrefetchPathTblFileCollectionViaTblDocumentEvent.Relation.SetAliases("d", "de")

filterArgs.PrefetchPath.Add(MyTblDocumentEntity.PrefetchPathTblFileCollectionViaTblDocumentEvent, _
                            0, New RelationPredicateBucket( _
                            (TblFileFields.Status.SetObjectAlias("f") = "FileAdded") And _
                            (TblDocumentEventFields.EventName.SetObjectAlias("de") = "Active")).PredicateExpression)

Additional Info: Runtime Lib version is 2.0.07.0611 Using LLBLGenPRO 2.0 Adapter model SQL Server Database

MyTblDocumentEntity.PrefetchPathTblFileCollectionViaTblDocumentEvent.Relation.SetAliases("d", "de")

filterArgs.PrefetchPath.Add(MyTblDocumentEntity.PrefetchPathTblFileCollectionViaTblDocumentEvent, _ 0, New RelationPredicateBucket( _ (TblFileFields.Status.SetObjectAlias("f") = "FileAdded") And _ (TblDocumentEventFields.EventName.SetObjectAlias("de") = "Active")).PredicateExpression)

Please post the complete fetch code.

This is the code that fails, when the adapter.FetchEntity is called

    Dim DocId As Integer = 4111
    Dim entity As IEntity2
    Dim prefetchPath as IPrefetchPath2

    prefetchPath = New PrefetchPath2(CType(EntityType.TblDocumentEntity, Integer))

    'Add prefetch paths - 1 : n relations 
    prefetchPath.Add(MyTblDocumentEntity.PrefetchPathTblGroupModify)
    prefetchPath.Add(MyTblDocumentEntity.PrefetchPathTblGroupRead)

    'Add the prefetch path with filter condition - m : n
    prefetchPath.Add (MyTblDocumentEntity.PrefetchPathTblFileCollectionViaTblDocumentEvent, _
       0, New RelationPredicateBucket( _
       (TblFileFields.Status = "FileAdded") And _
       (TblDocumentEventFields.EventName = "Active")).PredicateExpression)

    'Create the parent entity with primary key
    entity = New MyTblDocumentEntity(DocId)

    'Get the data using the adapter - errors here
    adapter.FetchEntity(entity, prefetchPath, Nothing)

This works, if I remove the filter condition:

    Dim DocId As Integer = 4111
    Dim entity As IEntity2
    Dim prefetchPath as IPrefetchPath2

    prefetchPath = New PrefetchPath2(CType(EntityType.TblDocumentEntity, Integer))

    'Add prefetch paths - 1 : n relations 
    prefetchPath.Add(MyTblDocumentEntity.PrefetchPathTblGroupModify)
    prefetchPath.Add(MyTblDocumentEntity.PrefetchPathTblGroupRead)

    'Add the prefetch path WITHOUT filter condition - m : n
    prefetchPath.Add(MyTblDocumentEntity.PrefetchPathTblFileCollectionViaTblDocumentEvent)

    'Create the parent entity with primary key
    entity = New MyTblDocumentEntity(DocId) 

    'Get the data using the adapter
    adapter.FetchEntity(entity, prefetchPath, Nothing)

Resulting SQL

Generated Sql query: Query: SELECT DISTINCT [FocusADRegister].[dbo].[tblFile].[FileID] AS [FileId], [FocusADRegister].[dbo].[tblFile].[RevID] AS [RevId], [FocusADRegister].[dbo].[tblFile].[FileName], [FocusADRegister].[dbo].[tblFile].[Extension], [FocusADRegister].[dbo].[tblFile].[Status] FROM (( [FocusADRegister].[dbo].[tblDocument] [LPA__1] INNER JOIN [FocusADRegister].[dbo].[tblDocumentEvent] [LPA_T2] ON [LPA__1].[DocID]=[LPA_T2].[DocID]) INNER JOIN [FocusADRegister].[dbo].[tblFile] ON [FocusADRegister].[dbo].[tblFile].[FileID]=[LPA_T2].[FileID]) WHERE ( ( ( [LPA_T2].[DocID] = @DocId1)) AND ( ( [FocusADRegister].[dbo].[tblFile].[Status] = @Status2 AND [FocusADRegister].[dbo].[tblDocumentEvent].[EventName] = @EventName3))) Parameter: @DocId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 4118. Parameter: @Status2 : String. Length: 10. Precision: 0. Scale: 0. Direction: Input. Value: "File Added". Parameter: @EventName3 : String. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "Active". Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ: no paging. Method Enter: DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery Method Enter: DataAccessAdapterBase.OpenConnection Method Exit: DataAccessAdapterBase.OpenConnection A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll Method Exit: DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery Method Enter: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.CloseConnection

StackTrace

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, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchParameterisedPrefetchPath(IEntityCollection2 rootEntities, Int64 maxNumberOfItemsToReturn, IPrefetchPath2 prefetchPath) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse) at FocusBLL.UIHelper.BaseHelper.GetBaseEntity(enumDataAccessAdapters adapter, IEntity2 entity, IPrefetchPath2 prefetchPath, Context contextToUse) in C:\projects\FOCUSi\trunk\Development\Focus\BLL\FocusBLL\BaseHelper.vb:line 270

prefetchPath.Add (MyTblDocumentEntity.PrefetchPathTblFileCollectionViaTblDocumentEvent, _ 0, New RelationPredicateBucket( _ (TblFileFields.Status = "FileAdded") And _ (TblDocumentEventFields.EventName = "Active")).PredicateExpression)

First of all you don't need to use a RelationPredicateBucket, just use a PredicateExpression.

You are fetching from the TblFile entity and you have a filter on the middle entity (TblDocumentEvent), then you should provide the relation ebtween them in the RelationCollection parameter accepted by another overload of the PrefetchPath.Add() method.

Or you can use a PrefetchPath to TblDocumentEvent with a SubPath to TblFile with appropriate filters set to each.

Thank you. I tried that and the alias issue seems to be resolved, in that the SQL rendered is correct and when run in a Query Analyser window retrieves the rows required.

However now when I call adapter.FetchEntity I get a timeout error, I tried increasing the timeout to 180 secs and it still times out. The SQL query shown here only takes around 200ms to run in SQL Query Analyser.

What could FetchEntity be doing that takes so long? The SQL profiler shows the execution as complete, while the VS debugger is still waiting for the FetchEntity to return.

Excuse me if I am missing the obvious here, but I am finding this very trying.

    Dim DocId As Integer = 4111
    Dim entity As IEntity2
    Dim prefetchPath as IPrefetchPath2

    'Add prefetch paths - 1 : n relations 
    prefetchPath.Add(MyTblDocumentEntity.PrefetchPathTblGroupModify)
    prefetchPath.Add(MyTblDocumentEntity.PrefetchPathTblGroupRead)

    Dim filterRelations As IRelationCollection = New RelationCollection()
    Dim predExpression As IPredicateExpression = New PredicateExpression()

    'Create predicate expression    
    predExpression.Add((TblFileFields.Status = "Active"))
    predExpression.Add((TblDocumentEventFields.EventName = "File Added"))

    Dim relation As IEntityRelation
    relation = MyTblFileEntity.Relations.TblDocumentEventEntityUsingFileId

    'Create a filter relation
    filterRelations.Add(relation)

    'Add the prefect with filter
    prefetchPath.Add( 
                   MyTblDocumentEntity.PrefetchPathTblFileCollectionViaTblDocumentEvent, 0, _
                    predExpression, filterRelations)

    'Create the parent entity with primary key
    entity = New MyTblDocumentEntity(DocId)

    'Get the data using the adapter - errors here
    adapter.FetchEntity(entity, prefetchPath, Nothing)

Resulting SQL runs without error in Query Analyser

exec sp_executesql N'SELECT DISTINCT [FocusADRegister].[dbo].[tblFile].[FileID] AS [FileId], [FocusADRegister].[dbo].[tblFile].[RevID] AS [RevId], [FocusADRegister].[dbo].[tblFile].[FileName], [FocusADRegister].[dbo].[tblFile].[Extension], [FocusADRegister].[dbo].[tblFile].[Status] FROM ((( [FocusADRegister].[dbo].[tblDocument] [LPA__1] INNER JOIN [FocusADRegister].[dbo].[tblDocumentEvent] [LPA_T2] ON [LPA__1].[DocID]=[LPA_T2].[DocID]) INNER JOIN [FocusADRegister].[dbo].[tblFile] ON [FocusADRegister].[dbo].[tblFile].[FileID]=[LPA_T2].[FileID]) INNER JOIN [FocusADRegister].[dbo].[tblDocumentEvent] ON
[FocusADRegister].[dbo].[tblFile].[FileID]=[FocusADRegister].[dbo].[tblDocumentEvent].[FileID]) WHERE ( ( ( [LPA_T2].[DocID] = @DocId1)) AND ( ( [FocusADRegister].[dbo].[tblFile].[Status] = @Status2 AND [FocusADRegister].[dbo].[tblDocumentEvent].[EventName] = @EventName3)))',N'@DocId1 int,@Status2 nvarchar(10),@EventName3 nvarchar(50)',@DocId1=4118,@Status2=N'Active',@EventName3=N'File Added'

The Fetch methods executes a SQL query to fetch the entity in hand, and another queries to fetch the different prefetchPaths envolved.

Would you please examine the rest of the queries too?

Thanks.

Walaa wrote:

Would you please examine the rest of the queries too?

Sure. I have collected all the queries from SQL profiler and run them all in SQL Query Analyser. The data is what I expect and it works as you described. They all run in less than 1 second and return the expected data.

I even cut the code down to only do the prefetch path with filter and filter relations in it and I had the same result - a timeout. If I remove the filter and filter relations it works.

Timeout still occurs with only the one prefetch path used with filter and relation

    Dim DocId As Integer = 4111
    Dim entity As IEntity2
    Dim prefetchPath as IPrefetchPath2
    Dim filterRelations As IRelationCollection = New RelationCollection()
    Dim predExpression As IPredicateExpression = New PredicateExpression()

    'Create predicate expression    
    predExpression.Add((TblFileFields.Status = "Active"))
    predExpression.Add((TblDocumentEventFields.EventName = "File Added"))

    Dim relation As IEntityRelation
    relation = MyTblFileEntity.Relations.TblDocumentEventEntityUsingFileId

    'Create a filter relation
    filterRelations.Add(relation)

    'Add the prefect with filter
    prefetchPath.Add( 
                 MyTblDocumentEntity.PrefetchPathTblFileCollectionViaTblDocumentEvent, 0, _
                    predExpression, filterRelations)

    'Create the parent entity with primary key
    entity = New MyTblDocumentEntity(DocId)

    'Get the data using the adapter - errors here
    adapter.FetchEntity(entity, prefetchPath, Nothing)

Would I not be better off prefetching the m:n collection and doing an in memory filter as a work around? So just remove the filter and filterRelations in the constructor.

Thank you for taking time to look at this. I know this is difficult when you don't have the benefit of seeing the actual code and environment.

However now when I call adapter.FetchEntity I get a timeout error

Would you please post the exact exception text and stack trace?

Would I not be better off prefetching the m:n collection and doing an in memory filter as a work around? So just remove the filter and filterRelations in the constructor.

That's a possible workaround, but it won't be the ideal thing to do if you have many entities fetched that you don't need.

Right here are all the details from a test run. As I said before if I remove the relation and relationFilter, it works fine. SQL profiler shows the query as complete, but the adapter.FetchEntity just sits there in the VS debugger waiting until the timeout.

ORMQueryExecutionException Message An exception was caught during the execution of a retrieval query: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

ORMQueryExecutionException Stacktrace 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, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchParameterisedPrefetchPath(IEntityCollection2 rootEntities, Int64 maxNumberOfItemsToReturn, IPrefetchPath2 prefetchPath) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntity(IEntity2 entityToFetch, IPrefetchPath2 prefetchPath, Context contextToUse) at FocusBLL.UIHelper.BaseHelper.GetBaseEntity(enumDataAccessAdapters adapter, IEntity2 entity, IPrefetchPath2 prefetchPath, Context contextToUse) in BaseHelper.vb:line 270

Exception Message Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Inner Exception Message 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)

QueryExecuted - this query takes less than 100ms if run in Query Analyser and only returns 12 rows of data Query: SELECT DISTINCT [Register].[dbo].[tblFile].[FileID] AS [FileId], [Register].[dbo].[tblFile].[RevID] AS [RevId], [Register].[dbo].[tblFile].[FileName], [Register].[dbo].[tblFile].[Extension], [Register].[dbo].[tblFile].[Status] FROM ((( [Register].[dbo].[tblDocument] [LPA__1] INNER JOIN [Register].[dbo].[tblDocumentEvent] [LPA_T2] ON [LPA__1].[DocID]=[LPA_T2].[DocID]) INNER JOIN [Register].[dbo].[tblFile] ON [Register].[dbo].[tblFile].[FileID]=[LPA_T2].[FileID]) INNER JOIN [Register].[dbo].[tblDocumentEvent] ON [Register].[dbo].[tblFile].[FileID]=[Register].[dbo].[tblDocumentEvent].[FileID]) WHERE ( ( ( [LPA_T2].[DocID] = @DocId1)) AND ( ( [Register].[dbo].[tblFile].[Status] = @Status2 AND [Register].[dbo].[tblDocumentEvent].[EventName] = @EventName3))) Parameter: @DocId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 4118. Parameter: @Status2 : String. Length: 10. Precision: 0. Scale: 0. Direction: Input. Value: "Active". Parameter: @EventName3 : String. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "File Added".

Further to this. If I remove either one of the predicates, by commenting them out, it works.

This is the m:n relation scenario again with three tables that make up the PrefetchPathTblFileCollectionViaTblDocumentEvent:

TblDocumentEntity has 1:n with TblDocumentEvent TblFileEntity has 1:n with TblDocumentEvent TblDocumentEvent is the intermediate table

If I'm using the predicate expression incorrectly I can deal with that, but otherwise I'm inclined to think this is a bug.

    Dim DocId As Integer = 4111
    Dim entity As IEntity2
    Dim prefetchPath as IPrefetchPath2
    Dim filterRelations As IRelationCollection = New RelationCollection()
    Dim predExpression As IPredicateExpression = New PredicateExpression()

    'Create predicate expression    
    

    '********R  E  M  0  V  E*****************************************

    predExpression.Add((TblFileFields.Status = "Active"))


    '**************0  R*****************************************


    predExpression.Add((TblDocumentEventFields.EventName = "File Added"))

    '************************************************************

    Dim relation As IEntityRelation
    relation = MyTblFileEntity.Relations.TblDocumentEventEntityUsingFileId

    'Create a filter relation
    filterRelations.Add(relation)

    'Add the prefect with filter
    prefetchPath.Add( 
                 MyTblDocumentEntity.PrefetchPathTblFileCollectionViaTblDocumentEvent, 0, _
                    predExpression, filterRelations)

    'Create the parent entity with primary key
    entity = New MyTblDocumentEntity(DocId)

    'Get the data using the adapter - errors here
    adapter.FetchEntity(entity, prefetchPath, Nothing)

Looks like the alias bug with inheritance we had to fix with an API change in v2.5. Will look into it. (you use inheritance in any of the involved entities?)

Otis wrote:

Looks like the alias bug with inheritance we had to fix with an API change in v2.5. Will look into it. (you use inheritance in any of the involved entities?)

Thanks for your response here. If by inheritance, you mean the two classes code generation option, then yes.

To add to this - when using SQL Profiler - and a single predicate 3 SQL queries are generated, whereas when using two predicates in the predicate expression only two are generated and the timeout occurs.

I would like also to say a big thank you for all the support you and your team provide. This forum is invaluable and really gives me confidence in the product and it's future. As you are in touch with the issues people may be having or just helping newbies like myself to get over the basics, I believe you are offering far more than just a useful product. I wish you every deserved success!

I am now totally convinced of the benefits of LLBLGen and will be buying my license now that my evaluation period has come to an end.

First your problem:

sbense wrote:

I am having difficulty setting aliases for Prefetch paths where I need to supply a predicate expression where a collection entity exists for a many to many relationship. The resulting SQL shows the problem. The aliases from the predicate expression - there are none - do not match those produced by the prefetch path relation.

As the SetAliases method provides only a start and end alias, how can I set aliases for all relations in a prefetch path which could have more than 2 relations defined.

    filterArgs.PrefetchPath.Add
            (MyTblDocumentEntity.PrefetchPathTblFileCollectionViaTblDocumentEvent, _
                0, New RelationPredicateBucket( _
             (RegisterHelper.TblFileFields.Status =  "Added") And _
             (RegisterHelper.TblDocumentEventFields.EventName = 
                                                        "Active")).PredicateExpression)

The resulting SQL generated for this is as follows:

SELECT DISTINCT 
[Register].[dbo].[tblFile].[FileID] AS [FileId], 
[Register].[dbo].[tblFile].[RevID] AS [RevId], 
[Register].[dbo].[tblFile].[FileName], 
[Register].[dbo].[tblFile].[Extension], 
[Register].[dbo].[tblFile].[Status] 
FROM (( [Register].[dbo].[tblDocument] [LPA__1]  
INNER JOIN [Register].[dbo].[tblDocumentEvent] 
    [LPA_T2]  
ON  [LPA__1].[DocID]=[LPA_T2].[DocID]) 
INNER JOIN [Register].[dbo].[tblFile]  
ON  
    [Register].[dbo].[tblFile].[FileID]=[LPA_T2].[FileID]) 
WHERE ( ( ( 
[LPA_T2].[DocID] = 4118)) 
AND 
( ( 
[Register].[dbo].[tblFile].[Status] = 'File Added' 
AND [Register].[dbo].[tblDocumentEvent].[EventName] = 'Active'
)))

The prefetch path elements are using hard-coded aliases to prevent entity name clashes. The TblDocumentEvent entity in the M:N prefetch path element is aliased as TblDocumentEvent_

(see the sourcecode of PrefetchPathTblFileCollectionViaTblDocumentEvent) This is necessary to work around an issue where an m:n relation exists between entities which are in the same inheritance hierarchy (have the same supertype).

So if I do:

PrefetchPath2 path = new PrefetchPath2((int)EntityType.CustomerEntity);
path.Add(CustomerEntity.PrefetchPathEmployees, 0, new PredicateExpression(OrderFields.ShipCountry == "Germany"));
EntityCollection<CustomerEntity> customers = new EntityCollection<CustomerEntity>();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(customers, null, path);
}

I get the same error. If I do:

PrefetchPath2 path = new PrefetchPath2((int)EntityType.CustomerEntity);
path.Add(CustomerEntity.PrefetchPathEmployees, 0, new PredicateExpression(OrderFields.ShipCountry.SetObjectAlias("Order_") == "Germany"));
EntityCollection<CustomerEntity> customers = new EntityCollection<CustomerEntity>();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(customers, null, path);
}

It works. This is also the same for filtering normal M:N fetches on the intermediate entity.

sbense wrote:

Otis wrote:

Looks like the alias bug with inheritance we had to fix with an API change in v2.5. Will look into it. (you use inheritance in any of the involved entities?)

Thanks for your response here. If by inheritance, you mean the two classes code generation option, then yes.

No I meant inheritance of entities. So you have 'Person' as supertype and 'Employee' and 'Customer' (bad example, but it illustrates what I mean ) as subtypes of Person and they have an m:n relation and you fetch customers and employees. 'Person' then has to be joined twice, and if you don't alias customer or employee, it has to cook up artificial aliases for Person, though if you refer to a field in person (through customer or employee) in your query, your field should get that alias as well.

To add to this - when using SQL Profiler - and a single predicate 3 SQL queries are generated, whereas when using two predicates in the predicate expression only two are generated and the timeout occurs.

I would like also to say a big thank you for all the support you and your team provide. This forum is invaluable and really gives me confidence in the product and it's future. As you are in touch with the issues people may be having or just helping newbies like myself to get over the basics, I believe you are offering far more than just a useful product. I wish you every deserved success!

I am now totally convinced of the benefits of LLBLGen and will be buying my license now that my evaluation period has come to an end.

Thanks for the kind words!

Otis wrote:

No I meant inheritance of entities. So you have 'Person' as supertype and 'Employee' and 'Customer' (bad example, but it illustrates what I mean ) as subtypes of Person and they have an m:n relation and you fetch customers and employees. 'Person' then has to be joined twice, and if you don't alias customer or employee, it has to cook up artificial aliases for Person, though if you refer to a field in person (through customer or employee) in your query, your field should get that alias as well.

I've got it now. Although I'm not using inheritance to the same extent as you have shown - ie. not explicitly creating subtypes of one supertype, the predicate expression seems to introduce the two joins that you describe, because I am filtering on the intermediate table of the m:n relationship and then on the one of the other tables on the 1:n site of the intermediate in a single prefetch path on the same prefetch m:n collection. Phew! Hope that makes sense. Sometimes it is really difficult to express these concepts clearly.

I'll try the example you have shown in your example against my m:n instance. Thanks again for a super product and support

Right I got this working now using the Alias you indicated.

In my scenario the generated code has the value "TblDocumentEvent_" as the alias for the intermediate table in the m:n relation

As I can reach this alias from the property documentEntity.PrefetchPathTblFileCollectionViaTblDocumentEvent.Relation.AliasFKSide I'll use that as a work around.

    Dim filterRelations As IRelationCollection = New RelationCollection()
    Dim predExpression As IPredicateExpression = New PredicateExpression()

    Dim alias As String 
    alias = documentEntity.PrefetchPathTblFileCollectionViaTblDocumentEvent.Relation.AliasFKSide

    predExpression.Add((RegisterHelper.TblFileFields.Status = "Active"))
    predExpression.Add ((TblDocumentEventFields.EventName.SetObjectAlias(alias) = "File Added"))
    ...

Could you please confirm that this what has been done in V2.5 - ie. when adding filters to the prefetch path, the field aliases will be taken automatically from the same property? Or will the developer have to make this association?

You've to specify the alias for the intermediate entity in the M:N relation. The filter is in another object, and has no notion of the relation. THis sounds odd, but the elements are separated to have a distributed query production system, so there's no one routine which generates a query, lots of separated objects together do that.

So the way I illustrate it in the example is also the way it should be done in v2.5. What I referred to as the bug we fixed in v2.5 is different: that's about auto-aliasing, so that's a different thing than you refer to

Thanks for clearing this up. Post closed. Over and out