Feb 22, 20224 min read

Soccer Microservice on .Net core, Entity Framework, Mysql and Insomnia

Building Soccer microservice on .Net Core, Entity Framework, Mysql, Insomnia, with Swagger, Auto-mapper, OData, etc.

Introduction

In this article we will show you how to create a webApi and build a CRUD with a mysql database, from inserting data, handling files and using VSC with some extensions. I hope I can help.

Repository https://github.com/agustinafassina/SoccerNetCore

Requirements before starting · Download sdk .net core 3.1 https://dotnet.microsoft.com/download/dotnet/3.1. · Visual Studio Code https://code.visualstudio.com/download · Mysql https://www.mysql.com/ · Workbench or DBeaver (databases administrator). · Postman or Insomnia (I always use Insomnia). · Database:

CREATE DATABASE `soccernetcore`;
CREATE TABLE `player` (
    `Id` int(11) NOT NULL AUTO_INCREMENT,
    `Name` varchar(100) DEFAULT NULL,
    `LastName` varchar(100) DEFAULT NULL,
    `Country` varchar(100) DEFAULT NULL,
    `Logo` varchar(200) DEFAULT NULL,
    `LogoNameUniq` varchar(200) DEFAULT NULL,
    `TeamId` int(11) DEFAULT NULL,
    PRIMARY KEY (`Id`),
    KEY `player_FK` (`TeamId`),
    CONSTRAINT `player_FK` 
    FOREIGN KEY (`TeamId`) 
    REFERENCES `team` (`Id`)
) 
ENGINE=InnoDB AUTO_INCREMENT=57 DEFAULT CHARSET=utf8mb4;
    
CREATE TABLE `team` (
    `Id` int(11) NOT NULL AUTO_INCREMENT,
    `Name` varchar(100) DEFAULT NULL,
    `Country` varchar(50) DEFAULT NULL,
    `Logo` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`Id`)
    ) 
ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

The main thing in this service is the loading of players with their photos.

Detail of the services in which we will be working on:

Project Folder Structure

Let´s go.

We write by console:

dotnet new webapi -o SoccerApi

We validate if everything went well with dotnet run or dotnet build (to compile).

dotnet run

The host is https://localhost:5001/WeatherForecast method Get. It has a WeatherForecastController by default.

Create folder Models

→Player.cs

public class Player
{
  public int Id {get;set;}
  public string Name {get;set;}
  public string LastName {get;set;}
  public string Logo {get;set;}
  public string LogoNameUniq {get;set;}
  public string Country {get;set;}
  [ForeignKey("TeamId")]
  public int TeamId {get;set;}
  public virtual Team Team { get; set; }
}

→Team.cs

public class Team
{
  public Team()
  {
    Players = new HashSet<Player>();
  }
  public int Id {get;set;}
  public string Name {get;set;}
  public string Country {get;set;}
  public virtual ICollection<Player> Players { get; set; }
}

Create folder Repositories

→DependencyInjection.cs


public static class DependencyInjection
{
  public static IServiceCollection AddRepository(this IServiceCollection services, IConfiguration configuration)
  {
    if (configuration.GetValue<bool>("UseInMemoryDatabase"))
    {
      services.AddDbContext<SoccerNetCoreDbContext>(options =>options.UseInMemoryDatabase("Test db"));
    }else{
      services.AddDbContext<SoccerNetCoreDbContext>(options => options.UseMySQL(Environment.GetEnvironmentVariable("ConnectionString") ?? configuration.GetConnectionString("DefaultConnection")));
}
     services.AddScoped<ISoccerNetCoreDbContext>(provider => provider.GetService<ISoccerNetCoreDbContext>());
     services.AddRepositories();
     return services;
   }
   private static IServiceCollection AddRepositories(this IServiceCollection services)
   {
     services.AddTransient<IPlayerRepository, PlayerRepository>();
     services.AddTransient<ITeamRepository, TeamRepository>();
     return services;
   }
}

→Create PlayerRepository.cs

