Optimizing a LINQ to SQL query.

Question

I have a query that looks like this:

public IList<Post> FetchLatestOrders(int pageIndex, int recordCount)
{
 DatabaseDataContext db = new DatabaseDataContext();
 return (from o in db.Orders
   orderby o.CreatedDate descending
   select o)
   .Skip(pageIndex * recordCount)
   .Take(recordCount)
   .ToList();
}

I need to print the information of the order and the user who created it:

foreach (var o in FetchLatestOrders(0, 10))
{
 Console.WriteLine("{0} {1}", o.Code, o.Customer.Name);
}

This produces a SQL query to bring the orders and one query for each order to bring the customer. Is it possible to optimize the query so that it brings the orders and it's customer in one SQL query?

Thanks

UDPATE: By suggestion of sirrocco I changed the query like this and it works. Only one select query is generated:

public IList<Post> FetchLatestOrders(int pageIndex, int recordCount)
{
 var options = new DataLoadOptions();
 options.LoadWith<Post>(o => o.Customer);
 using (var db = new DatabaseDataContext())
 {
  db.LoadOptions = options;
  return (from o in db.Orders
    orderby o.CreatedDate descending
    select o)
    .Skip(pageIndex * recordCount)
    .Take(recordCount)
    .ToList();
 }
}

Thanks sirrocco.

Answer 1

you might want to look into using compiled queries

have a look at http://www.3devs.com/?p=3

Answer 2

Something else you can do is EagerLoading. In Linq2SQL you can use LoadOptions : More on LoadOptions One VERY weird thing about L2S is that you can set LoadOptions only before the first query is sent to the Database.