Can't access related data after disposal of DataContext in Linq to SQL

Question

Restated my Question, old Text Below

As I am still hoping for an answer I would like to restate my question. Image I have a GUI with two lists, one that shows a List of all entries to a database tblOrders and another one that shows the items in each order.

I can use Linq2sql or EF to get all orders from the database, like so:

using (DataClasses1DataContext DataContext = new DataClasses1DataContext())
    ListOfOrders = DataContext.tblOrder.ToList();

I can display these order in a list or datagridview. Then on a selection of one of the jobs I want to access the collection of entities from the table tblItems. I can do this like so:

ListOfOrders.First().tblItems.toList();

Except I can not, because I need the DataContext which has been disposed. This makes sense in a way, since I can not guarantee that there is not a new items that has been added to that order since I retrieved the ListOfOrders. So ideally I would like to check if there has been an addition to a tblOrder.tblItems collection and only newly retrieve that collection from the server if required.

The background is, that my model is a bit more complex: It consists of Orders, which consist of Parts, which consist of Tasks. So to assess the progress of each order I have to retrieve all parts that belong to an order and for each of them I have to see how many of the tasks have been completed. In a database with 200 job, each with 1 to 10 parts this simply makes my program to slow in terms of responsiveness ...

Can anyone help me?

Original Question

I found a lot of questions concerning DataContext, but I have not found a solution to my problem yet. If I do the following:

using (DataClasses1DataContext DataContext = new DataClasses1DataContext())
    ListOfOrders = DataContext.tblOrder.ToList();

This gives me a list of the entities in the tblOrder table. But now I want to do this:

DataTable Orders = new DataTable();
Orders.Columns.Add("Order-ID", typeof(int));
Orders.Columns.Add("Order-Name", typeof(string));
Orders.Columns.Add("Order-Items", typeof(string));

dataGridView1.DataSource = Orders;

foreach (tblOrder Order in ListOfOrders)
{
    var newRow = Orders.NewRow();
    newRow["Order-ID"] = Order.orderID;
    newRow["Order-Name"] = Order.orderName;
    newRow["Order-Items"] = string.Join(", ", Order.tblItem.Select(item=> item.itemName).ToList());
        // System.ObjectDisposedException
    (dataGridView1.DataSource as DataTable).Rows.Add(newRow);
}

And I can not because accessing all entities in the tblItem table that are related to orders by foreign key don't seem to be stored.

What which is working:

DataClasses1DataContext DataContext = new DataClasses1DataContext();
ListOfOrders = DataContext.tblOrder.ToList();

DataTable Orders = new DataTable();
Orders.Columns.Add("Order-ID", typeof(int));
Orders.Columns.Add("Order-Name", typeof(string));
Orders.Columns.Add("Order-Items", typeof(string));

dataGridView1.DataSource = Orders;

foreach (tblOrder Order in ListOfOrders)
{
    var newRow = Orders.NewRow();
    newRow["Order-ID"] = Order.orderID;
    newRow["Order-Name"] = Order.orderName;
    newRow["Order-Items"] = string.Join(", ", Order.tblItem.Select(item=> item.itemName).ToList()); 
    (dataGridView1.DataSource as DataTable).Rows.Add(newRow);
}

DataContext.Dispose();

But as I understand this is not desirable.

EDIT

I extended my example into a Controller-View-Pattern.

using System.Collections.Generic;
using System.Linq;

namespace TestApplication
{
    class Controller
    {
        private List<tblOrder> _orders;
        public IList<tblOrder> Orders
        {
            get
            {
                return _orders;
            }
        }

        public Controller()
        {
            using (var DataContext = new DataClasses1DataContext())
            {
                _orders = DataContext.tblOrder.ToList();
            }
        }
    }
}

And the view now retrieves the Orders from the controller:

using System.Data;
using System.Linq;
using System.Windows.Forms;

namespace TestApplication
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            Controller controller = new Controller();

            DataTable Orders = new DataTable();
            Orders.Columns.Add("Order-ID", typeof(int));
            Orders.Columns.Add("Order-Name", typeof(string));
            Orders.Columns.Add("Order-Items", typeof(string));

            dataGridView1.DataSource = Orders;

            foreach (tblOrder Order in controller.Orders)
            {
                var newRow = Orders.NewRow();
                newRow["Order-ID"] = Order.orderID;
                newRow["Order-Name"] = Order.orderName;
                newRow["Order-Items"] = string.Join(", ", Order.tblItem.Select(item=> item.itemName).ToList());
                (dataGridView1.DataSource as DataTable).Rows.Add(newRow);
            }
        }
    }
}

Sadly the problem remains the same ...

Answer 1

Entity Framework lazy-loads object data, meaning it loads the minimum amount of data it has to as late as possible. Take your query:

ListOfOrders = context.tblOrder.ToList();

Here you are requesting all of the records in the tblOrder table. Entity Framework doesn't read ahead in your program and understand that you will be looking at the tblItem table after the context has been disposed, so it assumes it can load the tblItem data later. Being lazy, it loads the bare minimum requested: The list of records in tblOrder.

There are two ways is a way around this:

Disable lazy loading

    using (var context = new DataClasses1DataContext())
    {
        data.Configuration.LazyLoadingEnabled = false;
        _orders = context.tblOrder.ToList();
    }

With LazyLoadingEnabled=false Entity Framework will select the entire contents of the tblOrder table and all tables connected with it via a foreign key. This may take a while and use a lot of memory, depending on the size and number of related tables.

(Edit: My mistake, disabling LazyLoading does not enable eager loading, and there is no default configuration for eager loading. Apologies for the misinformation. The .Include command below looks like the only way to go.)

Include additional tables

    using (var context = new DataClasses1DataContext())
    {
        data.Configuration.LazyLoadingEnabled = false;
        _orders = context.tblOrder.Include("tblItems").ToList();
    }

This tells Entity Framework to load all related data from tblItems up front while it's loading the tblOrders table data. EF still doesn't load any data from other related tables, so that other data will be unavailable after the context is disposed.

However, this does not solve the problem of stale data -- that is, over time the records in dataGridView1 will no longer be up-to-date. You could have a button or timer that triggers a refresh. The simplest method of refreshing would be to do the entire process over again -- reload _orders, then selectively repopulate dataGridView1.