Using 2.6 Final / 2.6.9.1202 on Postgres
(Please inform me if there is better terminology to express what I'm doing. I'm minimally-self-taught on database stuff)
I have a table of results, which has an n-ary tree of results it has replaced, either due to merging two similar results or keeping a modification history of the single result. The tree is maintained via a join table with FK's to the original and the replacing results. Relevant DDL below.
In the LLBLGen designer, for ReportEntry I've mapped the relation DeletedLinkedToSelf as ReportEntry.ReportEntryId (1:n) BucketJoin.ReportEntryId. For BucketJoin, I've mapped the relation DeletedIntermediate as BucketJoin.DeletedId (m:1) ReportEntry.ReportEntryId.
My query uses a prefetch path like so:
IPrefetchPath2 path = new PrefetchPath2((int)EntityType.ReportEntryEntity);
path.Add(ReportEntryEntity.PrefetchPathDeletedLinkedToSelf).SubPath.Add(BucketJoinEntity.PrefetchPathDeletedIntermediate);
with a linq query.
And finally we get to my problem. A.DeletedLinkedToSelf contains B and C, and B.DeletedLinkedToSelf contains D. But A.DeletedLinkedToSelf[0].DeletedLinkedToSelf is empty. I'd thought that since B is uniquely identified by its PK that A.DeletedLinkedToSelf[0] and B would be the same object, but that does not appear to be the case.
I'm displaying these in a treeview with a recursive constructor. When I display A as my root, I see children B and C. When I display B, I see child D.
I'm hoping there's some option I can activate to help this situation, so I don't have to do an external lookup for the directly queried entities. Thanks for your help!
CREATE TABLE report_entries (
report_entry_id SERIAL PRIMARY KEY,
...
);
CREATE TABLE bucket_joins (
deleted_id integer NOT NULL,
report_entry_id integer NOT NULL,
join_reason integer NOT NULL,
PRIMARY KEY(deleted_id, report_entry_id)
);
ALTER TABLE bucket_joins ADD CONSTRAINT fk_report_join
FOREIGN KEY (report_entry_id) REFERENCES report_entries (report_entry_id) ON DELETE CASCADE
;
ALTER TABLE bucket_joins ADD CONSTRAINT fk_deleted_join
FOREIGN KEY (deleted_id) REFERENCES report_entries (report_entry_id) ON DELETE CASCADE
;
