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

10 min readJan 4, 2023

In this article, the repository pattern will be discussed, and how it could be implemented in .net 6 and Asp.net WebAPI on Visual Studio 2022. Libraries and packages that will be used in this article will be covered in each section. In part 1 SQL server will be used and in the other 2 parts, MySQL and MongoDB will be used.

Let’s get started.

What is the Repository pattern?

The repository pattern is a class in your Infrastructure layer that helps you implement your data’s persistence and it has lots of benefits in your code structure. For instance, makes easier unit testing, makes readable and clean code and sends information to the database indirectly.

2 important definitions will be mentioned to clearer the repository pattern.

Microsoft definition:

Repositories are classes or components that encapsulate the logic required to access data sources. They centralize common data access functionality, providing better maintainability and decoupling the infrastructure or technology used to access databases from the domain model layer. (1)

Book Patterns of enterprise application architecture (Page 280)

A Repository mediates between the domain and data mapping layers, acting like an in-memory domain object collection. Client objects construct query specifications declaratively and submit them to the Repository for satisfaction. Objects can be added to and removed from the Repository, as they can from a simple collection of objects, and the mapping code encapsulated by the Repository will carry out the appropriate operations behind the scenes. Conceptually, a Repository encapsulates the set of objects persisted in a data store and the operations performed over them, providing a more object-oriented view of the persistence layer. The repository also supports the objective of achieving a clean separation and one-way dependency between the domain and data mapping layers. (2)

After the above definition, let’s implement the repository pattern in .Net 6. The CRUD operation will be implemented. Let’s suppose Music is a model.

First step:

Create a WebAPI project in Visual Studio 2022.

As it is demonstrated in the above image, we have an empty project based on .Net 6. In the next step, the Music model should be created.

Second step:

Create a Music model inside the Models folder. This model will be demonstrated the table inside the SQL database or objects inside the NoSQL database. If a project does not use the repository pattern, it should work with this model directly. Indeed, a Repository pattern could be a bridge between View Model in MVC projects or DTO inside the API projects with the main models.

So, inside the API project, create a Models folder and add 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; }
    }

As demonstrated in the above image, we have used a simple Music model to keep the article simple. However, you can use Domain-Driven design to create your model. Now the database’s connection string should be inserted into the appsettings.json file.

"ConnectionStrings": {
    "DbConnection": "server=.;database=RepositoryPatternAPI_Medium;Trusted_Connection=true;MultipleActiveResultSets=True"
  }

Next, add below Ef core packages. So, from Tools and Nuget Package Manager select Package Manager Console and add the following commands.

Install-Package Microsoft.EntityFrameworkCore -Version 6.0.1
Install-Package Microsoft.EntityFrameworkCore.Design -Version 6.0.1
Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 6.0.1
Install-Package Microsoft.EntityFrameworkCore.Tools -Version 6.0.1

Now we should add the DbContext class that the Entity framework provides it. So, inside the Models folder, add AppDbContext.cs class.

using Microsoft.EntityFrameworkCore;
public class AppDbContext : DbContext
    {
        // Constructor
        public AppDbContext(DbContextOptions<AppDbContext> option) : base(option)
        {

        }

        // override OnModelCreating
        // This method is executed when a model create
        // For instance, Seed method can call inside it and insert data inside the database when model wants to be created.
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }

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

Next, we should Register the connection string inside the Program.cs and pass the parameter of DbConnection that has been inserted inside the appsettings.json file.

// Retrieve ConnectionString from appsettings.json 
var connectionString = builder.Configuration.GetConnectionString("DbConnection");

// Use connectionString variable inside the AddDbContextPool that provided by EntityFrameworkCore
builder.Services.AddDbContextPool<AppDbContext>(option =>
    option.UseSqlServer(connectionString)
);

Lastly, add the following commands inside the Package Manager Console to execute migrations.

Add-Migration InitialMigration \\ Initial Migration or anything you want

Update-Database

As you can see inside the SQL server management or any other management UI, the Music table has been created.

Third step:

