I have the following db model:

MedicalProfessional Patient MedicalProfessionalAssignedPatients. (has 1 foriegn key into MedicalProfessionals and 1 foriegn key into Patient)

I am trying to load all the MedicalProfessionals and also preload the Patients assigned to the MedicalProfessional like so:

        MedicalProfessionalsEntityFactory factory = new MedicalProfessionalsEntityFactory();
        IPrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.MedicalProfessionalsEntity);

        prefetchPath.Add(MedicalProfessionalsEntity.PrefetchPathMedicalProfessionalMedicalProfessionalAssignedPatients);
        prefetchPath.Add(MedicalProfessionalsEntity.PrefetchPathPatientsCollectionViaMedicalProfessionalAssignedPatients);
        EntityCollection physicians = new EntityCollection(factory);
        using (DataAccessAdapter adapter = new DataAccessAdapter(StaticMethods.ConnectionString))
        {
            adapter.FetchEntityCollection(physicians, null, prefetchPath);
        }

When I run it I get this error: Relation at index 6 doesn't contain an entity already added to the FROM clause. Bad alias?

What am I doing Wrong?

Thanks,

Billy Jacobs

MedicalProfessionalsEntityFactory factory = new MedicalProfessionalsEntityFactory(); IPrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.MedicalProfessionalsEntity);

        prefetchPath.Add(MedicalProfessionalsEntity.PrefetchPathMedicalProfessionalMedicalProfessionalAssignedPatients);
        prefetchPath.Add(MedicalProfessionalsEntity.PrefetchPathPatientsCollectionViaMedicalProfessionalAssignedPatients);
        EntityCollection physicians = new EntityCollection(factory);
        using (DataAccessAdapter adapter = new DataAccessAdapter(StaticMethods.ConnectionString))
        {
            adapter.FetchEntityCollection(physicians, null, prefetchPath);
        }

It probably should be something like

MedicalProfessionalsEntityFactory factory = new MedicalProfessionalsEntityFactory();
            IPrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.MedicalProfessionalsEntity);
        
            var node = prefetchPath.Add(MedicalProfessionalsEntity.PrefetchPathMedicalProfessionalMedicalProfessionalAssignedPatients);
            node.Subpath.Add(MedicalProfessionalAssignedPatientsEntity.PrefetchPathPatientsCollection);

No. The collections are both at the same level. The are both collections hanging off of a MedicalProfessional. The only reason I was loading the PrefetchPathMedicalProfessionalMedicalProfessionalAssignedPatients was because I thought it might need it to inner join on.

Thanks for trying though.

Hi Billy. This should be sufficient:

IPrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.MedicalProfessionalsEntity);
prefetchPath.Add(MedicalProfessionalsEntity.PrefetchPathPatientsCollectionViaMedicalProfessionalAssignedPatients);

EntityCollection physicians = new EntityCollection(new MedicalProfessionalsEntityFactory());
using (DataAccessAdapter adapter = new DataAccessAdapter(StaticMethods.ConnectionString))
{
     adapter.FetchEntityCollection(physicians, null, prefetchPath);
}

Though your code posted above should work nice too as far as I can see. Please try my snippet and if it doesn't work please post the exception stackTrace, the LLBLGen pro runtime libraries version and the SQL generated. (ref).

Also, this is the difference between prefetch the 2-levels path and the 1-lever m:n path: http://llblgen.com/TinyForum/Messages.aspx?ThreadID=11138&StartAtMessage=0&#61967

I tried your code and this is the stack trace of the error.

