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 ...
