Hi,
I have got the follwing hierarchy structure for a Project entity:
Project_Id (int, PK)
Project_ParentProject_Id (int, nullable, references Project_Id)
Project_Name (string)
SubProjects (which is a list of children Project entities)
I added a "Parent Project" and a "Child Project" and saved them. All OK so far.
Now I want to load a Project with it's "first level" children.
This is the code:
Project parent = (from
p in uow.Projects
where
p.Name == "Parent Project"
select
p
).SingleOrDefault();
int count = parent.SubProjects.Count;
This how LightSpeed queries the database:
SELECT
Projects.*
FROM
(
SELECT
Projects.Project_Id AS [Projects.Project_Id],
Projects.LockVersion AS [Projects.LockVersion],
Projects.Project_Name AS [Projects.Project_Name],
Projects.Project_ParentProject_Id AS [Projects.Project_ParentProject_Id],
ROW_NUMBER() OVER(ORDER BY Projects.Project_Id) as RowNumber
FROM
Projects
WHERE
Projects.Project_Name = 'Parent Project'
)
Projects
WHERE
RowNumber <= 2
--> Time: 2 ms
SELECT
Projects.Project_Id AS [Projects.Project_Id],
Projects.LockVersion AS [Projects.LockVersion],
Projects.Project_Name AS [Projects.Project_Name],
Projects.Project_ParentProject_Id AS [Projects.Project_ParentProject_Id]
FROM
Projects
WHERE
Projects.Project_ParentProject_Id = 1
--> Time: 2 ms
Two queries, all ok. However, the "WHERE RowNumber <= 2" looks a bit strange.
I now want to edger load the children. I set the Property "Edger Load Collection" to true and run the same code.
That is how LigthSpeed queries the database:
SELECT
Projects.*
FROM
(
SELECT
Projects.Project_Id AS [Projects.Project_Id],
Projects.LockVersion AS [Projects.LockVersion],
Projects.Project_Name AS [Projects.Project_Name],
Projects.Project_ParentProject_Id AS [Projects.Project_ParentProject_Id],
ROW_NUMBER() OVER(ORDER BY Projects.Project_Id) as RowNumber
FROM
Projects
WHERE
Projects.Project_Name = 'Parent Project'
)
Projects
WHERE
RowNumber <= 2
The SubProjects collection is empty. The SQL looks a bit strange for me.
What do I need to do to eager loading the children?
Kind regards,
Sörnt