at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryTextInternal(Int32& uniqueMarker, Boolean ansiJoins, String& nonAnsiWhereClause, String nonAnsiRootTableReference, String nonAnsiFieldSuffix) at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.ToQueryText(Int32& uniqueMarker) at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Boolean relationsSpecified, Boolean sortClausesSpecified) at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause) at SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.CreatePagingSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase.CreateSelectDQ(IEntityFieldCore[] selectList, IFieldPersistenceInfo[] fieldsPersistenceInfo, IDbConnection connectionToUse, IPredicate selectFilter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateSelectDQ(IEntityFields2 fieldsToFetch, IFieldPersistenceInfo[] persistenceInfoObjects, IPredicateExpression filter, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IRelationCollection relationsToWalk, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateQueryFromElements(IEntityFields2 fieldCollectionToFetch, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize, IFieldPersistenceInfo[]& persistenceInfo, IRetrievalQuery& selectQuery) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, Boolean allowDuplicates) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.MergeManyToMany(IPrefetchPathElement2 currentElement, IRelationPredicateBucket elementFilter, Int64 maxNumberOfItemsToReturn, IEntityCollection2 rootEntities) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath) 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.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, IPrefetchPath2 prefetchPath) at StevenDale.PediNotes.Client.SourceObjectListPanes.PatientListPane.LoadTestPatients() in C:\Development\StevenDale.PediNotes\StevenDale.PediNotes.Client\SourceObjectListPanes\PatientListPane.xaml.cs:line 89

David wrote:

if it doesn't work please post the exception stackTrace, the LLBLGen pro runtime libraries version and the SQL generated.

Version 2.6 The Sql Generated was (And I am getting this from SQL Profiler) NOTE: This runs fine in Management Studio. This query gets the Medical Professionals but does not get the associated Patients. Is there another way to get the generated SQL?

SELECT DISTINCT [StevenDale.PediNotes].[dbo].[OrganizationalUnits].[Id] AS [F18_0], [StevenDale.PediNotes].[dbo].[OrganizationalUnits].[__active] AS [F18_1], [StevenDale.PediNotes].[dbo].[OrganizationalUnits].[__lastupdateuser] AS [F18_2], [StevenDale.PediNotes].[dbo].[OrganizationalUnits].[__lastupdatetime] AS [F18_3], [StevenDale.PediNotes].[dbo].[OrganizationalUnits].[__timestamp] AS [F18_4], [StevenDale.PediNotes].[dbo].[Persons].[Id] AS [F20_5], [StevenDale.PediNotes].[dbo].[Persons].[FirstName] AS [F20_6], [StevenDale.PediNotes].[dbo].[Persons].[LastName] AS [F20_7], [StevenDale.PediNotes].[dbo].[Persons].[MiddleName] AS [F20_8], [StevenDale.PediNotes].[dbo].[Persons].[NickName] AS [F20_9], [StevenDale.PediNotes].[dbo].[Persons].[LegalName] AS [F20_10], [StevenDale.PediNotes].[dbo].[Persons].[MaidenName] AS [F20_11], [StevenDale.PediNotes].[dbo].[Persons].[Gender] AS [F20_12], [StevenDale.PediNotes].[dbo].[Persons].[Prefix] AS [F20_13], [StevenDale.PediNotes].[dbo].[Persons].[Suffix] AS [F20_14], [StevenDale.PediNotes].[dbo].[Persons].[BirthDate] AS [F20_15], [StevenDale.PediNotes].[dbo].[Persons].[SocialSecurityNumber] AS [F20_16], [StevenDale.PediNotes].[dbo].[Persons].[Race] AS [F20_17], [StevenDale.PediNotes].[dbo].[Persons].[MaritalStatus] AS [F20_18], [StevenDale.PediNotes].[dbo].[MedicalProfessionals].[Id] AS [F16_19] FROM (( [StevenDale.PediNotes].[dbo].[OrganizationalUnits] INNER JOIN [StevenDale.PediNotes].[dbo].[Persons] ON [StevenDale.PediNotes].[dbo].[OrganizationalUnits].[Id]=[StevenDale.PediNotes].[dbo].[Persons].[Id]) INNER JOIN [StevenDale.PediNotes].[dbo].[MedicalProfessionals] ON [StevenDale.PediNotes].[dbo].[Persons].[Id]=[StevenDale.PediNotes].[dbo].[MedicalProfessionals].[Id]) WHERE ( ( [StevenDale.PediNotes].[dbo].[MedicalProfessionals].[Id] IS NOT NULL))

