CRUD operation by Repository pattern using .Net 6, Ef-Core, SQL-Server, MySQL, MongoDB Part-2

8 min readJan 9, 2023

In this article, the repository pattern will be implemented using .Net 6 and MySQL database. The main reason this part has been written is to be more familiar with using other databases using the Repository pattern. If you want to know how we can use SQL Server in .Net core using EF-core and Repository pattern, you could visit Part-1 in Medium, watch the video on YouTube, or review the code inside GitHub. So, let’s get started new part.

What is the Repository pattern?

As mentioned in the previous part, The repository pattern is a class in your Infrastructure layer that helps you implement your data’s persistence and has many benefits in your code structure. The complete explanation will be found in Part 1. (1)

Pre-requisites:

Knowledge of C#
Docker Desktop
VSCode
Packages that will be mentioned throughout the article
MySQL and MySQL workbench (Optional)

So, let’s begin the coding.

First Step:

In the VSCode Create a web API project using the .Net CLI command.

dotnet new webapi

Using this statement, a web API project will be created. Now, MySQL should be run in the Docker Desktop. It could be run in Docker by downloading the image of MySQL or you can install it manually and then use MySQL workbench as a management UI.

Second Step:

Add the below command in the terminal in VSCode to run MySQL into the Docker.

docker run --name mysql1 -p 3306:3306 -e MYSQL_ROOT_HOST=localhost -e MYSQL_ROOT_PASSWORD=yourpassword -d mysql:latest

Pull the MySQL image and Run it inside the Docker

The MySQL image will be pulled and run in the Docker Desktop as shown in the above image.

After running MySQL in Docker, we can run it in the MySQL extension or MySQL workbench. In this tutorial, MySQL extension will be used.

Third Step:

Now add Music Model inside the Models folder. (This is the model that has been used in the previous part to keep the example simple)

Music.cs

public class Music
{
    public string Id { get; set; }
    public string Title { get; set; }
    public DateTime ReleaseDate { get; set; }
    public string Artist { get; set; }
    public int Rate { get; set; 
}

Before the next step, some packages should be added to use Entity Framework Core besides MySQL. Therefore, add the following commands in the terminal.

dotnet add package Microsoft.EntityFrameworkCore — version 6.0.10
dotnet add package Microsoft.EntityFrameworkCore.Design — version 6.0.10
dotnet add package Microsoft.EntityFrameworkCore.Tools — version 6.0.10
dotnet add package Microsoft.EntityFrameworkCore.Relational — version 6.0.10
dotnet add package Pomelo.EntityFrameworkCore.MySql

These packages will help implement CRUD operation using the Entity framework.

Fourth Step:

Now like in the previous part, AppDbContext should be added to the Models folder.

AppDbContext.cs

public class AppDbContext : DbContext
    {
        public AppDbContext(DbContextOptions<AppDbContext> option) : base(option)
        {

        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Seed Data can be executed in this method.
            base.OnModelCreating(modelBuilder);
        }

        public DbSet<Music> Music { get; set; }
    }

The connection string should be added to the appsettings.json file and then registered inside the Program.cs file.

appsettings.json

"ConnectionStrings": {
    "DbConnection" : "server=localhost;port=3306;database=RepositoryPattern;user=root;password=REPO_PATT_MYSQL"
  }

Program.cs

// Add connection string
var connectionString = builder
                        .Configuration
                        .GetConnectionString("DbConnection");

// Set the connectionString to AddDbContext
builder.Services.AddDbContext<AppDbContext>(options=>options.UseMySql(connectionString,ServerVersion.AutoDetect(connectionString)));

Now, add migration to create the database and related table.

dotnet ef migrations add InitialMigration   //Add any name that you want
dotnet ef database update

As you can see the database and Music table based on the model have been created.

Fifth Step:

Now Repository pattern should be implemented. So, In the root of the project, add the Data folder and inside that add the MusicRepo folder and then Add IMusicService.cs file and MusicSerivce.cs file.

IMusicService.cs

public interface IMusicService
{
    Task<Music> AddMusic(Music music);
    Task<Music> EditMusic(Music music);
    bool DeleteMusic(string id);
    IEnumerable<Music> ListMusic();
    Music DetailsMusic(string id);
}

Now add the implementation of the IMusicService.

MusicService.cs

public class MusicService : IMusicService
    {
        private readonly AppDbContext _dbContext;

        // Constructor
        public MusicService(AppDbContext dbContext)
        {
            _dbContext = dbContext;
        }

        // Add a Music
        public async Task<Music> AddMusic(Music music)
        {
            if(music is not null)
            {
                music.Id = Guid.NewGuid().ToString();
                await _dbContext.Music.AddAsync(music);
                await _dbContext.SaveChangesAsync();
            }
            return music;
        }

        // Details a music
        public Music DetailsMusic(string id)
        {
            return _dbContext.Music.FirstOrDefault(m=>m.Id == id);
        }
        // Delete a music
        public bool DeleteMusic(string id)
        {
            var music = DetailsMusic(id);
            if(music is not null)
            {
                _dbContext.Music.Remove(music);
                _dbContext.SaveChanges();
                return true;
            }
            return false;
        }
        
        // Edit a music based on the Id
        public async Task<Music> EditMusic(Music music)
        {
            var musicDetails = DetailsMusic(music.Id);
            if(musicDetails is not null)
            {
                // We can use any mapping function or libraries like Automapper or Mapster
                // Automapper will be used inside the controller
                musicDetails.Title = music.Title;
                musicDetails.ReleaseDate = music.ReleaseDate;
                musicDetails.Artist = music.Artist;
                musicDetails.Rate = music.Rate;
                await _dbContext.SaveChangesAsync();
            }
            return musicDetails;
        }

