Is there any way to rewrite this LINQ query so that it only does one SQL query?

Question

Hi,

I have a LINQ query which is fetching a list of nested objects.

from c in ClientsRepository.LoadAll()
orderby c.Name
select new ComboBoxOptionGroup
    {
    Text = c.Name,
    Options = from p in c.Projects
              orderby p.Name
              select new ComboBoxOption
              {
                  Text = p.Name,
                  Value = p.ID.ToString()
              }
    }

Unfortunately this LINQ query results in num(clients)+1 SQL queries. Is there any elegant way of rewriting this so that it only results in one SQL query?

My idea would be to fetch all projects ordered by clients and to do the rest of the work in two nested foreach loops, but that seems to defy the elegance that was intended when LINQ was designed. Are there any better options?

Answer 1

You propably search for "join" operator: http://www.hookedonlinq.com/JoinOperator.ashx

Answer 2

If ClientsRepository.LoadAll() is a wrapper around a query, that actually goes to the database and retrieves the data (does not return IQueryable<T>) then you are stuck. If it returns a part of a query (does return IQueryable<T>) then it should be possible.

However without knowing where the data (or object) context is and your model it is hard.

Normally I would expect to do something like:

from client in dataContext.Clients
join p in dataContext.Projects on p.ClientId equals c.Id
  into projects
orderby client.Name
select new ComboBoxOptionGroup {
  Text = client.Name,
  Option = from p in projects
           orderby p.Name
           select new ComboBoxOption {
             Text = p.Name,
             Value = p.ID.ToString()
           }
}

Answer 3

Another possible alternative would be to use DataLoadOptions.

DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Client>(c => c.Projects);
context.DeferredLoadingEnabled = false; // You may not need/want this line
context.LoadOptions = dlo;

I haven't tested this way of doing it, but you'll hopefully be able to leave your query alone and reduce the number of necessary queries.