bjacobs wrote:

Version 2.6

This is the way to retrieve the RuntimeLibrary version: The runtime library version is obtainable by rightclicking the SD.LLBLGen.Pro.ORMSupportClasses.NETxy.dll in windows explorer and then by selecting properties and the version tab. The version is then enlisted at the top as the fileversion. It has the typical format as 2.0.0.YYMMDD, or starting in 2007, the format 2.0.YY.MMDD

bjacobs wrote:

The Sql Generated was (And I am getting this from SQL Profiler) NOTE: This runs fine in Management Studio. This query gets the Medical Professionals but does not get the associated Patients. Is there another way to get the generated SQL?

You have to enable Tracing

It would be good update to the latest RuntimeLibraries build. If the problem persists, would be helpful if you prepare some tiny zip with the *.lgp project, some DDL script of the structure of your DB and some example data.

bjacobs wrote:

When I run it I get this error: Relation at index 6 doesn't contain an entity already added to the FROM clause. Bad alias?

Sorry to add another person to a crowded thread . . .

Is the code you posted the exact code you are running, or have you simplified it for the forum? When I've gotten the "Bad alias?" message, it's always turned out to be a problem with relations (RelationPredicateBucket). The error doesn't look related to prefetch paths to me.

Phil

1. Yes it is the exact code.
2. Version : 2.6.8.814

