Generic Repository with EF Core Store Procedure in .NET Core 8

public class EmployeeRepository : Repository.StoreProcedure.Repository, IEmployeeRepository
{
    private readonly EmpDBContext _dbContext;
    public EmployeeRepository(EmpDBContext empDBContext)
        : base(empDBContext)
    {
        _dbContext = empDBContext;
    }
    // TODO: Write here custom methods that are required for specific requirements.
    public bool Any(Expression<Func<Emp, bool>> predicate)
    {
        return _dbContext.Emps.Any(predicate);
    }
}

Here is the code for the EmployeeDto.cs file.

public record EmployeeDto
{
    public Guid Id { get; init; }
    public string Name { get; init; }
    public EmployeeType Type { get; init; }
    public string Mno { get; init; }
    public decimal Salary { get; init; }
}

Let’s develop the IEmployeeService.cs class file.

public interface IEmployeeService
{
    Guid Create(Employee employee);
    int Update(Employee employee);
    bool Delete(Guid id);
    EmployeeDto GetById(Guid id);
    List<EmployeeDto> GetAll();
    bool Exists(Expression<Func<Emp, bool>> predicate);
}

Here is the code for the implementation of the IEmployeeService interface.

public class EmployeeService : IEmployeeService
{
    private readonly IEmployeeRepository _employeeRepository;
    public EmployeeService(IEmployeeRepository employeeRepository)
    {
        _employeeRepository = employeeRepository;
    }
    public Guid Create(Employee employee)
    {
        var parameters = new SqlParameter[]
        {
            new("@Name", employee.Name),
            new("@Type", employee.Type),
            new("@Mno", employee.Mno),
            new("@Salary", employee.Salary)
        };
        var result = _employeeRepository.Create<Guid>(
            $"[dbo].[CreateEmployee] {string.Join(", ", parameters.Select(x => x.ParameterName))}", 
            parameters
        );
        return result;
    }
    public int Update(Employee employee)
    {
        var parameters = new SqlParameter[]
        {
            new("@Id", employee.Id),
            new("@Name", employee.Name),
            new("@Type", employee.Type),
            new("@Mno", employee.Mno),
            new("@Salary", employee.Salary)
        };
        var result = _employeeRepository.Update(
            $"[dbo].[UpdateEmployee] {string.Join(", ", parameters.Select(x => x.ParameterName))}", 
            parameters
        );
        return result;
    }
    public bool Delete(Guid id)
    {
        var sqlParameter = new SqlParameter("@Id", id);
        var result = _employeeRepository.Delete("[dbo].[DeleteEmployee] @Id", sqlParameter);
        return result;
    }
    public EmployeeDto GetById(Guid id)
    {
        var sqlParameter = new SqlParameter("@Id", id);
        var emp = _employeeRepository.Get<Emp>("[dbo].[GetEmployeeById

We have to register the Repository, EmployeeRepository, and EmployeeService in the DI container for usage in our application; otherwise, it will not resolve those dependencies and give an error in the runtime.

Here is the code to register the dependencies in the DI container.

builder.Services.AddScoped<IRepository, Repository>();
builder.Services.AddScoped<IEmployeeRepository, EmployeeRepository>();
builder.Services.AddScoped<IEmployeeService, EmployeeService>();

Here is the code for the EmployeeController.cs class, inside that, inject the IEmployeeService interface, resolve the dependency on the constructor side, and use it in our application.

[Route("api/[controller]")]
[ApiController]
public class EmployeeController : ControllerBase
{
    private readonly IEmployeeService _employeeService;
    public EmployeeController(IEmployeeService employeeService)
    {
        _employeeService = employeeService;
    }
    [HttpGet("{id}")]
    public IActionResult GetById(Guid id)
    {
        var emp = _employeeService.GetById(id);
        if (emp == null)
        {
            return NotFound();
        }
        return Ok(emp);
    }
    [HttpGet]
    public IActionResult GetAll()
    {
        return Ok(_employeeService.GetAll());
    }
    [HttpPost]
    public IActionResult Create(Employee employee)
    {
        if (employee == null)
        {
            return BadRequest();
        }
        if (_employeeService.Exists(x => x.Name == employee.Name))
        {
            return BadRequest("Employee already exists.");
        }
        employee.Id = _employeeService.Create(employee);
        return CreatedAtAction(
            nameof(GetById),
            new { employee.Id },
            employee);
    }
    [HttpPut]
    public IActionResult Update(Employee employee)
    {
        if (employee == null)
        {
            return BadRequest();
        }
        if (!_employeeService.Exists(x => x.Id == employee.Id))
        {
            return NotFound("Employee not exists.");
        }
        if (_employeeService.Exists(x => x.Name == employee.Name && x.Id != employee.Id))
        {
            return BadRequest("Employee already exists.");
        }
        _employeeService.Update(employee);
        return NoContent();
    }
    [HttpDelete]
    public IActionResult Delete(Guid id)
    {
        var emp = _employeeService.GetById(id);
        if (emp == null)
        {
            return NotFound();
        }
        _employeeService.Delete(id);
        return NoContent();
    }
}

Let's run the application, and we will perform the get-employee operation.

You need to perform all the operations (POST, PUT, DELETE, GET), then you need to download the sample code, unzip it, and open it in Visual Studio 2022.

Change the database connection string, run the application, and play around in this application as well.

The only requirement for this sample code is that it be written using the .NET 8 version, as the sample application was. If you haven't already, I would prefer that you install the .NET 8 version on your computer before attempting to use it.

We learned the new technique and evolved together.

Happy coding!