Linq2Sql - How do you lazy load nested lists?

Question

How do you lazy load nested lists without actually executing the query? Using a very basic example, say I have:

class CityBlock {
     IList<Building> BuildingsOnBlock;
     Person BlockOwner;
}

class Building {
     IList<Floor> FloorsInBuilding;
}

class Floor {
     IList<Cubicle> EmployeeCubicles;
}

Class Cubicle {
     System.Guid CubicleID;
     Person CubicleOccupant;
}

And then in my repository layer, I have the following method:

GetCityBlocks()

And then in the Service layer I'll have GetCityBlocksByOwner, where I utilize an extension method to get the city blocks owned by a specific person, say we just want Guido's blocks:

GetCityBlocks().ForOwner("Guido")

If we do a .ToList() in the repository it's going to execute the queries - that's going to be ridiculous since we don't know who's blocks we're getting at that level. So the question is, how do we do this efficiently?

Let's assume there are 50000 block owners, and some 1000000 city blocks, loading all of them is not an option. Using IQueryables won't work due to the nesting (without extreme hacking, at least that I'm aware of). Also, if I try to use something like Rob Conery's LazyList, then we essentially have a leak from our DAL into our domain models, which could be very very bad in the future.

So how do I go about doing this correctly?

• Is it a matter of determining the correct context? If so, would we do this in the Repository Layer, or the Service layer?
• Do I half meld together the Service layer and my Repository layer to get very specific service methods?
• Or am I missing something entirely? (still relatively new to the Linq2Sql thing, which is being phased out anyways so...)

Edit: In the repository pattern, we're currently mapping to our domain objects, so it would look something like this:

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

For this to work, we'd have to make the buildings get a .ToList(), unless we make that actual field in the CityBlock object an IQueryable - which doesn't seem right, because it seems as if too much power would be granted to anyone who accesses the CityBlock.BuildingsOnBlock field. Is this mapping to our domain objects something we should maybe do up in the service layer?

Answer 1

You do it by returning IQueryables instead of ILists.

ToList() causes the queries to execute immediately, because a conversion must be performed from IQueryable to IList.

As long as you are returning IQueryables lazy loading should defer execution until the data is actually needed, i.e. when ToList() is called.

I can't find a reference at the moment, but it is my understanding that, if you do it this way, linq to sql has the opportunity to optimize the SQL it sends to the server. In other words, it will ultimately read these records:

GetCityBlocks().ForOwner("Guido")

rather than these records:

GetCityBlocks()

Answer 2

You could try a different approach in mapping your domain objects to make it work. The problem is that no matter what you do (unless you change your lists to IQueryables in your domain objects) you will end up ToList()ing while you're mapping.

The other way it to let linq2Sql do the mapping to your POCOs by creating a custom datacontext and not using the designer for the mapping :), that way you keep your domain model clean and let linq2Sql populate the dependencies at the correct time. Note that going into this route has its own problems, but it can be done.

Here's a link to get you started on this route

Achieving POCO s in Linq to SQL