In this article, you will learn how to connect a .Net Core 3 web API with a MySQL database by using Entity Framework Core.
The original project is available here: NetCore3-EFCore.
Technologies
Topics
Installation
You will need a .Net Core 3 project connected with a MySQL database, so follow the instructions from this repository or from this article.
These are your current packages:
You don't need Dapper anymore, so download the Entity Framework Core package for MySQL, called Pomelo.EntityFrameworkCore.MySql, by Nuget:
Create a folder in the root called Infra with three other folders inside of it: interface, models and repository.
It's time for some coding!
Repository
Inside the repository folder, create a class called DBContext with the following code:
using Microsoft.EntityFrameworkCore;
namespace NetCore3WebAPI.Infra.Repository
{
public class DBContext: DbContext
{
public DBContext(DbContextOptions<DBContext> options) : base(options)
{
}
protected override void OnModelCreating(ModelBuilder modelBuider)
{
base.OnModelCreating(modelBuider);
}
}
}
This class will be responsible for mirroring the database tables into Class objects.
If you have followed the instructions from the article NetCore3 MySQL, your database must have a table called user. Create a class within the Infra/Models folder containing the same properties as the table:
namespace NetCore3WebAPI.Infra.Models
{
public class User
{
public int id { get; set; }
public string name { get; set; }
}
}
Modify the DBContext class by adding a DBSet for the User class:
public DbSet<User> User { get; set; }
This object will be used to manipulate the data from the table.
Inside the OnModelCreating, you need to mirror each model with its respective table. So do this for the User class:
modelBuider.Entity<User>(e =>
{
e
.ToTable("user")
.HasKey(k => k.id);
e
.Property(p => p.id)
.ValueGeneratedOnAdd();
});
This will be the result:
using Microsoft.EntityFrameworkCore;
using NetCore3WebAPI.Infra.Models;
namespace NetCore3WebAPI.Infra.Repository
{
public class DBContext: DbContext
{
public DbSet<User> User { get; set; }
public DBContext(DbContextOptions<DBContext> options) : base(options)
{
}
protected override void OnModelCreating(ModelBuilder modelBuider)
{
modelBuider.Entity<User>(e =>
{
e
.ToTable("user")
.HasKey(k => k.id);
e
.Property(p => p.id)
.ValueGeneratedOnAdd();
});
base.OnModelCreating(modelBuider);
}
}
}
To build the repository, it will be necessary a super class containing the generic methods used by the DBSet object.
Inside the Infra/Interface folder, create two interfaces:
using System;
using System.Collections.Generic;
using System.Linq.Expressions;
namespace NetCore3WebAPI.Infra.Interface
{
public interface IBaseRepository<T> where T : class
{
IEnumerable<T> List(Expression<Func<T, bool>> expression);
bool Any(Expression<Func<T, bool>> expression);
T Save(T entity);
T Update(T entity);
void Delete(T entity);
}
}
using NetCore3WebAPI.Infra.Models;
namespace NetCore3WebAPI.Infra.Interface
{
public interface IUserRepository : IBaseRepository<User>
{
}
}
Inside the Infra/Repository folder, create two classes:
using Microsoft.EntityFrameworkCore;
using NetCore3WebAPI.Infra.Interface;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
namespace NetCore3WebAPI.Infra.Repository
{
public class BaseRepository<T> : IBaseRepository<T> where T : class
{
private DbSet<T> entity_;
protected DBContext context;
public BaseRepository(DBContext context)
{
this.context = context;
entity_ = context.Set<T>();
}
public virtual IEnumerable<T> List(Expression<Func<T, bool>> expression)
{
return entity_.Where(expression).ToList();
}
public bool Any(Expression<Func<T, bool>> expression)
{
return entity_.Any(expression);
}
public T Save(T entity)
{
entity_.Add(entity);
context.SaveChanges();
return entity;
}
public T Update(T entity)
{
entity_.Update(entity);
context.SaveChanges();
return entity;
}
public void Delete(T entity)
{
entity_.Remove(entity);
context.SaveChanges();
}
}
}
using NetCore3WebAPI.Infra.Interface;
using NetCore3WebAPI.Infra.Models;
namespace NetCore3WebAPI.Infra.Repository
{
public class UserRepository : BaseRepository<User>, IUserRepository
{
public UserRepository(DBContext context) : base(context)
{
}
}
}
Inside the Startup's ConfigureServices method, create a service for the IUserRepository and inject the MySQL connection string (from the appsettings.json) into DBContext:
(...)
using Microsoft.EntityFrameworkCore;
namespace NetCore3WebAPI
{
public class Startup
{
(...)
public void ConfigureServices(IServiceCollection services)
{
(...)
services.AddScoped<IUserRepository, UserRepository>();
services.AddDbContext<DBContext>(o => o.UseMySql(Configuration.GetConnectionString("MySQL")));
}
(...)
}
}
Now, we are ready to test our connection!
Inside the UserController class, inject the IUserRepository interface inside the constructor:
private readonly ConnectionStrings con;
private readonly IUserRepository repo;
public UserController(ConnectionStrings c, IUserRepository r)
{
con = c;
repo = r;
}
Import the Linq library and modify the Get method like this:
(...)
using System.Linq;
namespace NetCore3WebAPI.Controllers
{
[Route("api/User")]
[ApiController]
public class UserController : ControllerBase
{
(...)
[HttpGet]
public async Task<IActionResult> Get([FromQuery] User vm)
{
return await Task.Run(() =>
{
var result = repo.List(w =>
(vm.id == 0 || w.id == vm.id)
&&
(vm.name == null || w.name.ToUpper().Equals(vm.name.ToUpper())));
return Ok(result);
//using (var c = new MySqlConnection(con.MySQL))
//{
// var sql = @"SELECT * FROM user
// WHERE (@id = 0 OR id = @id)
// AND (@name IS NULL OR UPPER(name) = UPPER(@name))";
// var query = c.Query<User>(sql, vm, commandTimeout: 30);
// return Ok(query);
//}
});
}
(...)
}
}
You did the same thing as the Dapper code was doing, but without using SQL commands.
Run the web API and access the Swagger by the URL:
http://localhost:53000/swagger/index.html
Test the GET method:
The result will be something like this:
Conclusion
It was much more complex to connect with a database by using Entity Framework than by using Dapper. However, due to the interface/repository layers, the database access has become safer, better structured and object oriented.
Entity Framework is advised for systems in which the business rules are kept in the application layer. This commonly happens when there are databases with different technologies integrated with the web API.
Top comments (2)
Algumas dicas:
1) O repositório no Entity Core pode ser todo Async
2) No List retorne caso queira async ValueTask < List < T > >. Para isso utilize await e ToListAsync
3) No controller não utilize Task.Run e modifique assinatura para retornar async ValueTask < IActionResult >
4) Sempre que possível utilize ValueTask ao invés de Task
5) Sempre que possível utilize async e await
Ao escrever alguns código nessa resposta o site tá cortando algumas palavras (deve ser por achar que são tags Html)
Interessante. Não sabia sobre esse ValueTask. Vou pesquisar.