MySQL C# 实体框架：已经有一个与此连接关联的打开的 DataReader，必须先关闭它
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6140633/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
C# Entity Framework: There is already an open DataReader associated with this Connection which must be closed first
提问by tortuga
I'm working on a ASP.NET MVC3 application and I've created a database in MySQL 5.5which contains a company table having a one-to-many relationship with a contacts table.
我正在开发一个 ASP.NET MVC3 应用程序，我在MySQL 5.5 中创建了一个数据库，其中包含一个与联系人表具有一对多关系的公司表。
table Bedrijf(with navigation property "contacts")
表Bedrijf（带有导航属性“ contacts”）
table Contact
台联系
Since I had to take over this database from a currently running site I generated a Entity Model based on that database and I wrote the following code to display a list of companies (grouped by status), mentioning the number of contacts in that company:
由于我必须从当前运行的站点接管该数据库，因此我基于该数据库生成了一个实体模型，并编写了以下代码来显示公司列表（按状态分组），并提及该公司的联系人数量：
CompanyRepository.cs
CompanyRepository.cs
...
public IQueryable<Bedrijf> getCompaniesByStatus(int status)
{
return entities.Bedrijven.Where(c => c.bedrijf_status == status).OrderBy(c => c.bedrijf_naam);
}
...
View calling 3 partial Views
视图调用 3 个局部视图
@{Html.RenderPartial("ucCompaniesByStatus", Model.newCompanies, (new ViewDataDictionary { { "Titel", "Nieuwe bedrijven" } }));}
<br />
@{Html.RenderPartial("ucCompaniesByStatus", Model.activeCompanies, (new ViewDataDictionary { { "Titel", "Actieve bedrijven" } }));}
<br />
@{Html.RenderPartial("ucCompaniesByStatus", Model.inActiveCompanies, (new ViewDataDictionary { { "Titel", "Niet actieve bedrijven" } }));}
Partial View
局部视图
@model IEnumerable<xxx.Models.Bedrijf>
<table id="companytable">
<tr>
<th id="thtitle">
@ViewData["Titel"]
</th>
<th id="thactions"></th>
</tr>
@foreach (var item in Model)
{
<tr>
<td>
@Html.ActionLink(@item.bedrijf_naam, "CompanyDetails", new { id = item.bedrijf_id })
(@item.contacts.Count contact(en))
</td>
<td id="actions">
@Html.ActionLink("Edit", "CompanyEdit", new { id=item.bedrijf_id }) |
@Html.ActionLink("Details", "CompanyDetails", new { id = item.bedrijf_id }) |
@Html.ActionLink("Delete", "Delete", new { id = item.bedrijf_id })
</td>
</tr>
}
</table>
In my list of companies, I would like to display the number of contacts assigned to that company but I got the following Error:
在我的公司列表中，我想显示分配给该公司的联系人数量，但出现以下错误：
There is already an open DataReader associated with this Connection which must be closed first.
已经有一个与此连接关联的打开的 DataReader，必须先关闭它。
When go to my .edmx file and set Lazy Loading Enabled : FalseI'm able to get a result (But the count on my contacts is not working (I get 0), assuming my related contacts are not loaded now.):
当转到我的 .edmx 文件并设置Lazy Loading Enabled : False我能够得到一个结果（但我的联系人计数不起作用（我得到 0），假设我的相关联系人现在没有加载。）：
How Can I get this working with Lazy Loading Enabled? My beginner ASP.NET (MVC) skills don't bring me to a solution at the moment.
如何在启用延迟加载的情况下使用此功能？我的 ASP.NET (MVC) 初学者技能目前无法找到解决方案。
Adding MultipleActiveResultSets=True;in the web.config connectionstring is pointed out as solution often, but no difference in my case.
添加MultipleActiveResultSets=True; 在 web.config 连接字符串中经常被指出作为解决方案，但在我的情况下没有区别。
Tried the .Includein my CompanyRespository while having lazy loading set to False, but I think I didn't do that correctly since I'm not familiar witht he syntax.
在将延迟加载设置为 False 时尝试了.Include在我的 CompanyRespository 中，但我认为我没有正确执行此操作，因为我不熟悉他的语法。
This description makes also sense;
这个描述也有道理；
It is not about closing connection. EF manages connection correctly. My understanding of this problem is that there are multiple data retrieval commands executed on single connection (or single command with multiple selects) while next DataReader is executed before first one has completed the reading. The only way to avoid the exception is to allow multiple nested DataReaders = turn on MultipleActiveResultSets. Another scenario when this always happens is when you iterate through result of the query (IQueryable) and you will trigger lazy loading for loaded entity inside the iteration.
这与关闭连接无关。EF 正确管理连接。我对这个问题的理解是，有多个数据检索命令在单个连接（或具有多个选择的单个命令）上执行，而下一个 DataReader 在第一个完成读取之前执行。避免异常的唯一方法是允许多个嵌套的 DataReaders = 打开 MultipleActiveResultSets。总是发生这种情况的另一种情况是，当您遍历查询结果 (IQueryable) 时，您将在迭代中触发加载实体的延迟加载。
but no idea how I should fix this problem in my code with this information. Where/How use @item.contacts.Countto show the number of contacts?
但不知道我应该如何使用这些信息在我的代码中解决这个问题。在哪里/如何使用@item.contacts.Count来显示联系人数量？
Thanks in advance.
提前致谢。
采纳答案by Ladislav Mrnka
Try using this:
尝试使用这个：
public IQueryable<Bedrijf> getCompaniesByStatus(int status)
{
return entities.Bedrijven
.Include("contacts")
.Where(c => c.bedrijf_status == status)
.OrderBy(c => c.bedrijf_naam);
}
I think MySql connector probably doesn't support multiple active result sets and because of that the setting in connection string didn't help you.
我认为 MySql 连接器可能不支持多个活动结果集，因此连接字符串中的设置对您没有帮助。
回答by Abhimanyu Shukla
I had similar Issue. Noticed that was using IEnumerable collection and was calling another function, where was querying database. Since it was IEnumerable collection, reader was open. Changed IEnumerable to list to resolve the issue.
我有类似的问题。注意到正在使用 IEnumerable 集合并调用另一个函数，其中查询数据库。由于是 IEnumerable 集合，所以读者是开放的。将 IEnumerable 更改为 list 以解决该问题。
回答by Greg Quinn
The MySQL connector does not support MultipleActiveResultSets, so that connection string modification will not work.
MySQL 连接器不支持 MultipleActiveResultSets，因此连接字符串修改将不起作用。
To get around the issue, in your controller, simply add the .ToList() method to wherever your data is queried.. For example...
为了解决这个问题，在您的控制器中，只需将 .ToList() 方法添加到查询数据的任何位置.. 例如...
public ActionResult Create()
{
ViewBag.PossibleCompanies = context.Companies.ToList();
return View();
}
