Hello Folks,
Today i will tell tou how to optimize the LINQ query with
DataLoadOptions. With a very simple example we will see how easily we
can reduce the number of database call.
Here we go...
I have a DBML file that has Customer table and Order table that are mapped by CustomerID.
Now to find out all the Order for a given Customer there exists two ways to write the LINQ query.
Normal non optimized way:
var result = from c indb.Customers
wherec.CustomerID == "BOLID"
select c;
foreach (var item in result)
{
foreach(var ord initem.Orders)
{
Console.WriteLine(ord.OrderID);
}
}
In this case two query are fired first to fetch all thecustomer for given ID and then a seperate query is fired to fetch all the orderfor given CustomerID.
Optimized Way:
DataLoadOptions dataload = new DataLoadOptions();
dataload.LoadWith<Customer>(c => c.Orders);
db.LoadOptions = dataload;
varresult = from c indb.Customers
wherec.CustomerID == "BOLID"
selectc;
db.Log = Console.Out;
foreach (var item in result)
{
foreach(var ord initem.Orders)
{
Console.WriteLine(ord.OrderID);
}
}
In this case what extra that is done is that we loaded the Order table in
the starting only. This is called the eager loading.
As clear from the query generated this result is just onecall to database.
This a very simple scenario where you can use the DataLoadOptionsto optimize your
query.
Happy Coding J
Related Posts:
LINQ to
SQL Best Practice
Using let Keyword in LINQ
Error: Subquery returned more than 1 value
Maintain Transaction in LINQ to SQL
Error: String must be exactly one character long
Perform Cross Database LINQ join Operation