The deferred loading of data in LINQ to SQL can operates at two granularity levels: the entity and the entity data member.
LINQ to SQL allows the definition of an entity model that maps relational table rows to instances of a .NET class. One interesting feature is the navigation between entities. For example, you might have the following lines of code:
Order_Det order = db.Orders.Single((o) => o.OrderID == 10251);
decimal total = order.Order_Details.Sum(od => od.Quantity * od.UnitPrice);
By default, each of these lines of code produces a different query to SQL Server. The first line looks for the order 10251. The second line get the lines of the order and calculates its total value. I said “by default” because this is a behavior controlled by the DeferredLoadingEnabled property of the DataContext. If you disable this property with the following line of code:
dataContext.DeferredLoadingEnabled = false;
the access to the Order_Details property will result in an empty list.
Probably it is not common disabling DeferredLoadingEnabled setting. More often, you might find useful to load in memory all the lines of an order together with an Order instance. To do that, you can use the LoadOption setting of the DataContext, which has to be set before querying the Orders in the DataContext instance.
DataLoadOptions loadOptions = new DataLoadOptions();
loadOptions.LoadWith<Order>(o => o.Order_Details);
dataContext.LoadOptions = loadOptions;
As I said, another level of deferred loading is the entity data member. If you have a table with a very large column (for example, a VARCHAR(MAX) one), you can avoid to load that property in memory each and every time you build an instance of the containing entity. To get deferred loading on a data member, you need to declare the storage member of Link<T> type, which is a wrapper over the T exposed type of the data member itself. In the following code we can see the Address property of a DelayCustomer class declared in this way.
[Table(Name = "Customers")]
public class DelayCustomer {
private Link<string> _Address;
[Column(IsPrimaryKey = true)]
public string CustomerID;
[Column]
public string CompanyName;
[Column]
public string Country;
[Column(Storage = "_Address")]
public string Address {
get { return _Address.Value; }
set { _Address.Value = value; }
}
}
The Link<T> wrapper produces an access to the SQL Server database whenever that property is accessed for the first time after the container object initialization. The following code will produce a query to SQL Server for each of the row in the foreach loop.
var query =
from c in Customers
where c.Country == "Italy"
select c;
foreach (var row in query) {
Console.WriteLine(
"{0} - {1}",
row.CompanyName,
row.Address);
}
Deferred loading of entities and properties is useful to consume less memory when not all the related entities and/or not all the entity data members are accessed frequently.
