2

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

Psyfun
  • 353
  • 3
  • 15
  • 1
    three quick ideas: 1) combine both queries into one LINQ query 2) exhaust the IQueryable that is `context.Devices` before you start the second query; 3) activate the equivalent of SQL Server's "multiple active resultsets" (MARS) [for your MySql connection](https://stackoverflow.com/q/25953560/1132334). – Cee McSharpface May 11 '18 at 13:57
  • 1
    related: https://stackoverflow.com/q/4867602/1132334 – Cee McSharpface May 11 '18 at 14:08
  • That makes sense. I never encountered this before because the database connection I have been using for SQL Server was enabling MARS. I am hoping that the MySqlClient implements MARS as well. I should probably re-evaluate my ling queries to try to eliminate the situation altogether, though. Thanks for the help. – Psyfun May 11 '18 at 16:52
  • "I am hoping that the MySqlClient implements MARS as well." This is not possible because it's a limitation of MySQL Server itself (there is no specified way to multiplex multiple result sets over one connection in the MySQL protocol https://dev.mysql.com/doc/internals/en/client-server-protocol.html ). – Bradley Grainger May 11 '18 at 18:30

1 Answers1

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.

Bradley Grainger
  • 27,458
  • 4
  • 91
  • 108
  • Yes, these should get things going in the right direction. As I noted above, the reason I was not running into this problem before was that MARS was enabled on my SQL Server connections. – Psyfun May 11 '18 at 16:53