Tips and Tricks: Forcing LINQ to SQL to load Child Objects without deferred execution and why

Today I would like to discuss how to force LINQ to SQL to load child objects, as well as shape its load, without deferred execution. Before we begin, let’s first discuss why that is even necessary.

What does Deferred execution give us:

Composable queries and deferred execution work together to make LINQ a unusual rich query language. If you properly understand these features of LINQ you will be able to write less code that executes faster, in order to accomplish more.

What problems can it create:

var query = from customer in db.Customers
where customer.City == "Paris"
select customer;

foreach (var Customer in query) <<>

{

Console.WriteLine(Customer.CompanyName);
foreach (var order in Customer.Orders) <<>

{

Console.WriteLine(order.OrderID);

}

}

As you can see above, you are sending SQL across the wire each time you loop through the Customer object. This is not an ideal way to handle this load if you already know you will need to pre-load all of the child objects ahead of time.


Forcing it to load up the child objects if you know you are going to need them:

//Create a new Data Context to load data objects

NorthwindDataContext nwind = new NorthwindDataContext();

//Create a DataLoad Options object to tell the Datacontext

// how to load this object, if it

// loads the specified object (through the generic part)

DataLoadOptions options = new DataLoadOptions();

options.LoadWith<Product>(p => p.Category);

options.LoadWith<Product>(p => p.Order_Details);

options.LoadWith<Order_Detail>(od => od.Order);

//Set the load options for this dataContext

nwind.LoadOptions = options;

//This products object has preloaded the Parent object Category

// and Child object OrderDetails,

// as well as each OrderDetails parent Order object

IEnumerable<Product> products = nwind.Products.ToList<Product>();

Changing what data actually gets loaded for the child objects for the Customer’s Order:

//Create a DataLoad Options object to tell the Datacontext

// how to load this object, if it

// loads the specified object (through the generic part)

DataLoadOptions newOptions = new DataLoadOptions();

//This will tell the Datacontext to load the Order Object

// for the Customer (when it loads through deferred or not)

// to load only those Orders with OrderID < style="">

// in descending order by OrderDate

newOptions.AssociateWith<Customer>(c => from o in c.Orders

where o.OrderID < 10700

orderby o.OrderDate descending

select o);

As I hope you can see, LINQ to SQL gives you a lot of flexibility on how to load and when to load. I really do enjoy working with LINQ and all its pieces. I still have a lot to learn with it, but the more I use it the more I appreciate it and become reliant on it.