In our database with have a table: PolicyProspect, which has the primary key PolicyProspectGuid and also has a nullable foreign key RenewalOfPolicyProspectGuid.
When we get a new policy, the renewal is left null, but when a policy is renewed, the link from the new policy to the old is documented via that foreign key. This has never caused any issues until we tried to create a report that uses the structure:
//These would be policies that were potentially renewed
EntityCollection<PolicyProspectEntity> policies = new EntityCollection<ORM.EntityClasses.PolicyProspectEntity>();
//Starting from our new policies...
PrefetchPath2 path = new PrefetchPath2(EntityType.PolicyProspectEntity);
//get the policies they were renewed from (these are also PolicyProspectEntities)
var oldPolicy = path.Add(PolicyProspectEntity.PrefetchPathRenewalOfPolicyProspect).SubPath;
//And then get the carrier submission.
var oldSubmissions = oldPolicy.Add(PolicyProspectEntity.PrefetchPathCarrierSubmission).SubPath;
QueryParameters param = new QueryParameters();
param.PrefetchPathToUse = path;
param.CollectionToFetch = policies;
DataAccessAdapter adapter = new DataAccessAdapter();
await adapter.FetchEntityCollectionAsync(param, CancellationToken.None);
The expected behavior would be for the items in the RenewalOfPolicyProspect collection to have their CarrierSubmission data prefetched. However, this doesn't happen when a policy is both in the base data set and in the child data set. My guess is that as the entity is already loaded during the execution of the base, the existing record is reused in the Renewal set and thus does not execute the prefetch path (which is only specified for the child data set).
We have worked around this via a view (and this is from a huge database has been using LLBLGen since version 2, so this isn't an issue that comes up often), so this is more curiosity as to what the best approach to queries that would expect to see the same record in multiple places in the prefetch graph.
