Self-referencing tables in Linq2Sql
- by J-Man
Hi,
I've seen a lot of questions on self-referencing tables in Linq2Sql and how to eagerly load all child records for a particular root object. I've implemented a temporary solution by accessing all underlying properties, but you can see that this doesn't do the performance any good.
The thing is though, that all records are correlated with each-other using a correlation GUID. Example below:
RootElement
- Id: 1
- ParentId: null
- CorrelationId: 4D68E512-4B55-44f4-BA5A-174B630A03DD
ChildElement1
- Id: 2
- ParentId: 1
- CorrelationId: 4D68E512-4B55-44f4-BA5A-174B630A03DD
ChildElement2
- Id: 3
- ParentId: 2
- CorrelationId: 4D68E512-4B55-44f4-BA5A-174B630A03DD
ChildElement1
- Id: 4
- ParentId: 2
- CorrelationId: 4D68E512-4B55-44f4-BA5A-174B630A03DD
In my case, I do have access to the correlationId, so I can retrieve all of my records by performing the following query:
from element in db.Elements
where element.CorrelationId == '4D68E512-4B55-44f4-BA5A-174B630A03DD'
select element;
But, of course, I want these elements associated with each other by executing this query:
from element in db.Elements
where element.CorrelationId == '4D68E512-4B55-44f4-BA5A-174B630A03DD' && element.ParentId == null
select element;
My question is: is it possible to combine the results the first query as some sort of 'caching mechanism' for the query where I get the root element?
Thanks for the input.
J.
