Eager loading child lists in LINQ to SQL

Question

I am trying to optimize a LINQ to SQL request so that it results in a single SQL statement. At the moment i have something like this:

EDIT : removed the "where" filter for clarity - also "Id" is a primary key in the databsse for both object types

hotel = (from h in db.Hotels

         let rooms = (from r in db.HotelRooms
                      where r.HotelId == h.Id
                      select new HotelRoom
                      {
                          Id = r.Id,
                          Code = r.Code,
                          Category = r.Category
                      })

         select new Hotel
         {
             Id = h.Id,
             Name = h.Name,
             Rooms = rooms.ToList()
         }).FirstOrDefault();

Where the Hotel business level class is defined as:

public class Hotel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<HotelRoom> Rooms { get; set; }
}

Wherein the query is using initializers to create business objects on the fly from the DataContext entities as the query is executed.

The problem is that obviously the .ToList() on the Rooms collection causes the Hotel.Rooms collection to be populated via a second query.

Is there a way to populate the Hotel.Rooms property with all the HotelRooms, without it generating additional queries?

Answer 1

  //grab the rows from the database in direct fashion.
var joinedRows = 
(
  from h in db.Hotels
  where h.Name == name
  from hr in h.HotelRooms
  select new {HotelRow = h, HotelRoomRow = hr}
)
.ToList();

  //shape the data locally
List<Hotel> result =
  from row in joinedRows
  group row.HotelRoomRow by row.HotelRow into g
  let h = g.Key
  select new Hotel()
  {
    Id = h.Id,
    Name = h.Name,
    Rooms = (
      from r in g
      select new HotelRoom()
      {
        Id = r.Id,
        Code = r.Code,
        Category = r.Category
      }).ToList()
  };