I understand that it is good practice to use a using block when getting data from the LINQ-to-SQL classes as shown below.

However, when I do this, I can only access the shallow properties of orders (e.g. Order.OrderId) but not the deeper properties (e.g. Customer.CustomerName) since they seem to be dispose开发者_运维问答d of at this point.

I can take out my using block which allows me to access Customers, but this does not free the resources.

What is the best practice here?

using System;
using System.Collections.Generic;
using System.Linq;
using TestExtn2343.Models;

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

            var orders = GetOrders(10, 10);

            orders.ForEach(x =>
            {
                Customer customer = x.Customer;
                if (customer != null)
                {
                    //SUCCEEDS:
                    Console.WriteLine("{0}, {1}", x.OrderID);

                    //FAILS: "
                    Console.WriteLine("{0}, {1}", x.OrderID, x.Customer.ContactName.ToString());
                }
            });

            Console.ReadLine();
        }

        public static List<Order> GetOrders(int skip, int take)
        {
            using (MainDataContext db = new MainDataContext())
            {
                List<Order> orders = (from order in db.Orders
                                      select order).Skip(skip).Take(take).ToList();
                return orders;
            }
        }

    }
}

Answer:

Thanks Adam, using your suggestion I got my code to work like this:

public static void Main(string[] args)
{
    using (MainDataContext db = new MainDataContext())
    {
        GetOrders(db, 10, 10).ForEach(x => Console.WriteLine("{0}, {1}", x.OrderID, x.Customer.ContactName.ToString()));
    }
    Console.ReadLine();
}

public static List<Order> GetOrders(MainDataContext db, int skip, int take)
{
    List<Order> orders = (from order in db.Orders
                          select order).Skip(skip).Take(take).ToList();
    return orders;
}

The deeper properties of the object can't be accessed because LINQ-to-SQL uses lazy loading (that is, it automatically retrieves those records as needed, so when you access them for the first time it performs a database operation). Because the DataContext is disposed, it can't perform the necessary database operations. It is possible to have properties like this loaded "eagerly" (meaning at the time of initial retrieval), but you'll have to know all of these up front.

In general, your DataContext should live for the duration of your unit of work, so it appears here that your DataContext needs to be declared outside of this method and passed in (or even declared as an extension method on your DataContext).

Here is a good blog post on DataContext lifetime management.

If you know for a fact that you're going to want to get Customers, you can specify that in the DataLoadOptions and it will wire up the request to eagerly load them instead of lazily loading them.

Here's the msdn article on DataLoadOptions.

Example:

var dlo = new DataLoadOptions();
dlo.LoadWith<Order>(o => o.Customer); 
context.DataLoadOptions = dlo;