Saturday, October 31, 2009

The need of DataLoadOptions

=======
Problem
=======
If you run the following C# code on Northwind database, there will be one query sent to db to get the Customers needed and also one seperate query for each customer to get the individual order count:
-------------------------
var query = from c in db.Customers
where c.CustomerID.StartsWith("A")
select c;

foreach (var c in query){ Console.WriteLine( c.ContactName + " " + c.Orders.Count);}
-------------------------
It can be a lot of queries if customers are large.
============================================================
Witout using DataLoadOptions, we can do the same using single query as below:
=======================================================
------------------------------
var query = from c in db.Customers
where c.CustomerID.StartsWith("A")
select new {name=c.ContactName, count=c.Orders.Count};

foreach (var c in query){ Console.WriteLine( c.name + " " + c.count);}
------------------------------
The above code makes difference because we added the query needed to get the count into the same query for getting Customer. LINQ to SQL is smart enough to parse it into a single query, which has the same effect as seting the DataLoadOptions.
=============================================
Using the following code, you can get Customers with all their Orders in one query just like by setting DataLoadOptions to Orders:
============================================
---------------------------------------
var query = from c in db.Customers
where c.CustomerID.StartsWith("A")
select new {name=c.ContactName, count=c.Orders};
--------------------------------------
Then you can access related Orders collection in you code by looping through the anonimous objects generated.

No comments:

Post a Comment