3. Trace SQL

   Query: SELECT DISTINCT [StevenDale.PediNotes].[dbo].[OrganizationalUnits].[Id] AS [F18_0], [StevenDale.PediNotes].[dbo].[OrganizationalUnits].[__active] AS [F18_1], [StevenDale.PediNotes].[dbo].[OrganizationalUnits].[__lastupdateuser] AS [F18_2], [StevenDale.PediNotes].[dbo].[OrganizationalUnits].[__lastupdatetime] AS [F18_3], [StevenDale.PediNotes].[dbo].[OrganizationalUnits].[__timestamp] AS [F18_4], [StevenDale.PediNotes].[dbo].[Persons].[Id] AS [F20_5], [StevenDale.PediNotes].[dbo].[Persons].[FirstName] AS [F20_6], [StevenDale.PediNotes].[dbo].[Persons].[LastName] AS [F20_7], [StevenDale.PediNotes].[dbo].[Persons].[MiddleName] AS [F20_8], [StevenDale.PediNotes].[dbo].[Persons].[NickName] AS [F20_9], [StevenDale.PediNotes].[dbo].[Persons].[LegalName] AS [F20_10], [StevenDale.PediNotes].[dbo].[Persons].[MaidenName] AS [F20_11], [StevenDale.PediNotes].[dbo].[Persons].[Gender] AS [F20_12], [StevenDale.PediNotes].[dbo].[Persons].[Prefix] AS [F20_13], [StevenDale.PediNotes].[dbo].[Persons].[Suffix] AS [F20_14], [StevenDale.PediNotes].[dbo].[Persons].[BirthDate] AS [F20_15], [StevenDale.PediNotes].[dbo].[Persons].[SocialSecurityNumber] AS [F20_16], [StevenDale.PediNotes].[dbo].[Persons].[Race] AS [F20_17], [StevenDale.PediNotes].[dbo].[Persons].[MaritalStatus] AS [F20_18], [StevenDale.PediNotes].[dbo].[Patients].[Id] AS [F19_19], [StevenDale.PediNotes].[dbo].[Patients].[MedicalRecordNumber] AS [F19_20], [StevenDale.PediNotes].[dbo].[Patients].[BirthWeight] AS [F19_21], [StevenDale.PediNotes].[dbo].[Patients].[BirthHeadCircumference] AS [F19_22], [StevenDale.PediNotes].[dbo].[Patients].[BirthLength] AS [F19_23], [StevenDale.PediNotes].[dbo].[Patients].[GestationalDays] AS [F19_24], [StevenDale.PediNotes].[dbo].[Patients].[Mother] AS [F19_25], [StevenDale.PediNotes].[dbo].[Patients].[Father] AS [F19_26] FROM (((((( [StevenDale.PediNotes].[dbo].[OrganizationalUnits] [LPA_M1] INNER JOIN [StevenDale.PediNotes].[dbo].[Persons] [LPA_M2] ON [LPA_M1].[Id]=[LPA_M2].[Id]) INNER JOIN [StevenDale.PediNotes].[dbo].[MedicalProfessionals] [LPA_M3] ON [LPA_M2].[Id]=[LPA_M3].[Id]) INNER JOIN [StevenDale.PediNotes].[dbo].[MedicalProfessional_AssignedPatients] [LPA_M4] ON [LPA_M3].[Id]=[LPA_M4].[MedicalProfessional]) INNER JOIN [StevenDale.PediNotes].[dbo].[Patients] ON [StevenDale.PediNotes].[dbo].[Patients].[Id]=[LPA_M4].[Patient]) INNER JOIN [StevenDale.PediNotes].[dbo].[Persons] ON [StevenDale.PediNotes].[dbo].[Persons].[Id]=[StevenDale.PediNotes].[dbo].[Patients].[Id]) INNER JOIN [StevenDale.PediNotes].[dbo].[OrganizationalUnits] ON [StevenDale.PediNotes].[dbo].[OrganizationalUnits].[Id]=[StevenDale.PediNotes].[dbo].[Persons].[Id]) WHERE ( [LPA_M4].[MedicalProfessional] IN (@MedicalProfessional1, @MedicalProfessional2, @MedicalProfessional3, @MedicalProfessional4) AND ( [StevenDale.PediNotes].[dbo].[Patients].[Id] IS NOT NULL)) Parameter: @MedicalProfessional1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 23c44a91-9223-4188-98f1-31aadb791069. Parameter: @MedicalProfessional2 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8b1bb237-b858-457a-9737-230fe9a7b1a0. Parameter: @MedicalProfessional3 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: b9936d7f-8900-4799-a31c-c0746c24de3c. Parameter: @MedicalProfessional4 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 49ff42d5-19dd-4835-85a9-9fd6e9d072fb.

Version : 2.6.8.814

that's an older runtime. We've had some changes in the runtime for specific edge case inheritance hierarchies. Please download the latest runtime lib from the customer area, make sure you're using that latest build and try again.

I downloaded and replaced the runtime dlls, rebuilt my solution including the llblgen generated projects that I am referencing and I get the same error.

Do I need to regenerate the llblgen generated projects?

NOTE: If I click on the SD.LLBLGEN.Pro.ORMSupportClasses.NET20 reference in my project the runtime version says v2.0.50727.

That's the .NET version. The build nr is shown when you right-click the dll in windows explorer -> properties -> version. (see also the guidelines thread in this forum)

As this is a very specific issue, and all our own tests work properly, we need some sort of repro case to make this pop up on our side as well, at least more information. Do you use any inheritance? Could you specify the table definitions and some sample data so we can rebuild the entities here and try to reproduce it?

