Cannot access related data after deleting DataContext in Linq to SQL

Question

Repeat my question, old text below

Since I still hope for an answer, I would like to repeat my question. Image I have a graphical interface with two lists, one of which shows a list of all the records in the tblOrders database and the other that shows the elements in each order.

I can use Linq2sql or EF to get all orders from the database, for example:

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

I can display this order in a list or datagridview . Then, by choosing one of the tasks, I want to access the collection of objects from the tblItems table. I can do it like this:

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

Also, I cannot, because I need a DataContext that has been deleted. This makes sense in some way, because I cannot guarantee that there are no new items that were added to this order, since I received the ListOfOrders. So ideally I would like to check if there has been an addition to a tblOrder.tblItems collection ListOfOrders. So ideally I would like to check if there has been an addition to a tblOrder.tblItems ListOfOrders. So ideally I would like to check if there has been an addition to a tblOrder.tblItems and only recently retrieved this collection from the server, if necessary.

My model is supposed to be a little more complicated: it consists of orders consisting of parts that consist of tasks. Therefore, in order to evaluate the progress of each order, I have to get all the parts belonging to the order, and for each of them I have to see how many tasks have been completed. In a database with 200 tasks, each of which contains from 1 to 10 parts, this simply slows down my program in terms of responsiveness ...

Can anybody help me?

Original question

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

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

This gives me a list of objects 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 cannot, because access to all the objects in the tblItem table associated with foreign key orders does not seem to be preserved.

What works:

 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 it, this is undesirable.

EDIT

I extended my example to 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(); } } } } 

Now the view now receives 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); } } } } 

Unfortunately, the problem remains the same ...

Answer 1

Entity Framework lazy-load transfers the data of the object, that is, it loads the minimum amount of data that it has, as late as possible. Take the request:

 ListOfOrders = context.tblOrder.ToList(); 

Here you request all entries in the tblOrder table. The Entity Framework does not read in your program and does not understand that you will look at the tblItem table after the context has been deleted, so it is assumed that it can load tblItem data later. Being lazy, it loads the required minimum minimum: List of entries in tblOrder .

There are two ways :

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 associated with it using a foreign key. This can take some time and consume a lot of memory depending on the size and number of related tables. C>

(Edit: my mistake, disabling LazyLoading does not allow you to download files impatiently , and there is no default configuration for actively loading Apologies for 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 means that the Entity Framework loads all the associated data from tblItems in front when it loads the tblOrders table tblOrders . EF still does not load data from other related tables, so other data will not be available after the context is deleted.

However, this does not solve the problem of obsolete data, that is, over time, the entries in dataGridView1 will cease to be relevant. You may have a button or timer that starts the update. The easiest way to update is to repeat the whole process - reload _orders , and then selectively dataGridView1 .

Answer 2

I advise you to create a new result class containing your data:

 class Result { int ID {get;set;} string OrderName {get;set;} IEnumerable<string> ItemNames {get;set;} } 

Select the required data:

 class Controller { private List<Result> _orders; public IList<Result> Orders { get { return _orders; } } public Controller() { using (var DataContext = new DataClasses1DataContext()) { _orders = DataContext.tblOrder.Select(o=> new Result { ID = o.orderID, OrderName = o.orderName, ItemNames = o.tblItem.Select(item=> item.itemName) }).ToList(); } } } 

And after that, tie this collection to the grid view. This way you get all your data before deleting the context, and you no longer have any dependencies.

Answer 3

Just the answer to the first question.
Like EF, you set up the context after the work [units] of work (necessary in ASP, good practice in WinForm / WPF).

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

after that if you try to run this statement

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

EF works this way:
- get the first ListOfOrders element, which is a proxy server for your object.
- try to get tblItems related to LazyLoad.
Currently, to get tblItems, you need access to the database with the context that is extracted from the proxy server of the first ListOfOrder, but the context is located.

Therefore, you cannot use this approach.

There are two solutions and some options on them:
- You can read all tblItems before deleting the context (you can see it in another answer), but not a scalable approach.
- When you need to access tblItems, you receive an order from a new context and do what you need before deleting it. In your case

 using (DataClasses1DataContext DataContext = new DataClasses1DataContext()) { int Id = ListOfOrders.First().Id; var myListOftblItems = DataContext.tblOrder.Find(Id).tblItems.ToList(); } 

A variation of this approach is to read only those elements. You can do this if tblItems has also set the foreign key field, and not just the navigation property (usually I do not).

 using (DataClasses1DataContext DataContext = new DataClasses1DataContext()) { int Id = ListOfOrders.First().Id; var myListOftblItems = DataContext.tblItems.Where(t => t.IdOrder == Id).ToList(); } 

Answer 4

The default EF behavior for Lazy Load objects. All in all, this is a good idea, and I think you shouldn't turn it off unless you have a good reason.

EF also provides methods for Eager Loading the specified objects:

 // Add this! using System.Data.Entity; 

Then you can use the Include method:

 public static IList<Order> GetOrdersAndItems() { List<Order> orders; using (var context = new ShopDC()) { context.Database.Log = Console.WriteLine; Console.WriteLine("Orders: " + context.Orders.Count()); orders = context.Orders .Where(o => o.OrderID > 0) // Tells EF to eager load all the items .Include(o => o.Items) .ToList(); } return orders; } 

Now you can use GetOrdersAndItems to load the list with all your orders, and each order will contain all the elements:

 public static void Run() { IList<Order> disconnectedOrders = GetOrdersAndItems(); foreach (var order in disconnectedOrders) { Console.WriteLine(order.Name); foreach (var item in order.Items) { Console.WriteLine("--" + item.Name); } } } 

For more examples and several levels, see here.

Note: using an assistant or controller or repository is not important in understanding this mechanism, so I just used the static method.