Use LoadDataOptions class in LINQ for immediate filtering and loading of relational data

LINQ is one of the nice feature every .NET developer would love to appreciate Microsoft for. I do not wish to explain much about linq, I am getting in to topic with a belief that you will have minimum understanding of LINQ and how it helps in data access from .NET Framework 3.5 onwards.

Consider the following example on linQ using Northwind Database.

[code lang=”c-sharp”]

NorthwindDataContext db = new NorthwindDataContext();

//Normal linq query fetch only ORDER Information
var orderQuery = from order in db.Orders
select order;

List<Order> orderList = orderQuery.ToList();
[/code]

LINQ will not load/fetch all relative information to Order class Since it is using Lazy loading. if we consider the above example it will return only Order information.

When you query for an object, you actually retrieve only the object you requested. The related objects are not automatically fetched at the same time. (For more information, see Querying Across Relationships (LINQ to SQL).)

The DataLoadOptions class  in System.Data.Linq namespace provides two methods to achieve immediate loading of specified related data. The LoadWith method allows for immediate loading of data related to the main target. The AssociateWith method allows for filtering related objects.

So I modified my above query to use DataLoadOptions to load associated Customer,Employee,Order_Details,Shipper information also within each Order object.

Now the modified code looks like

[code lang=”c-sharp”]

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;

namespace LINQSampleApp
{
class Program
{
static void Main(string[] args)
{

NorthwindDataContext db = new NorthwindDataContext();

//Normal linq query fetch only ORDER Information
var orderQuery = from order in db.Orders
select order;

List<Order> orderList = orderQuery.ToList();

//LINQ doesn’t load/fetch all relational information to Order class.
//Since it’s using Lazy loading, if we consider the above example it will return only
//Order information.

/*
* Consider the Scenario:
* —————————
* Suppose I would have to fetch all Order information and order
* details information and customer information within the ORDER entity
* because Order_Details, Customers are child or relational data for the order
* We could do this with a single select query like above.
* But we have to specify the explicit information about relational data to be loaded/retrieved
* within DataLoadOptions class. DataLoadOptions we configures the relational data mappins and
* conditions for filling child entities.
*
* Look in to the following example.
* */

db.Dispose();
//disposing previous data context. Because it will not allow us to specify LoadOptions after fetching information.
//Keep in mind, create loadoptions and assign it before the execution of main query.

NorthwindDataContext db1 = new NorthwindDataContext();

//Creating an instance of DataLoadOptions class
DataLoadOptions loadOptions = new DataLoadOptions();

//Specifying the mappings using Load() method to load relational data to the parent entity

//Specifying to load associated Customer information to each instance of Order class
loadOptions.LoadWith<Order>( c => c.Customer);

//Specifying to load associated Employee information to each instance of Order class
loadOptions.LoadWith<Order>(c => c.Employee);

//Specifying to load associated Order_Details information to each instance of Order class
loadOptions.LoadWith<Order>(c => c.Order_Details);

//Specifying to load associated Shipper_Details information to each instance of Order class
loadOptions.LoadWith<Order>(c => c.Shipper);

//Assign the loadOptions class to NorthwindDataContext instance ‘db’
db1.LoadOptions = loadOptions;

//Now lets run our old query again

var orderFullQuery = from order in db1.Orders
select order;

List<Order> orderFullList = orderFullQuery.ToList();

Console.ReadLine();
}
}
}

[/code]

Quite interesting is it?.  With a single fetch we can get all relational necessary data for the object requested.

More information read from MSDN Reference about LoadDataOptions class

Courtesy : MSDN Library