This step is the main section of this article that is related to repository patterns. To implement the repository pattern, we need an interface and a class that represents the repository. So, inside the API project, add a Data folder and inside it, we add a new folder again and we call it MusicRepo. Now we can add our interface and repository inside the Music folder.

As shown in the above image, IMusicService represents the interface and MusicService represents the repository.

Now, let’s create the IMusicService interface.

public interface IMusicService
    {
        // Add Music based on the Music model
        Task<Music> AddMusic(Music music);

        // Edit Music based on the Music model
        Task<Music> EditMusic(Music music);

        // Delete a Music based on the id
        bool Delete(string id);

        // Details of a Music based on the id
        Music DetailsMusic(string id);

        // List of all Music
        IEnumerable<Music> ListMusic();
    }

Next, we need the implementation of IMusicService inside the MusicService.cs file.

public class MusicService : IMusicService
    {
        private readonly AppDbContext _appDbContext;

        // Constructor
        // Inject AppDbContext inside the constructor to make access to the AppDbContext 
        public MusicService(AppDbContext appDbContext)
        {
            _appDbContext = appDbContext;
        }
        
        // Add new music
        public async Task<Music> AddMusic(Music music)
        {
            if(music is not null)
            {
                music.Id = Guid.NewGuid().ToString();
                await _appDbContext.Music.AddAsync(music);
                await _appDbContext.SaveChangesAsync();
            }
            return music;
        }
        
        // Delete a music based on the id
        public bool Delete(string id)
        {
            var music = DetailsMusic(id);
            if(music is not null)
            {
                _appDbContext.Music.Remove(music);
                _appDbContext.SaveChanges();
                return true;
            }
            return false;
        }
        
        // Details of a music based on the id
        public Music DetailsMusic(string id)
        {
            return _appDbContext.Music.FirstOrDefault(m => m.Id == id);
        }

        // Edit a music
        public async Task<Music> EditMusic(Music music)
        {
            var musicDetail = DetailsMusic(music.Id);
            if(music is not null)
            {
                // AutoMapper or Mapster or any kind of function that 
                // could map these properties must be used
                musicDetail.Title = music.Title;
                musicDetail.ReleaseDate = music.ReleaseDate;
                musicDetail.Artist = music.Artist;
                musicDetail.Rate = music.Rate;
                await _appDbContext.SaveChangesAsync();
            }
            return musicDetail;
        }
        
        // List of all music
        public IEnumerable<Music> ListMusic()
        {
            return _appDbContext.Music.OrderByDescending(m=>m.ReleaseDate).ToList();
        }
    }

As shown in the above codes, the MusicService has been implemented as the repository based on the IMusicService interface. Now, this service is usable when injected inside the controller. But first of all, we need to register the MusicService inside the Program.cs class.

// Add Music Service to the scoped for implementation based on the requirement. 
// In this example we want to instantiate after each request
// AddSingleton ===> Created 1st time requestd, subsequent request use the same instance
// AddTransient ===> Same within a request but created for every new request
// AddScoped ======> New instance provided everytime, never the same reused.
builder.Services.AddScoped<IMusicService,MusicService>();

Fourth step:

In the final section, this MusicService will be used inside the controller. First of all, in the Controllers folder, add a new controller and call it MusicController, then inject IMusicService which represents the MusicService as an interface.

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

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

Based on the title, this would not be an API course. But this article will cover the main point that we need to implement inside our API service. The first thing that should be considered is using DTO inside the API. So, first of all, we need to know what is DTO and why we should use it inside the API.

What is DTO?

DTO or Data Transfer Object is an object which maps the request inputs that comes from the client to the server to the main model. We can consider various packages and methods to implement this mapping. AutoMapper and Mappster are the most popular packages that are usable for mapping. Another way is using manual mapping.

Why we should use DTO?

One of the important reasons is that the actual model should not be demonstrated to the client. Another reason is that we can use some properties inside the DTO that is not inside our model. For instance, the FullName property will be used inside the DTO and it makes from FirstName and LastName that will receive from the actual model. (3)

