Using Entity Framework Core with MySQL for .Net Core 2 Web API

.Net Core 2.0 released and you might using it for learning or actual production purposes.

With .Net Core you usually use Entity Framework Core to communicate with your database, and usually you might use MS SQL Server with this environment. But have you thought about MySql Database? Couldn't find any good resource to how to do this? Then you are in the correct place.

In this post I'll explain you how to use MySql as you Database while using .Net Core backend.

Let's get started.

I'm developing this in a Mac and I'm using Visual Studio Community for Mac.

For this example I'll create a Web API project and get pre entered data from MySql database.

1. Open MySql management tool and do following. 

1.1. Create a database (UserDB)

1.2. Create a table (Users)

1.3. Add fields to Users table (Username, Password, etc.)

1.4. Add some sample data to the table.

2. Create a .Net Core Web API project using Visual Studio. 

I have created as UserLogin API.

2. Add Entity Framework Core for MySql

2.1. Select the created project and goto Project --> Add NuGet Packages...

2.2. Search for MySql and select MySql.Data.EntityFrameworkCore and click on Add Package.

3. Create a Model class for User.
3.1. Add properties according to the table in the database.
3.2. Add [Key] data annotation to define primary key.

using System.ComponentModel.DataAnnotations;

namespace UserLogin.Model
{
    public class User
    {
        [Key]
        public string Username { get; set; }
        public string Password { get; set; }
    }
}

4. Create Context class
4.1. Create a folder called Repository
4.2. Add using statement for Microsoft.EntityFrameworkCore;
4.3. Add empty class (UserContext) and inherit DbContext
4.4. Override OnConfiguring method to use MySql database.
4.5. Add DbSet for Users table
So when we add all together, complete code will be as bellow:

using Microsoft.EntityFrameworkCore;
using UserLogin.Model;

namespace UserLogin.Repository
{
    public class UserContext : DbContext
    {
        public DbSet<User> Users { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseMySQL("server=localhost;Port=3306;database=UserDB;user=root;password=your_password");
        }
    }
}

5. Create a API Controller (UserLogin)
5.1. Add Newtonsoft.Json to the project. We are using to return the user information.

using System;
using System.Linq;
using Microsoft.AspNetCore.Mvc;
using UserLogin.Repository;
using Newtonsoft.Json;

namespace UserLogin.Controllers
{
    [Route("api/[controller]")]
    public class LoginController : Controller
    {
        [HttpGet ("LoginUser")]
        public string LoginUser(string username, string password)
        {
            try
            {
                var dbContext = new UserContext();
                var users = dbContext.User.FirstOrDefault(u => u.Username == username && u.Password == password);
                return JsonConvert.SerializeObject(users);
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
    }
}

6. Go to your favorite API call checker. I'm using Postman.
6.1. Call Login API and it will return user details.

That's it.
Happy Coding... :)