Forcing LINQ to SQL to make one single call for all child rows

Question

Let say I have a method (example taken from another post):

public IQueryable<CityBlock> GetCityBlocks(){
    var results = from o in db.city_blocks
                  let buildings = GetBuildingsOnBlock(o.block_id) //returns Iqueryable
                  select new CityBlock {
                      BuildingsOnBlock = buildings,
                      BlockOwner = o.block_owner
                  };
    return results;
}

In the calling method I add Skip() and Take() methods plus some filtering and then do a ToList().

The trouble is that I am getting dozens of database calls - one for all the city blocks and then a separate one for each building.

Is there a way that I can refactor this code to just make two calls: one for the city blocks and one for all the buildings

Should add that I have tried eager loading using:

var dataLoadOptions = new DataLoadOptions();
dataLoadOptions.LoadWith<city_blocks>(x => x.buildings);            
db.LoadOptions = dataLoadOptions;

but does not seem to make a difference.

Answer 1

Are you familiar with the L2S concepts of Lazy Loading and Eager Loading? Lazy loading does what you are experiencing. It will go to the database each time a request is made for child records. Eager loading gets them all up front. You can a Google search on Linq and Lazy or Eager Loading to learn more about this.

Answer 2

Perhaps you could use a Join and Group By?

public IQueryable<CityBlock> GetCityBlocks(){
    var results = from o in db.city_blocks
                  join b in db.buildings on o.block_id equals b.block_id
                  group by o into g
                  select new CityBlock {
                      BuildingsOnBlock = g.buildings.DefaultIfEmpty(),
                      BlockOwner = g.Key.block_owner
                  };
    return results;
}

Answer 3

Eager load in Linq to SQL is flawed, it is a lazy load performed upfront.. that is, one query per collection per entity. So you get the same ripple load effects as lazy load, but you get them upfront.

Entity Framework 4 supports true eager load, it can issue a single query that returns all the results.

Answer 4

seems to be a limitation of Linq to sql:

http://codebetter.com/blogs/david.hayden/archive/2007/08/06/linq-to-sql-query-tuning-appears-to-break-down-in-more-advanced-scenarios.aspx