(according to the queries, you do use inheritance, so it's important we need all the information you can give us to rebuild the classes here so we can run a reprocase which mimics your situation).

I am attaching a sample with everything necessary to reproduce the problem including a backup of the database.

I have a complete sample including the database but it is 820k and the max upload size is 512k.

Let me know what to do.

bjacobs wrote:

I have a complete sample including the database but it is 820k and the max upload size is 512k.

Let me know what to do.

Mail it to support AT llblgen DOT com and we'll look into it a.s.a.p. It will likely be friday (tomorrow) till we have a solid answer, as these debug sessions with relations being added twice etc. do require some time.

If you used custom templates etc. please let us know as well.

I just emailed the sample.

I did not do anything custom.

I also included the LLBLGen project file.

Thanks

Your code runs fine, however there are no MedicalProfessionals in the DB, so no additional data is read, simply because MedicalProfessionals is empty.

As you specify a factory of MedicalProfessionals, no SUPERtypes are fethed (i.e. no persons nor organisationalunits.)

If I add a MedicalProfessional however (by copying a Person's ID and filling in some random data) I do get an error, though not the error you ran into.

I.o.w.: 1) I'll investigate this error I ran into 2) The error you reported in the startpost isn't reproducable with the repro case you posted, nor is the problem you reported really clear, as the reprocase simply ran OK.

Next time, please provide a reprocase which indeed fails with the error / problem you reported here. Thanks.

Hmm. Your database is incomplete (restored on sqlserver 2005)

This query:

SELECT DISTINCT [StevenDale.PediNotes].[dbo].[OrganizationalUnits].[Id] AS [F18_0], [StevenDale.PediNotes].[dbo].[OrganizationalUnits].[__active__] AS [F18_1], 
    [StevenDale.PediNotes].[dbo].[OrganizationalUnits].[__lastupdateuser__] AS [F18_2], 
    [StevenDale.PediNotes].[dbo].[OrganizationalUnits].[__lastupdatetime__] AS [F18_3], 
    [StevenDale.PediNotes].[dbo].[OrganizationalUnits].[__timestamp__] AS [F18_4], 
    [StevenDale.PediNotes].[dbo].[Persons].[Id] AS [F20_5], 
    [StevenDale.PediNotes].[dbo].[Persons].[FirstName] AS [F20_6], 
    [StevenDale.PediNotes].[dbo].[Persons].[LastName] AS [F20_7], [StevenDale.PediNotes].[dbo].[Persons].[MiddleName] AS [F20_8], 
    [StevenDale.PediNotes].[dbo].[Persons].[NickName] AS [F20_9], [StevenDale.PediNotes].[dbo].[Persons].[LegalName] AS [F20_10], 
    [StevenDale.PediNotes].[dbo].[Persons].[MaidenName] AS [F20_11], [StevenDale.PediNotes].[dbo].[Persons].[Gender] AS [F20_12], 
    [StevenDale.PediNotes].[dbo].[Persons].[Prefix] AS [F20_13], [StevenDale.PediNotes].[dbo].[Persons].[Suffix] AS [F20_14], 
    [StevenDale.PediNotes].[dbo].[Persons].[BirthDate] AS [F20_15], 
    [StevenDale.PediNotes].[dbo].[Persons].[SocialSecurityNumber] AS [F20_16], [StevenDale.PediNotes].[dbo].[Persons].[Race] AS [F20_17], 
    [StevenDale.PediNotes].[dbo].[Persons].[MaritalStatus] AS [F20_18], [StevenDale.PediNotes].[dbo].[Patients].[Id] AS [F19_19], 
    [StevenDale.PediNotes].[dbo].[Patients].[MedicalRecordNumber] AS [F19_20], 
    [StevenDale.PediNotes].[dbo].[Patients].[BirthWeight] AS [F19_21], 
    [StevenDale.PediNotes].[dbo].[Patients].[BirthHeadCircumference] AS [F19_22], 
    [StevenDale.PediNotes].[dbo].[Patients].[BirthLength] AS [F19_23], 
    [StevenDale.PediNotes].[dbo].[Patients].[GestationalDays] AS [F19_24], [StevenDale.PediNotes].[dbo].[Patients].[Mother] AS [F19_25], 
    [StevenDale.PediNotes].[dbo].[Patients].[Father] AS [F19_26] 
FROM (((((( [StevenDale.PediNotes].[dbo].[OrganizationalUnits] [LPA_M1]  
        INNER JOIN [StevenDale.PediNotes].[dbo].[Persons] [LPA_M2]  ON  [LPA_M1].[Id]=[LPA_M2].[Id]) 
        INNER JOIN [StevenDale.PediNotes].[dbo].[MedicalProfessionals] [LPA_M3]  ON  [LPA_M2].[Id]=[LPA_M3].[Id]) 
        INNER JOIN [StevenDale.PediNotes].[dbo].[MedicalProfessional_AssignedPatients] [LPA_M4]  ON  [LPA_M3].[Id]=[LPA_M4].[MedicalProfessional]) 
        INNER JOIN [StevenDale.PediNotes].[dbo].[Patients]  ON  [StevenDale.PediNotes].[dbo].[Patients].[Id]=[LPA_M4].[Patient]) 
        INNER JOIN [StevenDale.PediNotes].[dbo].[Persons]  ON  [StevenDale.PediNotes].[dbo].[Persons].[Id]=[StevenDale.PediNotes].[dbo].[Patients].[Id]) 
        INNER JOIN [StevenDale.PediNotes].[dbo].[OrganizationalUnits]  
                ON  [StevenDale.PediNotes].[dbo].[OrganizationalUnits].[Id]=[StevenDale.PediNotes].[dbo].[Persons].[Id]) 
WHERE ( ( ( [LPA_M4].[MedicalProfessional] = @MedicalProfessional1)) AND ( [StevenDale.PediNotes].[dbo].[Patients].[Id] IS NOT NULL))

from:

IPrefetchPath2 prefetchPath = new PrefetchPath2(EntityType.MedicalProfessionalsEntity);
prefetchPath.Add(MedicalProfessionalsEntity.PrefetchPathPatientsCollectionViaMedicalProfessionalAssignedPatients);

//EntityCollection physicians = new EntityCollection(new MedicalProfessionalsEntityFactory());
EntityCollection<MedicalProfessionalsEntity> physicians = new EntityCollection<MedicalProfessionalsEntity>();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(physicians, null, prefetchPath);
}

