Share via

Deferred versus Immediate Loading (LINQ to SQL)

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).) You cannot see the fact that the related objects are not already loaded, because an attempt to access them produces a request that retrieves them.

For example, you might want to query for a particular set of orders and then only occasionally send an e-mail notification to particular customers. You would not necessarily need initially to retrieve all customer data with every order. You can use deferred loading to defer retrieval of extra information until you absolutely have to. Consider the following example:

Dim db As New Northwnd("c:\northwnd.mdf")
Dim notificationQuery = _
    From ord In db.Orders _
    Where ord.ShipVia = 3 _
    Select ord

For Each ordObj As Order In notificationQuery
    If ordObj.Freight > 200 Then
    End If 

    Northwnd db = new Northwnd(@"northwnd.mdf");

    IQueryable<Order> notificationQuery =
    from ord in db.Orders
 where ord.ShipVia == 3
  select ord;

    foreach (Order ordObj in notificationQuery)
        if (ordObj.Freight > 200)


The opposite might also be true. You might have an application that has to view customer and order data at the same time. You know you need both sets of data. You know your application needs order information for each customer as soon as you get the results. You would not want to submit individual queries for orders for every customer. What you really want is to retrieve the order data together with the customers.

Dim db As New Northwnd("c:\northwnd.mdf")

db.DeferredLoadingEnabled = False 

Dim custQuery = _
    From cust In db.Customers _
    Where cust.City = "London" _
    Select cust

For Each custObj As Customer In custQuery
    For Each ordObj As Order In custObj.Orders
Northwnd db = new Northwnd(@"c:\northwnd.mdf");

db.DeferredLoadingEnabled = false;

IQueryable<Customer> custQuery =
    from cust in db.Customers
    where cust.City == "London" 
    select cust;

foreach (Customer custObj in custQuery)
    foreach (Order ordObj in custObj.Orders)

You can also join customers and orders in a query by forming the cross-product and retrieving all the relative bits of data as one large projection. But these results are not entities. (For more information, see The LINQ to SQL Object Model). Entities are objects that have identity and that you can modify, whereas these results would be projections that cannot be changed and persisted. Even worse, you would be retrieving lots of redundant data as each customer repeats for each order in the flattened join output.

What you really need is a way to retrieve a set of related objects at the same time. The set is a delineated section of a graph so that you would never be retrieving more or less than was necessary for your intended use. For this purpose, LINQ to SQL provides DataLoadOptions for immediate loading of a region of your object model. Methods include:

  • The LoadWith method, to immediately load data related to the main target.

  • The AssociateWith method, to filter objects retrieved for a particular relationship.

See Also

Other Resources

Query Concepts in LINQ to SQL