namespace SoccerNetCore.Repository
{
public class PlayerRepository : IPlayerRepository
{ 
  private readonly SoccerNetCoreDbContext _dbContext = null;
  public PlayerRepository(SoccerNetCoreDbContext dbContext)
  {
   _dbContext = dbContext;
  }
  public void Add(Player player)
  {
    _dbContext.Add(player);
    _dbContext.SaveChanges();
  }
  public void Remove(Player player)
  {
    _dbContext.Remove(player);
    _dbContext.SaveChanges();
  }
  public IEnumerable<Player> ListAll()
  {
    var players = _dbContext.Player.Include(c => c.Team).ToList();
    return players.AsEnumerable();
  }
  public Player GetById(int id) => _dbContext.Player.Find(id);
  public void Update(Player player, int playerId)
  {
    var players = _dbContext.Player.Find(playerId);
    players.LastName = player.LastName;
    players.Name = player.Name;
    players.Country = player.Country;
    player.TeamId = player.TeamId;
   _dbContext.SaveChanges();
  }
 }
}

→Create IPlayerRepository.cs

namespace SoccerNetCore.Repository
{
 public interface IPlayerRepository
 {
   void Add(Player player);
   void Remove(Player player);
   IEnumerable<Player> ListAll();
   Player GetById(int id);
   void Update(Player player, int playerId);
 }
}

→Create TeamRepository.cs

public class TeamRepository : ITeamRepository
{
 private readonly SoccerNetCoreDbContext _dbContext = null;
 
 public TeamRepository(SoccerNetCoreDbContext dbContext)
 { 
   _dbContext = dbContext;
 }
 public void Add(Team team)
 {
   _dbContext.Add(team);
   _dbContext.SaveChanges();
 }
 public IEnumerable<Team> ListAll() => _dbContext.Team.ToList();
 public Team GetById(int id) => _dbContext.Team.Find(id);
}

→Create ITeamRepository.cs

public interface ITeamRepository
{
  void Add(Team team);
  IEnumerable<Team> ListAll();
  Team GetById(int id);
}

Create folder Services

→DependencyInjection.cs

public static class DependencyInjection
{
  public static IServiceCollection AddService(this IServiceCollection services)
  {
    services.AddTransient<IPlayerService, PlayerService>();
    services.AddTransient<ITeamService, TeamService>();
    return services;
  }
}

→ Create IPlayerService.cs

public interface IPlayerService
{
  List<Player> GetList();
  Player Get(int id);
  void Post(Player request, Stream file);
  void Delete(int playerId);
  void Update(int playerId, Player body);
}

→Create TeamService.cs

public class TeamService : ITeamService
{
  private readonly ITeamRepository _teamRepository;
  public TeamService(ITeamRepository teamRepository)
  {
   _teamRepository = teamRepository;
  }
  public List<Team> GetList()
  {
   return (List<Team>)_teamRepository.ListAll();
  }
  public Team Get(int TeamId)
  {
    return _teamRepository.GetById(TeamId);
  }
  public void Post(Team request)
  {
    _teamRepository.Add(request);
  }
}

→ Create ITeamService.cs

public interface ITeamService
{
  List<Team> GetList();
  Team Get(int id);
  void Post(Team request);
}

Create folder Context

→Create SoccerNetCoreDbContext.cs

public class SoccerNetCoreDbContext :  DbContext, ISoccerNetCoreDbContext
{
  private readonly IConfiguration _configuration;
  public DbSet<Player> Player { get; set; }
  public DbSet<Team> Team { get; set; }
  public SoccerNetCoreDbContext(IConfiguration configuration) :    base()
  {
    _configuration = configuration;
  }  public SoccerNetCoreDbContext(IConfiguration configuration, DbContextOptions<SoccerNetCoreDbContext> options) : base(options)
  {
    _configuration = configuration;
  }
  public SoccerNetCoreDbContext
  (DbContextOptions<SoccerNetCoreDbContext> options) : base(options)
  {}
  protected override void OnModelCreating(ModelBuilder builder)
  {
     builder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());  base.OnModelCreating(builder);  }  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
    if (!optionsBuilder.IsConfigured)
    {
    optionsBuilder.UseMySQL("server=localhost;port=3306;user=root;password=;database=soccernetcore");    }
  }
}