        // List of all music
        public IEnumerable<Music> ListMusic()
        {
            return _dbContext.Music.OrderByDescending(m=>m.ReleaseDate).ToList();
        }
    }

As shown in the above code, the Repository pattern has been implemented and now we can use it in the API. But before we move forward to the next step, we should Register the MusicService in the Program.cs file.

// Reguster service
builder.Services.AddScoped<IMusicService,MusicService>();

Sixth Step:

Now, we need to add AutoMapper. But before using it, let’s explain about AutoMapper.

What is AutoMapper?

AutoMapper is a library that helps us to map a model to another model. It is a necessary section when a request comes through the API and passes it to the Repositories. Never ever the model should not be used inside the API directly and always AutoMapper or any Mapping libraries or functions should be used to pass the result to the client or get a model from the request that comes through the client.

So, to use AutoMapper, first of all, add the following command in the terminal.

dotnet add package AutoMapper — version 12.0.0
dotnet add package AutoMapper.Extensions.Microsoft.DependencyInjection — version 12.0.0

Now add a folder and call it MapModel and inside this folder, add a class and call it MusicMapper.cs. this class is responsible for mapping.

MusicMapper.cs

public class MusicMapper : Profile
{
    public MusicMapper()
    {
        // ===> Source ==> target
        CreateMap<Music,MusicDTO>();
        CreateMap<MusicDTO,Music>();
    }
}

Now, add a folder in the root of the project and call it DTO.

What is DTO?

Data Transfer Object is responsible to transfer input and output of requests that come through the endpoints. When any mapping functions or libraries have been used in the API, there should be existed a model that translates all inputs and outputs based on an object, and that object is called DTO. it is a class that should be represented to the client. There is a complete explanation of DTO in the Previous part 1.

MusicDTO.cs

public class MusicDTO
{
    // To keep it simple, we are going to use a DTO object like the base model
    // Valication attribues is useful
    public string Id { get; set; }
    [Required]
    public string Title { get; set; }
    public DateTime ReleaseDate { get; set; }
    public string Artist { get; set; }
    public int Rate { get; set; }
}

finally, Register the AutoMapper into the Program.cs file.

builder.Services.AddAutoMapper(AppDomain.CurrentDomain.GetAssemblies());

Now we can use MusicService and AutoMapper in MusicController.

Seventh Step:

First, Inject IMusicService and IMapper in MusicController and implement all endpoints required for CRUD operation.

MusicController.cs

[ApiController]
[Route("api/[controller]")]
public class MusicController : ControllerBase
{
    private readonly IMusicService _musicService;
    private readonly IMapper _mapper;

    public MusicController(IMusicService musicService, IMapper mapper)
    {
        _musicService = musicService;
        _mapper = mapper;
    }

    // This action will return all music as a list object 
    [HttpGet]
    public IActionResult Gets()
    {
        var model = _musicService.ListMusic();
        if (model.Any())
        {
            // Mapping model is a mandatory section to pass the result to the client
            // Never ever pass the base model to the client.
            // That is the main reason that we have to using Automapper or any kind of mapping inside an application
            // IEnumerable has been used due to the return object that is a list of all music
            return StatusCode(StatusCodes.Status200OK, _mapper.Map<IEnumerable<MusicDTO>>(model));
        }
            return StatusCode(StatusCodes.Status204NoContent);
    }

    // This action will return a music based on the id that comes through the API request url
    [HttpGet("Details")]
    public IActionResult Get(string id)
    {
        var model = _musicService.DetailsMusic(id);
        if (model is not null)
        {
            return StatusCode(StatusCodes.Status200OK, _mapper.Map<MusicDTO>(model));
        }
        return StatusCode(StatusCodes.Status404NotFound);
    }

    // This action will return a new music 
    [HttpPost]
    public async Task<IActionResult> Post(MusicDTO musicDTO)
    {
        // Map the model to able the post request to pass a model based on the Music model
        var mapModel = _mapper.Map<Music>(musicDTO);
        var newmusic = await _musicService.AddMusic(mapModel);
        if (newmusic is not null)
        {
            // To pass the new music that has been added, mapping model is a require section of all part of an API
            // When you want pass the result value to the client
            return StatusCode(StatusCodes.Status201Created, _mapper.Map<MusicDTO>(newmusic));
        }
        return StatusCode(StatusCodes.Status400BadRequest);
    }

    // This action will retrun an updated music
    [HttpPut]
    public async Task<IActionResult> Put(MusicDTO musicDTO)
    {
        // Map the model to able the post request to pass a model based on the Music model
        var mapModel = _mapper.Map<Music>(musicDTO);
        var newmusic = await _musicService.EditMusic(mapModel);
        if (newmusic is not null)
        {
           // To pass the new music that has been added, mapping model is a require section of all part of an API
           // When you want pass the result value to the client
           return StatusCode(StatusCodes.Status200OK, _mapper.Map<MusicDTO>(newmusic));
        }
        return StatusCode(StatusCodes.Status400BadRequest);
    }

    // This action will remove a music
    [HttpDelete]
    public IActionResult Delete(string id)
    {
       var music = _musicService.DeleteMusic(id);
       if (music is true)
       {
           return StatusCode(StatusCodes.Status200OK);
       }
       else
       {
            return StatusCode(StatusCodes.Status400BadRequest);
       }
    }
}