Fixing the "There is already an open DataReader associated with this Command which must be closed first." exception in Entity Framework
Once you get your model, database and mapping metadata files configured correctly for Entity Framework, one of the first exceptions that you're likely to face is the DataReader already open exception. It will normally surface when you're iterating over a result set and then lazy loading a child collection. In the following example I have a Contact entity that has a Phone collection. If I lazy load the Phones then I'll get the DataReader exception.
var contacts = from c in db.Contact
select c;
foreach (Contact c in contacts)
{
if (c.Phones.IsLoaded == false)
c.Phones.Load();
if (c.Phones.Count > 0)
{
Console.WriteLine(c.LastName);
foreach (ContactPhone p in c.Phones)
{
Console.WriteLine("\t"+p.ContactPhoneId);
}
}
}
The reason for the exception is that there is an open data reader while reading in the contacts and then the loading of the child Phone collection is attempting to open another DataReader. By default, the SqlClient ADO.NET driver does not allow this.
There are two ways to fix this. First, if you are using SQL Server 2005, you can just enable MARS in your connection string.
add name="YourDBConnectionString" connectionString="metadata=.;
provider=System.Data.SqlClient;provider connection string="Data Source=irv-dev-vms05;Initial Catalog=YourDB;Integrated Security=True;
MultipleActiveResultSets=true"" providerName="System.Data.Mapping" /
A second option is to read all the results into memory in one shot and close the connection. Then you are free to open another connection to the database to read in the child entities. So how do you control this? The trip to the database does not happen when the LINQ statement is assigned to the var in the code above. Instead, the query to the database happens during the inplicit call to the enumerator of the contacts collection. To force all the contacts to be loaded, simply copy them to a list in memory using the ToList method and then continue the normal processing.
var contacts = from c in db.Contact
select c;
List results = contacts.ToList();
foreach (Contact c in results)
{
if (c.Phones.IsLoaded == false)
c.Phones.Load();
if (c.Phones.Count > 0)
{
Console.WriteLine(c.LastName);
foreach (ContactPhone p in c.Phones)
{
Console.WriteLine("\t"+p.ContactPhoneId);
}
}
}
This works as expected. Another important realization here is that there is an open connection to the database as long as you are reading in records through that enumerator! A novice programmer may unwittingly include a massive number of time consuming operations within the foreach loop (calling a web service, for example, comes to mind) which will keep the connection to the database open for an inordinate length of time. Of course, you won't fall into that trap.
*********分隔符*********
文中提到解决方案有两种：
1、数据库为SQL Server 2005版本时，可以在web.config数据库链接串中加入MultipleActiveResultSets=true。
2、进行重复操作之前，将数据查询结果放入内存中，再进行使用。
具体解释看上文。