Loading OptionsWhich objects are needed for processing often depends on some user action or business logic. Consider an example in which a set of orders is displayed to a user. The user, based on some criteria, may pick a particular Order entity to drill into. At that point, the Order_Detail entities related to that specific Order are needed. When the set of orders was initially retrieved, the specific Order entity of interest may not have been known. In such a case, it is interesting to be able to load the Order_Details in a deferred or lazy or on-demand fashion. The same applies even when there is business logic in place of a user that determines if further drill-down is needed for a subset of entities.Deferred LoadingLINQ to SQL enables deferred loading through simple navigation. For a given Order, its E A? collection may be empty when the E is first loaded. But when the collection is first iterated, LINQ to SQL fires off a query to load the collection. After it is loaded, the collection is then available for future use without requiring another query. Asmall delay involved in executing the query aside, the E A? collection property behaves as if it is available. If it is never iterated, the corresponding query is never executed, and unnecessary transfer of data is avoided. You can imagine the savings if a thousand orders, each with dozens of details, are present in the database, and the user picks just one or a few of the orders for further drill-down. In the following example, using the ? = log, you can see that a query is fired when the inner " loop is hit. Let's add a few extra5 statements to see when a query gets executed:
II Connect the log to console to see generated SQL
db.Log = Console.Out;
var OrdersQuery = from o in db.Orders where o.Shipria == 1 select o;
Console.WriteLine("lterating over orders");
foreach(Order o in OrdersQuery)
{
II Process Order here if (o.Freight > 4OO)
Console.WriteLine("lterating over Order_Details for Order {O}\n", o.OrderlD);
II A query to load o.Order_Details is fired on first reference foreach (Order_Detail od in o.Order_Details)
II Process Order_Details
}
As explained before, E ' is executed at the beginning of the outer " loop due to deferred execution. The query for the deferred loaded collection E E A? is executed due to the inner " loop. Deferred execution of queries is a LINQ feature that is not specific to LINQ to SQL. Deferred loading is a LINQ to SQL feature. The following is the query generated for the deferred loading of the E A? property for the Order entity with ID 10430 when it is iterated the first time. It can be seen in the ? = log.
lterating over Order_Details for Order 1O43O
SELECT [tO].[OrderlD], [tO].[ProductlD], [tO].[UnitPrice], [tO].[Quantity], [tO].[Discount]
FROM [dbo].[Order Details] AS [tO] WHERE [tO].[OrderlD] = IpO
---- IpO: lnput lnt (Size = O; Prec = O; Scale = O) [1O43O]
The deferred loading capability requires LINQ to SQL to intercept the access to a relationship property. The interception provides a way to check if the target is already loaded so that it can be populated on first access. Two special types, and , are used to provide the interception capability. is used where the target is a collection, and is used where the target is a singleton. For example, E E A? is of type E A? , and E A ? E is of type E . The designer and the command- line tool SqlMetal both use the correct types on each end of a relationship based on the cardinality of the relationship. As a result, the generated entities are automatically equipped to provide deferred loading for relationships. Furthermore, deferred loading is also available for a nonrelationship property of a class. For example, if the Products table contains a large image of the product, you can choose to defer-load it by using a property of type; B , where B is the type used for mapping to the image. In the designer, you can set the "Delay Loading" property to true for such a class member to change the type to ;. B instead of B . The image would then be defer-loaded much like the Category entity referenced by the property in the ) " class. The difference between the two is that the image is not an entity-it is just a part of the ) " entity. is a different entity with its own identity.Eager LoadingWhereas deferred loading is handy when only an occasional relationship is navigated, in other cases all or most of the related entities are needed. Consider a variant of the previously shown logic in which the E A ? s for all E s are needed, regardless of the freight. In such cases, deferred loading can be too chatty and inefficient due to the number of queries and resulting round-trips to the database. Another option called eager loading (also called immediate loading) is available for just such a case. You can instruct the ? = to automatically bring in E A? for all retrieved E s, as shown in the following example. The ? E% on the ? = can be used to specify a relationship that should be eager-loaded-E A? for E entities in the example. The log shows that, unlike in the previous example, the inner " loop does not result in additional queries. All the related E A? s are preloaded with the Order entities.II Connect the log to console to see generated SQLdb.Log = Console.Out;DataLoadOptions dlopt = new DataLoadOptions(); dlopt.LoadWith<Order>(o => o.Order_Details); db.LoadOptions = dlopt;var OrdersQuery = from o in db.Orders
where o.Shipria == 1 && o.Freight > 4OO
select o;
II Order_Details are eager--loaded; additional queries are not needed foreach (Order_Detail od in o.Order_Details)
The following query for loading all the related E A? and the queried E s is different from the queries used for multistep deferred loading. For simplicity, the clause with columns from the Orders and Order Details tables is trimmed and elided. LINQ to SQL takes the results of the left outer join and constructs the E –E A? hierarchy.SELECT [tO].[OrderlD], [tO].[CustomerlD], [tO].[EmployeelD], ... (
SELECT COUNT(*)
FROM [dbo].[Order Details] AS [t2] WHERE [t2].[OrderlD] = [tO].[OrderlD]
) AS [value]
FROM [dbo].[Orders] AS [tO]
LEFT OUTER JOlN [dbo].[Order Details] AS [t1] ON [t1].[OrderlD] = [tO].[OrderlD]
WHERE ([tO].[Shipria] = IpO) AND ([tO].[Freight] > Ip1) ORDER BY [tO].[OrderlD], [t1].[ProductlD]
---- IpO: lnput lnt (Size = O; Prec = O; Scale = O) [1]
---- Ip1: lnput Decimal (Size = O; Prec = 33; Scale = 4) [4OO]
Multiple relationships may be set up for eager loading using ? E% . The only restriction is that the options cannot form a cycle. For example, after E E A? is specified for eager loading, E A ? E cannot be specified, because it would form a cycle among the classes through the relationships-E –E A? –E . This avoids the problem of unending traversal of cycles. One consequence is that certain relationships cannot be eager-loaded. For example, consider an Employee entity pointing to a collection of Employee entities in a managerreports relationship. Because the target entity type in % % is % , the relationship cannot be eager-loaded. However, deferred loading may be used in such cases because the relationship is loaded for a given instance without any danger of endless cycles.II Error. Cyclic relationship cannot be eager--loaded dlopt.LoadWith<Employee>(e => e.Reports);
The ability to load related objects is a crucial way to bridge the gap between object models and the underlying relational storage. Deferred and eager-loading options ensure that the bridging is done as efficiently as needed in the particular scenario. Yet an additional problem with relationships remains. Sometimes the sizes of related collections can be too large to be loaded in either eager or deferred fashion. Consider a database that stores orders over many years for customers. If the application is interested in showing only a small subset of the orders, perhaps for the last three months, it would be wasteful to load all the orders, whether eagerly or in a deferred fashion. Hence, LINQ to SQL provides an additional loading option to filter the collections in a consistent fashion.dlopt.AssociateWith<Customer>(c => c.Orders.Where(o => o.OrderDate.ralue.Year == 1998));
The load options for eager loading and filtering of relationships must be specified before any queries are performed. Specifying the options after the ? = instance has been used for queries creates a risk of inconsistent results across queries. Hence, it results in an exception. The 6 " 5 option may be specified with or without the eager loading option. It is equally applicable to deferred loading as well. The following example shows how the option can be used with the eager loading option:
DataLoadOptions dlopt = new DataLoadOptions(); dlopt.LoadWith<Customer>(c => c.Orders); dlopt.AssociateWith<Customer>(c => c.Orders.Where(
o => o.OrderDate.ralue.Year == 1998));
db.LoadOptions = dlopt;
Match the Loading Option to the Usage PatternWhen you use the power of the dot by navigating relationships, think about the most common usage pattern. If a user action in a user interface or a programmatic choice based on data requires an occasional loading of a related object, use deferred loading. If all related data is needed, use eager loading. Even where you want to filter related data in a predetermined fashion, use eager loading and queries associated with the relationship.