'Option not supported' error when using SQL Server with MySQL

c# entity-framework-6 mysql sql-server

Question

I'm trying to retrieve data from both MySQL and SQL Server on the same console application. I manage to retrieve data from MySQL, however when I trying to retrieve data from SQL Server, I got System.ArgumentException: 'Option not supported. Parameter name: multipleactiveresultsets' error.

The following is my app.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
  </startup>

  <connectionStrings>
    <add name="MySQLDb" providerName="MySql.Data.MySqlClient" connectionString="server=localhost;port=3306;database=sakila;uid=some_user;password=some_password"/>
    <add name="SQLDb" providerName="System.Data.SqlClient" connectionString="data source=USER-PC\SQLEXPRESS;initial catalog=MyDatabase;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"/>
  </connectionStrings>

  <entityFramework codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6">
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework"/>
    <providers>
      <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6"/>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer"/>
    </providers>
  </entityFramework>
</configuration>

And my C#:

#region MySQL.
{
    var dbContext = new MySQLDb();
    var dbSet = dbContext.Set<Actor>();

    var actors = dbSet.ToList();
}
#endregion

#region SQLServer.
{
    var dbContext = new SQLDb();
    var dbSet = dbContext.Set<User>();

    var users = dbSet.ToList(); // <-- Throw exception.
}
#endregion

If I disable entityFramework section in app.config and MySQL code block in my C# code, I can retrieve data from SQL Server without any issue.

Version info

  • MySQL.Data.Entity 6.10.8
  • NET Framework 4.6.1

Any idea?


Update 1

Found out that the connection type for MySQLDb is MySql.Data.MySqlClient.MySqlConnection, so that works just fine. But when instantiating SQLDb, the connection type is still MySql.Data.MySqlClient.MySqlConnection instead of System.Data.SqlClient.SqlConnection. How should we fix this?

1
1
4/1/2019 8:08:29 AM

Accepted Answer

The issue is that we are using MySql.Data.Entity.MySqlEFConfiguration (in the app.config) which set the default connection factory to use MySqlConnectionFactory.

The solution is to use a custom DbConfiguration in place of MySql.Data.Entity.MySqlEFConfiguration to deter from setting the default connection factory.

public class MySQLDbConfiguration : DbConfiguration
{
    public MySQLDbConfiguration()
    {
        SetProviderServices(MySqlProviderInvariantName.ProviderName, new MySqlProviderServices());
        SetProviderFactory(MySqlProviderInvariantName.ProviderName, new MySqlClientFactory());
    }
}

Declare the instance as readonly somewhere in the code,

private static readonly MySQLDbConfiguration DBConfig = new MySQLDbConfiguration();

and set the configuration PRIOR TO using any EF features

DbConfiguration.SetConfiguration(DBConfig);

And our app.config now becomes

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
  </startup>

  <connectionStrings>
    <add name="MySQLDb" providerName="MySql.Data.MySqlClient" connectionString="server=localhost;port=3306;database=sakila;uid=some_user;password=some_password"/>
    <add name="SQLDb" providerName="System.Data.SqlClient" connectionString="data source=USER-PC\SQLEXPRESS;initial catalog=MyDatabase;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"/>
  </connectionStrings>
</configuration>

If you're opt to use app.config instead of deriving a custom DbConfiguration, you can do the following

<?xml version="1.0" encoding="utf-8" ?>
<configuration>

  <!-- Alternative to custom DbConfiguration. -->
  <configSections>
    <section name = "entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <entityFramework>
    <providers>
      <provider invariantName = "MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6"/>
    </providers>
  </entityFramework>
  <system.data>
    <DbProviderFactories>
      <remove invariant = "MySql.Data.MySqlClient" />
      <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.10.8.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"/>
    </DbProviderFactories>
  </system.data>

  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
  </startup>

  <connectionStrings>
    <add name="MySQLDb" providerName="MySql.Data.MySqlClient" connectionString="server=localhost;port=3306;database=sakila;uid=some_user;password=some_password"/>
    <add name="SQLDb" providerName="System.Data.SqlClient" connectionString="data source=USER-PC\SQLEXPRESS;initial catalog=MyDatabase;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"/>
  </connectionStrings>
</configuration>
1
4/3/2019 4:33:07 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow