8.2 Entity Framework 6 Support

MySQL Connector/Net integrates support for Entity Framework 6.0 (EF6), but also offers support for Entity Framework 5 (EF5). This chapter explains the new features in EF6 implemented in MySQL Connector/Net.

In this section:

Requirements for EF6

  • MySQL Connector/Net 6.8.x , 6.9.x, 6.10.x, or 8.0.x

  • MySQL Server 5.5 or higher

  • Entity Framework 6 assemblies

  • .NET Framework 4.0 or higher (.NET Framework 4.5.1 or higher is required for MySQL Connector/Net 6.10 and 8.0)

Configuration

To configure MySQL Connector/Net support for EF6:

  1. Edit the configuration sections in the app.config file to add the connection string and the MySQL Connector/Net provider for EF6:

    <connectionStrings>
        <add name="MyContext" providerName="MySql.Data.MySqlClient"
            connectionString="server=localhost;port=3306;database=mycontext;uid=root;password=********"/>
    </connectionStrings>
    <entityFramework>
        <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>
  2. Apply the reference for MySql.Data.Entity automatically or manually as follows:

    • Install the MySql.Data.Entity NuGet package to add this reference automatically within app.config or web.config file during the installation. For more information about the MySql.Data.Entity NuGet package and its uses, see https://www.nuget.org/packages/MySql.Data.Entity/.

      Proceed to step 3.

    • Otherwise, add a reference for the MySql.Data.Entity.EF6 assembly to your project. Depending on the .NET Framework version used, the assembly is taken from either the v4.0 or the v4.5 folder).

      Unless MySQL Connector/Net was installed with the standalone MSI or MySQL Installer, which adds the reference, insert the following data provider information into the app.config or web.config file:

      <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.9.9.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
         </DbProviderFactories>
      </system.data>
      Important

      Always update the version number to match the one in the MySql.Data.dll assembly.

  3. Set the new DbConfiguration class for MySql. This step is optional but highly recommended, because it adds all the dependency resolvers for MySql classes. This can be done in three ways:

    • Adding the DbConfigurationTypeAttribute on the context class:

      [DbConfigurationType(typeof(MySqlEFConfiguration))]
    • Calling DbConfiguration.SetConfiguration(new MySqlEFConfiguration()) at the application start up.

    • Set the DbConfiguration type in the configuration file:

      <entityFramework codeConfigurationType="MySql.Data.Entity.MySqlEFConfiguration, MySql.Data.Entity.EF6">

    It is also possible to create a custom DbConfiguration class and add the dependency resolvers needed.

EF6 Features

Following are the new features in Entity Framework 6 implemented in MySQL Connector/Net:

  • Async Query and Save adds support for the task-based asynchronous patterns that have been introduced since .NET 4.5. The new asynchronous methods supported by MySQL Connector/Net are:

    • ExecuteNonQueryAsync

    • ExecuteScalarAsync

    • PrepareAsync

  • Connection Resiliency / Retry Logic enables automatic recovery from transient connection failures. To use this feature, add to the OnCreateModel method:

    SetExecutionStrategy(MySqlProviderInvariantName.ProviderName, () => new MySqlExecutionStrategy());
  • Code-Based Configuration gives you the option of performing configuration in code, instead of performing it in a configuration file, as it has been done traditionally.

  • Dependency Resolution introduces support for the Service Locator. Some pieces of functionality that can be replaced with custom implementations have been factored out. To add a dependency resolver, use:

    AddDependencyResolver(new MySqlDependencyResolver());

    The following resolvers can be added:

    • DbProviderFactory -> MySqlClientFactory

    • IDbConnectionFactory -> MySqlConnectionFactory

    • MigrationSqlGenerator -> MySqlMigrationSqlGenerator

    • DbProviderServices -> MySqlProviderServices

    • IProviderInvariantName -> MySqlProviderInvariantName

    • IDbProviderFactoryResolver -> MySqlProviderFactoryResolver

    • IManifestTokenResolver -> MySqlManifestTokenResolver

    • IDbModelCacheKey -> MySqlModelCacheKeyFactory

    • IDbExecutionStrategy -> MySqlExecutionStrategy

  • Interception/SQL logging provides low-level building blocks for interception of Entity Framework operations with simple SQL logging built on top:

    myContext.Database.Log = delegate(string message) { Console.Write(message); };
  • DbContext can now be created with a DbConnection that is already opened, which enables scenarios where it would be helpful if the connection could be open when creating the context (such as sharing a connection between components when you cannot guarantee the state of the connection)

      [DbConfigurationType(typeof(MySqlEFConfiguration))]
      class JourneyContext : DbContext
      {
        public DbSet<MyPlace> MyPlaces { get; set; }
    
        public JourneyContext()
          : base()
        {
    
        }
    
        public JourneyContext(DbConnection existingConnection, bool contextOwnsConnection)
          : base(existingConnection, contextOwnsConnection)
        {
    
        }
      }
    
      using (MySqlConnection conn = new MySqlConnection("<connectionString>"))
      {
        conn.Open();
        ...
    
        using (var context = new JourneyContext(conn, false))
        {
          ...
        }
      }
  • Improved Transaction Support provides support for a transaction external to the framework as well as improved ways of creating a transaction within the Entity Framework. Starting with Entity Framework 6, Database.ExecuteSqlCommand() will wrap by default the command in a transaction if one was not already present. There are overloads of this method that allow users to override this behavior if wished. Execution of stored procedures included in the model through APIs such as ObjectContext.ExecuteFunction() does the same. It is also possible to pass an existing transaction to the context.

  • DbSet.AddRange/RemoveRange provides an optimized way to add or remove multiple entities from a set.

