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.

Solution 1

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.

Solution 2

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

Comments

[Arion]

Yes that is also a way of doing it. But the database I am currently working in is quite old and are missing the relations between the target tables. Some loadoption i and find I updated the question with that.

[Pleun]

You could select a flat result set first, move that into memory for exmaple a List<> and use that list as input for creating your DTO's. That should give you one select with many outer joins.

[Arion]

Can you add a example for that?

[Magnus]

@Pleun I don't see any lazy loading in that query.

[usr]

This will not work. It will just eagerly send the N child-queries instead of lazily.

[usr]

This is the correct solution, even if it is a little inconvenient. SQL has problems returning trees.

[Arion]

Yeah I think so to. But I think i will leave it until tomorrow. Maybe someone comes around and find something that we have not thought about

[Arion]

If I have between 300-1000 rows in each table is it effective to do the group by:s on the client side? Or is the other suggestions more efficient?

[usr]

The grouping happens on the client side but your server-side code will be maximally efficient. It will be much more efficient then doing tons of roundtrips. The client has no problem at all grouping a few 1000 objects. That is really nothing.