Why is MySQL throwing DataReader Exception when used for EF

Question

I have been trying to use MySQL with Entity Framework 6. I have several projects that are successfully using SQL Server Express without issue. The new project is based on those previous projects and I needed some minor changes to connect to and seed the EF database. The problem I am running into now is that a simple query that works fine on SQL Server is throwing an exception when using MySQL.

There are two tables/entities involved. Device is a representation of a physical device that collects data samples. Monitor is a definition of the type of data to be sampled. So, there are multiple monitors defined for each device. As soon as I try to retrieve the list of monitors for a device, it throws the following error:

MySql.Data.MySqlClient.MySqlException: There is already an open DataReader associated with this Connection which must be closed first.

Here is the code:

using (var context = new ApplicationDbContext())
{
    foreach (var device in context.Devices)
    {
        var monitors = device.Monitors.Where(m => m.Enabled == true).ToList();

        if (monitors.Count > 0)
        {
            // Get sampled data from device
        }
    }
}

I am guessing this is a MySQL driver issue with respect to their EF implementation. Anyone have any ideas how to correct this or work around it? Am I doing something really stupid and obvious that I am just not seeing? I use these same patterns for accessing related entities all the time in my SQL Server implementations. So, I am worried that this is going to be a show stopper for using MySQL, at least with their built in drivers.

Some relevant information:

.NET: 4.5.2

EF: 6

MySQL: 5.7

MySqlClient: 6.9.11.0

Answer 1

This is happening because context.Devices is keeping a MySqlDataReader open (to stream the results in lazily), but device.Monitors.Where(...) tries to execute a second query on that same connection.

I can think of two workarounds:

1. Force evaluation of the first query

Use .ToList() to force all results to be brought into memory:

foreach (var device in context.Devices.ToList())

Note that this is unnecessarily inefficient because it's potentially bringing back a lot of data into memory that you might not need.

2. Combine both queries into one

foreach (var device in context.Devices.Where(d => d.Monitors.Any(m => m.Enabled)))
{
    // Get sampled data from device
}

This should cause EF to construct a single, more efficient query that only retrieves the device objects you need and avoids the "already an open DataReader" error.