Post
Ask Question

MySQL-Entity-Framework-Code-First For .NET Developers

MySQL is a database management system from Oracle that currently supports Entity Framework through MySQL ADO.NET Connector, which is a fully-managed ADO.NET driver for MySQL. I will go through the steps in setting up MySQL with Entity Framework 6.1.+, using Visual Studio.

PREREQUISITES

  • Visual Studio
    Download free community version here.
  • MySQL Server
    Download here.
  • ADO.NET driver for MySQL (Connector/NET)
    Download here.
  • Microsoft Entity Framework 6.1.+

Adding Entity Framework

Download Entity Framework from NuGet in Visual Studio. This will automatically reference all the required Microsoft Entity Framework assemblies in your project. 

Or 

Run this command in Package Management Console to download Entity Framework.  

Pm> Install-Package EntityFramework

Reference MySQL Assemblies (NB.These assemblies are available after installing MYSQL Connector for .NET)

  • MySQL.Data
  • MySQL.Data.Entity

Setting up the connection string in your Web.Config or App.Config 

Set the connection string to your existing MySQL database 

  1. <connectionStrings>  
  2.    <add name="SellRightDb" providerName="MySql.Data.MySqlClient"connectionString="server=localhost;userid=[your userid];password=[your password];database=sellright;persistsecurityinfo=True"/>  
  3. </connectionStrings>  

Entity Framework configuration 

Configuring MySQL to use Entity Framework adds the block below to your Web.config or App.config. 

  1. <entityFramework>  
  2.     <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />  
  3.     <providers>  
  4.         <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />  
  5.         <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" /> </providers>  
  6. </entityFramework>  

Create a simple Model class 

  1. class Product {  
  2.     public int ProductId {  
  3.         get;  
  4.         set;  
  5.     }  
  6.     public string ProductName {  
  7.         get;  
  8.         set;  
  9.     }  
  10.     public double Price {  
  11.         get;  
  12.         set;  
  13.     }  
  14.     public int Quantity {  
  15.         get;  
  16.         set;  
  17.     }  
  18. }  

Creating Your DBContext class

Import Namespaces
 
  1. using System.Data.Entity;  
  2. using MySql.Data.Entity;  

Configuring your DBContext class 

  1. // Code-Based Configuration and Dependency resolution  
  2. [DbConfigurationType(typeof(MySqlEFConfiguration))]  
  3. class SellRightContext: DbContext {  
  4.     //Add your Dbsets here  
  5.     public DbSet < Product > Products {  
  6.         get;  
  7.         set;  
  8.     }  
  9.     public SellRightContext()  
  10.         //Reference the name of your connection string  
  11.         : base("SellRightDb") {}  
  12. }  
  13. `   

Enable Code First Migration

PM> Enable-Migrations

Add Code First Migration

PM> Add-Migrations [Give Your Migration Settings A name]

Update the database (Specify Verbose to see the SQL Query, i.e being executed). 

PM> Update-Database -Verbose

NB.You should see your created product table.