在Linq处理DataContext到SQL之后，无法访问相关数据 [英] Can't access related data after disposal of DataContext in Linq to SQL

问题描述

重申我的问题，旧文本

由于我仍然希望得到一个答案，我想重申我的问题。图像我有一个带有两个列表的GUI，一个显示数据库的所有条目的列表 tblOrders ，另一个显示每个顺序的项目。

我可以使用Linq2sql或EF从数据库获取所有订单，如下所示：

  ListOfOrders = DataContext.tblOrder.ToList（）; 
  

我可以在列表中显示这些订单或 datagridview 。然后选择一个作业，我想从表 tblItems 中访问实体的集合。我可以这样做：

  ListOfOrders.First（）。tblItems.toList（）; 
  

除了我不能，因为我需要 DataContext 已经处理。这在某种程度上是有道理的，因为我不能保证自从我检索到 ListOfOrders以来，没有添加到该订单中的新项目。所以理想情况下，我想检查是否添加了tblOrder.tblItems 集合，只有在需要时才从服务器重新检索该集合。

背景是，我的模型有点复杂：它由订单组成，其中包括由任务组成的零件。所以为了评估每个订单的进度，我必须检索属于一个订单的所有零件，对于每个订单，我必须看看有多少任务已经完成。在一个拥有200个工作的数据库中，每个都有1到10个部分，这使我的程序在响应速度方面变得缓慢...

任何人都可以帮助我吗？ >

原始问题

我发现了很多关于 DataContext的问题，但我还没有找到我的问题的解决方案。
如果我执行以下操作：

  using（DataClasses1DataContext DataContext = new DataClasses1DataContext（））
 ListOfOrders = DataContext.tblOrder.ToList（）; 
  

这给我一个 tblOrder 表。
但现在我想这样做：

  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 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）; 
} 
  

我不能因为访问与外键相关的tblItem 表似乎不存储。

什么是正常工作：

  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 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（）; 
  

但是据了解，这是不可取的。

编辑

我将我的示例扩展到Controller-View-Pattern。

 使用System.Collections.Generic; 
使用System.Linq; 
 
命名空间TestApplication 
 {
 class Controller 
 {
 private List< tblOrder> _命令; 
 public IList< tblOrder>订单
 {
 get 
 {
 return _orders; 
} 
} 
 
 public Controller（）
 {
 using（var DataContext = new DataClasses1DataContext（））
 {
 _orders = DataContext.tblOrder.ToList（）; 
} 
} 
} 
} 
  

该视图现在从控制器检索订单：

 使用System.Data; 
使用System.Linq; 
使用System.Windows.Forms; 
 
命名空间TestApplication 
 {
 public partial class Form1：Form 
 {
 public Form1（）
 {
 InitializeComponent（） ; 
 
控制器控制器=新的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在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）; 
} 
} 
} 
} 
  

问题依然不变...

解决方案

实体框架延迟加载对象数据，意味着它加载最小数据量必须尽可能晚。您的查询：

  ListOfOrders = context.tblOrder.ToList（）; 
  

您正在请求 tblOrder 表。实体框架不会在您的程序中向前看，并且了解在上下文已被处理之后，您将看到 tblItem 表，因此它假定可以加载 tblItem 数据。懒惰，它加载了最低要求： tblOrder 中的记录列表。

有

禁用延迟加载

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

使用 LazyLoadingEnabled = false Entity Framework将选择 tblOrder 表的所有内容以及通过外键与其连接的所有表。这可能需要一些时间，并使用大量内存，具体取决于相关表的大小和数量。

（编辑：我的错误，禁用LazyLoading不能加载加载，而有没有默认的配置为渴望加载。对错误信息感到抱歉，下面的 .Include 命令看起来像唯一的方法。）

包含其他表

  using（var context = new DataClasses1DataContext ））
 {
 data.Configuration.LazyLoadingEnabled = false; 
 _orders = context.tblOrder.Include（tblItems）。ToList（）; 
} 
  

这告诉实体框架从加载所有相关数据tblItems 前面加载 tblOrders 表数据。 EF仍然不会从其他相关表中加载任何数据，以便在上下文处理之后其他数据将不可用。

但是，这并不能解决问题陈旧的数据 - 也就是说，随着时间的推移， dataGridView1 中的记录将不再是最新的。您可以有一个触发刷新的按钮或计时器。刷新的最简单的方法是重新执行整个过程 - 重新加载 _orders ，然后有选择地重新填充 dataGridView1 。

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

解决方案

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.

这篇关于在Linq处理DataContext到SQL之后，无法访问相关数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！