So, Inside the API project, add a new folder called DTO and add the MusicDTO.cs as a DTO object related to the incoming request model.

    public class MusicDTO
    {
        // To keep it simple, Music DTO properties are similar to the Music Model
        // Obviously we can add more properties and modify every think 
        // Finally, This DTO object should be mapped by AutoMapper
        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; }
    }

As shown in the MusicService, each CRUD method accepts the Music model. But in the request of API, each endpoint, receive MusicDTO. To map these objects together, AutoMapper is one option that could be added to the API application. So, before writing codes that are related to the CRUD operation inside the API, add the below packages inside the API.

Add the following commands inside the Package Manager Console.

Install-Package AutoMapper -Version 12.0.0
Install-Package AutoMapper.Extensions.Microsoft.DependencyInjection -Version 12.0.0

Now, AutoMapper should be registered inside the Program.cs file.

// Register AutoMapper
builder.Services.AddAutoMapper(AppDomain.CurrentDomain.GetAssemblies());

After registering the AuoMapper inside the Program.cs, it is usable based on a class that inherits from Profile that is come from AutoMapper. So, add a new folder in the API project, call it Mapping, and a new class inside it, and call it MusicMapping.cs and then add the following codes.

using AutoMapper;
public class MusicMapping : Profile
{
    public MusicMapping()
    {
    // CreateMap<Source, Target>
    // This mapping will be used when return the result of a request
    CreateMap<Music, MusicDTO>();

    // This mapping will be used when send a request
    CreateMap<MusicDTO, Music>();
  }
}

In the final step of the implementation, we can use this AutoMapper and send our request to the repository. so, let’s finish Part 1 of this implementation.

Fifth step:

To use AutoMapper inside the controller, it will be required an injection of IMapper. Therefore, Inject IMapper inside the controller’s constructor and insert all actions required for CRUD operation inside the MusicController.cs file.

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

        public MusicController(IMusicService musicService, IMapper mapper)
        {
            _musicService = musicService;
            _mapper = mapper;
        }
        
        // Add Music
        [HttpPost]
        public async Task<IActionResult> Post(MusicDTO musicDTO)
        {
            // map musicDTO to music and send it to the AddMusic function 
            // that comes through the repository
            var result = await _musicService.AddMusic(_mapper.Map<Music>(musicDTO));
            if(result is not null)
            {
                return StatusCode(StatusCodes.Status200OK, _mapper.Map<MusicDTO>(result));
            }
            return StatusCode(StatusCodes.Status400BadRequest);
        }

        // Update Music
        [HttpPut]
        public async Task<IActionResult> Put(MusicDTO musicDTO)
        {
            // map musicDTO to music and send it to the AddMusic function 
            // that comes through the repository
            var result = await _musicService.EditMusic(_mapper.Map<Music>(musicDTO));
            if (result is not null)
            {
                return StatusCode(StatusCodes.Status200OK, _mapper.Map<MusicDTO>(result));
            }
            return StatusCode(StatusCodes.Status400BadRequest);
        }

        // Delete Music
        [HttpDelete]
        public IActionResult Delete(string id)
        {
            var result = _musicService.Delete(id);
            if (result is true)
            {
                return StatusCode(StatusCodes.Status200OK);
            }
            return StatusCode(StatusCodes.Status400BadRequest);
        }

        // Details a music
        [HttpGet("Details")]
        public IActionResult Details(string id)
        {
            var result = _musicService.DetailsMusic(id);
            if (result is not null)
            {
                return StatusCode(StatusCodes.Status200OK, _mapper.Map<MusicDTO>(result));
            }
            return StatusCode(StatusCodes.Status400BadRequest);
        }

        // List Music
        [HttpGet]
        public IActionResult List()
        {
            var result = _musicService.ListMusic();
            if (result.Any())
            {
                return StatusCode(StatusCodes.Status200OK, _mapper.Map<IEnumerable<MusicDTO>>(result));
            }
            return StatusCode(StatusCodes.Status204NoContent);
        }
}

As shown in the above code, in the Post and Put action, 2 times mapping has been implemented and in other parts, it has been used once when the model is going to express a result to the client.

Finally, we can run the application. There are many applications that you can test your endpoints like Swagger or Postman and etc. In this article, the Postman will be used to test the endpoints.