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'm 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 innermost query

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

forces linq to translate sql to one select per object. I know that I can skip the selection and encode 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 make two choices, not a single object. So now to my questions. Is there any other way to do this? Can the internal collection be filled out differently?

Any suggestions would be appreciated.

EDIT
It was supposed to use loading. I can not find a download between the system and the company. But I can enable loadoption between. Company and user:

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

But this does not affect the request, it still translates into many options

EDIT2

As stated in the answers to the answers, the boot options are not used for this kind of linq query.

Answer 1

Alright, here is the suggestion you use to get everything in one request. I will simplify the data model for demo purposes:

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

This query will give you three levels of the tree at once. It will be quite effective. But the data will be redundant. You need to do some client processing in order to use it 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 redundancy by doing groupings. In other words: the query denormalized the data, and we need to normalize it again.

This approach is very cumbersome, but quick.

Answer 2

I realized that myself. The best way to see what I see is to do it (but then again, if anyone has a better suggestion, 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 lsSystem in the linq query as follows:

 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 for the system and one for users for companies.

Answer 3

You looked at LoadOptions and more specifically LoadWith .

This will stop Linq2sql from lazy loading and start loading.

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