Eager Loading Children for a LINQ stored procedure call

Question

I have a special stored procedure that returns a List of Distributors from my database. I then iterate through the loop of Distributors and output the results to a web page. However, I need to load the State and the Country for each Distributor. I would rather do this one time, before the loop so that the page is faster. This is my code:

List<Distributor> distQuery =  connection.spDistFindLocal(Lat, Long).ToList<Distributor>();

I know I can do distributor.State.Load(); on each element in my List but I think that is a poor choice. What other options do I have?

Answer 1

Use a projection:

List<DistributorViewModel> distQuery =  
    (from d in connection.spDistFindLocal(Lat, Long)
     select new DistributorViewModel
     {
         Name = d.Name,
         State = d.State.Name,
         Country = d.Country.Name
     })ToList();

Not only does this load all of the data in one query, but it also omits loading the properties you don't care about for this view.