After I've added a MedicalProfessional record with a random PK from Person (7ECC587A-8D10-4A57-AE9B-958B35BDC57F), I get the error: TestCase 'M:ReproduceLLBLGenError.Program.Main(System.String[])' failed: An exception was caught during the execution of a retrieval query: Invalid object name 'StevenDale.PediNotes.dbo.MedicalProfessional_AssignedPatients'.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

This is logical, as the table 'StevenDale.PediNotes.dbo.MedicalProfessional_AssignedPatients' doesn't exist!

Please provide proper reproducable material. Thanks.

Sorry about that. Not sure what happened. I must have zipped up an old backup. I just emailed the correct one.

With your new database I can reproduce it. It goes wrong with the intermediate query, which fetches PK values from both sides and which resultset is used to merge parent with child in the M:N based prefetch path. The query contains a relation which isn't connectable to any element already in the set.

The m:n relation in question is an edge case: both sides are in the same inheritance hierarchy, which is a tough case as aliasing issues pop up, but these should have been covered by the code.

This works though: EntityCollection<MedicalProfessionalsEntity> physicians = new EntityCollection<MedicalProfessionalsEntity>(); using (DataAccessAdapter adapter = new DataAccessAdapter()) { //adapter.FetchEntityCollection(physicians, null, prefetchPath); adapter.FetchEntityCollection(physicians, null);

adapter.FetchEntityCollection(physicians[0].PatientsCollectionViaMedicalProfessionalAssignedPatients,
            physicians[0].GetRelationInfoPatientsCollectionViaMedicalProfessionalAssignedPatients());

}

So the m:n relation fetching does work OK, it's the intermediate query which goes wrong. It will be monday till we have a fix for you.

Fixed. This is what you'd call a truly edge case . Though it was fixable with a generic solution, so it shouldn't pop up again. The attached dll should fix your problem.

I try to stay on the edge.

Thanks for fixing it.