→ ISoccerNetCoreDbContext.

public interface ISoccerNetCoreDbContext
{
  DbSet<Player> Player { get; set; }
  DbSet<Team> Team { get; set; }
}

Add the services in Startup.cs

public void ConfigureServices(IServiceCollection services)
{
   services.AddHealthChecks()
     .AddDbContextCheck<SoccerNetCoreDbContext>();
   services.SetupSwagger();
   services.AddMappers();
   services.AddRepository(Configuration);
   services.AddODataConfiguration();
   services.AddService();
   services.AddControllers(options => options.EnableEndpointRouting = false).AddNewtonsoftJson(options =>options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore);}
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
  if (env.IsDevelopment())
  {
    app.UseDeveloperExceptionPage();
  }
  app.UseHttpsRedirection();
  app.UseRouting();
  app.UseSwaggerConfig();
  app.UseEndpoints(endpoints =>
  {endpoints.MapControllers();});
  app.UseMvc(routeBuilder =>
  {
routeBuilder.Select().Filter().Expand().OrderBy().Count().MaxTop(null);routeBuilder.MapODataServiceRoute("odata", "odata", ODataConfiguration.GetEdmModel());
routeBuilder.EnableDependencyInjection();});
}

Add the controllers.

PlayersController.cs

[ApiController]
[Route("api/[controller]")]
public class PlayersController : ControllerBase
{
  readonly IPlayerService _playerService;
  private readonly IMapper _mapper;
  public PlayersController(IPlayerService playersService, IMapper mapper)
  {
    _playerService = playersService;
    _mapper = mapper;
  }
  //Decorator for Odata - For example: players?$filter=contains(name, 'Lionel')
[EnableQuery]
[HttpGet]
public ActionResult<IEnumerable<Player>> Get()
{
  return _playerService.GetList();
}[HttpGet("{id}")]
public ActionResult<Player> Get(int id)
{
  return _playerService.Get(id);
}[HttpPost]
public async Task<IActionResult> Post([FromForm] Player request, IFormFile FileLogo)
{
  var bodyPlayer = _mapper.Map<Player>(request);
  bodyPlayer.Logo = FileLogo.FileName;
  var streamFile = new MemoryStream();
  FileLogo.CopyTo(streamFile);
  _playerService.Post(bodyPlayer, streamFile);
  return Ok();
}[HttpDelete("{playerId}")]
public async Task<IActionResult> Delete([FromRoute] int playerId)
{
  try
  {
    _playerService.Delete(playerId);
    return Ok();
  }
  catch (System.Exception ex)
  {
    return BadRequest(ex.Message);
  }
}[HttpPut("{playerId}")]public async Task<IActionResult> Put([FromRoute] int playerId, [FromForm] Player request)
{
  try
  {
    var bodyPlayer = _mapper.Map<Player>(request);
    _playerService.Update(playerId, bodyPlayer);
    return Ok();
  }
  catch (System.Exception ex)
  {
    return BadRequest(ex.Message);
  }
 }
}

TeamsController.cs

[ApiController]
[Route("api/[controller]")]
public class TeamsController : ControllerBase
{
  readonly ITeamService _teamService;
  private readonly IMapper _mapper;
  public TeamsController(IMapper mapper, ITeamService teamService)
  {
    _teamService = teamService;
    _mapper = mapper;
  }[EnableQuery]
[HttpGet]
public ActionResult<IEnumerable<Team>> Get()
{
 return _teamService.GetList();
}[HttpGet("{id}")]
public ActionResult<Team> Get(int id)
{
  return _teamService.Get(id);
}[HttpPost]
public async Task<IActionResult> Post([FromForm] Team request)
{
  var team = _mapper.Map<Team>(request);
  _teamService.Post(team);
  return Ok();
}