Linq: query with three nested levels

Question

So I have three tables:

CREATE TABLE tblUser
(
    [pkUserID] [int] IDENTITY(1,1) NOT NULL,
    [userName] [varchar](150) NULL,
    [fkCompanyID] [int] NOT NULL
)

CREATE TABLE tblCompany
(
    [pkCompanyID] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](255) NULL
)

CREATE TABLE tblSystem
(
    [pkSystemID] [int] IDENTITY(1,1) NOT NULL,
    [systemName] [varchar](150) NULL,
    [fkCompanyID] [int] NULL
)

These are my data transfer objects:

public class SystemDTO
{
    public int pkSystemId { get; set; }
    public string Name { get; set; }
    public int? fkCompanyId { get; set; }
}

public class CompanyDTO
{
    public int pkCompanyId { get; set; }
    public string Name { get; set; }
    public IEnumerable<SystemDTO> Systems { get; set; }
}

public class UserDTO
{
    public int pkUserId { get; set; }
    public string Name { get; set; }
    public IEnumerable<CompanyDTO> Companies { get; set; }
}

This is the Linq query I am trying to do:

var result= (
        from user in db.tblUsers
        select new UserDTO()
        {
            pkUserId=user.pkUserID,
            Name=user.realName,
            Companies=
                (
                    from company in db.tblCompanies
                    where user.fkCompanyID==company.pkCompanyID
                    select new CompanyDTO()
                    {
                        pkCompanyId=company.pkCompanyID,
                        Name=company.name,
                        Systems=
                        (
                            from system in db.tblSystem
                            where system.fkCompanyId==company.pkCompanyId
                            select new SystemDTO()
                            {
                                pkSystemId=system.pkSystemID,
                                Name=system.systemName,
                                fkCompanyId=system.fkCompanyID
                            }
                        )
                    }
                )
        }
    ).ToList();

The problem with this query is that the most inner query

from system in db.tblSystem
where system.fkCompanyId==company.pkCompanyId
select new SystemDTO()
{
    pkSystemId=system.pkSystemID,
    Name=system.systemName,
    fkCompanyId=system.fkCompanyID
}

cause linq to translate the sql to one select per entity. I know that I can skip the select and loop the result and set the property. Like this:

var lsSystem= db.tblSystem.Select (s =>new SystemDTO(){pkSystemId=s.pkSystemID,Name=s.systemName,fkCompanyId=s.fkCompanyID}).ToList();
foreach (var user in result)
    {
        foreach (var company in user.Companies)
        {
            company.Systems=lsSystem.Where (a =>a.fkCompanyId==company.pkCompanyId).ToList();
        }   
    }

This will cause linq to do two select and not one per entity. So now to my questions. Is there any another way of doing this? Can I populate the inner collection in another way?

Any suggesting will be appreciated

EDIT
A suggesting was to use loadoption. I can't find a loadoption between system and company. But I can include the loadoption between. Company and user like this:

var option=new DataLoadOptions();
option.LoadWith<tblCompany>(a=>a.fkCompanytblUsers);
db.LoadOptions=option;

But this has no effect on the query it is still translated to many selects

EDIT2

As said in the answers comments the load options do not apply for this kinda linq query.

Answer 1

I figure it out myself. The best way what i can see is to do it like this (but again if anyone else have a better suggestion please add them):

var lsSystem= db.tblSystem.Select (s =>new SystemDTO()
                                        {
                                            pkSystemId=s.pkSystemID,
                                            Name=s.systemName,
                                            fkCompanyId=s.fkCompanyID
                                        }
                                ).ToLookup (s =>s.fkCompanyId);

And then use the lsSystem in the linq query like this:

var result= (
        from user in db.tblUsers
        select new UserDTO()
        {
            pkUserId=user.pkUserID,
            Name=user.realName,
            Companies=
                (
                    from company in db.tblCompanies
                    where user.fkCompanyID==company.pkCompanyID
                    select new CompanyDTO()
                    {
                        pkCompanyId=company.pkCompanyID,
                        Name=company.name,
                        Systems=lsSystem[company.pkCompanyID]
                    }
                )
        }
    ).ToList();

This will result in two select statements one for system and one for users to companies

Answer 2

Ok, here is a proposal that you an use to get everything in a single query. I'll simplify the data model for demonstration purposes:

select *
from ParentTable
join ChildLevel1 on ...
join ChildLevel2 on ...

That query will give you all three tree levels at once. It will be quite efficient. But the data will be redundant. You need to do some client processing to make it usable again:

var parents = from x in queryResults
group x by new { /* all parent columns here */ }) into g
select new Parent()
{
 ParentData = g.Key,
 Children1 = from x in g
             group x by new { /* all ChildLevel1 columns here */ }) into g
             select new Child1()
             {
              Child1Data = g.Key,
              Children2 = ... //repeat
             }
}

You need to remove the redundancies by doing groupings. In other words: The query has denormalized the data and we need to normalize it again.

This approach is very cumbersome but fast.

Answer 3

Have you looked into the LoadOptions and more particular LoadWith.

This will stop Linq2sql from lazy loading and will do eager loading.

Simple example here: http://davidhayden.com/blog/dave/archive/2007/08/05/LINQToSQLLazyLoadingPropertiesSpecifyingPreFetchWhenNeededPerformance.aspx