Using MySQL with Entity Framework in ASP.NET MVC

Sheonarayan
Posted by in ASP.NET MVC category on for Intermediate level | Points: 250 | Views : 29050 red flag
Rating: 5 out of 5  
 1 vote(s)

In this article, we shall learn how to use MySql database with Entity Framework in ASP.NET MVC application.
Recommendation
Read Implementing Google reCAPTCHA in ASP.NET MVC before this article.

Introduction

MySql is a very favorite database for most of the developers and companies at their initial stages. The main reason behind this is that it is free and easy to use; on top of it the performance of MySql is also not bad.

In fact, the 1st release of this website DotNetFunda.com was also backed with MySql database and it remain on MySql for couple of years.

I must accept that I had enjoyed working with MySql. As time passes, new releases and enhancements are added to MySql and it is now a good choice for .NET developers also to back their application with MySql.

In this article, we shall learn how to use MySql database with Entity Framework in ASP.NET MVC application.

Installing MySql package for Entity Framework

The first step we need to take is to install the MySql.Data.Entities package to our application using Manage NuGet Packages. To do this, right click the project Solution and select Manage NuGet Packages and then write MySql in the search box under Browse tab as displayed in the picture below.

This shows below search result, select MySQL.Data.Entities and then click Install to install this package.



Successful installation should place two .dlls into the project as shown below.




Configuring web.config file

The next step is to configure the web.config file.

1. - ADD
First, add a connection string, in our case it is MySqlConnection. Note that the
providerName is MySql.Data.MySqlClient.

<connectionStrings>
    <add name="MySqlConnection" connectionString="Server=dotnetfunda;Database=dotnetfunda;Uid=dotnetfunda;Pwd=Dotnetfunda;" 
         providerName="MySql.Data.MySqlClient" />
  </connectionStrings>

2. - CHECK
Next, Double check that following configuration is added to the last of the web.config (before </configuration>) file when the MySql package was getting installed by NuGet packages. If it is not, add following yourself.

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

3. ADD
Next, add a separate provider for MySql, note that the type attribute 2nd value after MySqlProviderServiecs must match with the .dll name as displayed in the 2nd picture above.

<providers>
      <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6"/>
    </providers>
Look at below picture where the names of the .dll has been highlighted.


Configuring the IdentityModel for MySql connection


Now, open IdentityModel.cs file from Models folder and change the connection string name as shown below. Remember that the connection string name should exactly match as written in the web.config file under <connectionStrings> tag.

    public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    {
        public ApplicationDbContext()
            : base("MySqlConnection", throwIfV1Schema: false)
        {
            
        }

        public static ApplicationDbContext Create()
        {
            return new ApplicationDbContext();
        }
    }

Now, we are done. It is time to test whether our configurations are working properly or not.

Testing application using MySql with Entity Framework

Following codes are specific to this demo scenario. To test the application, we have created a model corresponding to a sample table in our database.

    [Table("categoryparent")]
    public class categoryparent
    {
        [Key]
        public int AutoID { get; set; }

        public string CategoryParent { get; set; }

        public bool Active { get; set; }
    }
Now, create a controller and try to run the application and see if it works and below is the result.




Hurrah!, we have done this successfully.

Thanks for reading the article, do write your feedback or comments that will help me to improve forthcoming articles.

Page copy protected against web site content infringement by Copyscape

About the Author

Sheonarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com

Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Posted by: Debendra256 on: 6/15/2016 | Points: 25
Nice article sir..
Posted by: Sheonarayan on: 6/16/2016 | Points: 25
Thanks Debendra256.

Keep learning and sharing!

Posted by: Bhuvanesh6 on: 7/21/2016 | Points: 25
Good Article

Login to post response

Comment using Facebook(Author doesn't get notification)