Code First Features

Following are new Code First features supported by MySQL Connector/Net:

  • Code First Mapping to Insert/Update/Delete Stored Procedures supported:

    modelBuilder.Entity<EntityType>().MapToStoredProcedures();
  • Idempotent migrations scripts allow you to generate an SQL script that can upgrade a database at any version up to the latest version. To do so, run the Update-Database -Script -SourceMigration: $InitialDatabase command in Package Manager Console.

  • Configurable Migrations History Table allows you to customize the definition of the migrations history table.

Example for Using EF6

Model:

using MySql.Data.Entity;
using System.Data.Common;
using System.Data.Entity;

namespace EF6
{
  // Code-Based Configuration and Dependency resolution
  [DbConfigurationType(typeof(MySqlEFConfiguration))]
  public class Parking : DbContext
  {
    public DbSet<Car> Cars { get; set; }

    public Parking()
      : base()
    {

    }

    // Constructor to use on a DbConnection that is already opened
    public Parking(DbConnection existingConnection, bool contextOwnsConnection)
      : base(existingConnection, contextOwnsConnection)
    {

    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      base.OnModelCreating(modelBuilder);
      modelBuilder.Entity<Car>().MapToStoredProcedures();
    }
  }

  public class Car
  {
    public int CarId { get; set; }

    public string Model { get; set; }

    public int Year { get; set; }

    public string Manufacturer { get; set; }
  }
}

Program:

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;

namespace EF6
{
  class Example
  {
    public static void ExecuteExample()
    {
      string connectionString = "server=localhost;port=3305;database=parking;uid=root";

      using (MySqlConnection connection = new MySqlConnection(connectionString))
      {
        // Create database if not exists
        using (Parking contextDB = new Parking(connection, false))
        {
          contextDB.Database.CreateIfNotExists();
        }

        connection.Open();
        MySqlTransaction transaction = connection.BeginTransaction();

        try
        {
          // DbConnection that is already opened
          using (Parking context = new Parking(connection, false))
          {

            // Interception/SQL logging
            context.Database.Log = (string message) => { Console.WriteLine(message); };

            // Passing an existing transaction to the context
            context.Database.UseTransaction(transaction);

            // DbSet.AddRange
            List<Car> cars = new List<Car>();

            cars.Add(new Car { Manufacturer = "Nissan", Model = "370Z", Year = 2012 });
            cars.Add(new Car { Manufacturer = "Ford", Model = "Mustang", Year = 2013 });
            cars.Add(new Car { Manufacturer = "Chevrolet", Model = "Camaro", Year = 2012 });
            cars.Add(new Car { Manufacturer = "Dodge", Model = "Charger", Year = 2013 });

            context.Cars.AddRange(cars);

            context.SaveChanges();
          }

          transaction.Commit();
        }
        catch
        {
          transaction.Rollback();
          throw;
        }
      }
    }
  }
}

User Comments
Sign Up Login You must be logged in to post a comment.