Using MySql with ASP.NET MVC (Entity Framework 6)

Question

My database originally built with MSSQL but recently I've migrated it over to MySql. However, now I need to connect it to my MVC project (existing). I tried doing it by utilizing the Entity Framework wizard but get the following error message.

I read along and found that I need some classes that are required for MySql to work with Visual Studio and added the following NuGet packages. I even installed the MySql Microsoft package (with the connector + MySql for Visual Studio).

Even with all the packages installed I still get this error message. How can I connect my existing MVC project to a MySql database?

Answer 1

First install three Package Manager Console one by one (Tools->NutGet Package Manger->Package Manger Console)

PM> Install-Package EntityFramework
PM> Update-Package EntityFramework
PM> Install-Package MySql.Data.Entity

Web.config

 <connectionStrings>
  <add name="DefaultConnection"
    providerName="MySql.Data.MySqlClient"
    connectionString="Data Source=localhost;port=3306;Initial Catalog=api_db;User Id=root;password=''"/>
</connectionStrings>

Models DbContext

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;

namespace LiteRemit.Models
{
    public class MySqlCon : DbContext
    {
        //MySql Database connection String
        public MySqlCon() : base(nameOrConnectionString: "DefaultConnection") { }
        public virtual DbSet<CustomerModel> Customers { get; set; }
    }
}

Customer Model Class

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;

namespace LiteRemit.Models
{
    [Table("customers")]
    public class CustomerModel
    {
        [Key]
        public int CustomerId { get; set; }
        public string Name { get; set; }
        public string Country { get; set; }
    }
}

Controller Class

using LiteRemit.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace LiteRemit.Controllers
{
    public class HomeController : Controller
    {
        MySqlCon _con;
        public HomeController()
        {
            _con = new MySqlCon();
        }

        public ActionResult Index()
        {
            return View(_con.Customers.ToList());
        }
}
}

View Page

@using LiteRemit.Models
@model IEnumerable<CustomerModel>
  <table border="1">
        @foreach (var item in Model)
        {
            <tr>
                <td>@Html.DisplayFor(modelItem => item.CustomerId)</td>
                <td>
                    @Html.DisplayFor(modelItem => item.Name)
                </td>
                <td>@Html.DisplayFor(modelItem => item.Country)</td>
            </tr>
        }
    </table>

Answer 2

Try to change you web.config, add this

<entityFramework>
    <defaultConnectionFactory type="MySql.Data.Entity.MySqlConnectionFactory, MySql.Data.Entity.EF6" />
    <providers>
      <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6" />
    </providers>
  </entityFramework>

and this to

<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" />
    </DbProviderFactories>
  </system.data>

Answer 3

Maybe you have already found yourself a solution, but if you didn't, try: Tools -> NuGet Package Manager -> Package Manager Console, and update-package entityframework I've been through this myself. ;-)

Answer 4

Check what version of MySql.Data.Entity you have installed. I have 6.9.5, and it worked for me.

To update, run Update-Package MySql.Data.Entity in the Package Manager Console

You may have to add the -Pre for the pre-release version.