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

Introduction

An Object-Relational Mapping (ORM) framework for.NET called Entity Framework Core (EF Core) enables us to work with databases using.NET objects. Stored procedures are supported by EF Core, even though its primary focus is on entity modeling and LINQ-based query capabilities.

Entity Framework Core's stored procedures give users the ability to have predefined SQL logic run on the database server. Performance may gain from this, particularly for sophisticated queries.

Stored Procedure in the EF Core

Depending on whether your stored procedure executes a command, returns data, or performs an INSERT, UPDATE, DELETE, and SELECT there are a few different ways to call it in Entity Framework Core.

As an illustration

I will create a new Empty Asp.Net Core Web API project.

Prerequisites

We need to install below below-mentioned packages to the application.

• Microsoft.EntityFrameworkCore
• Microsoft.EntityFrameworkCore.SqlServer

Set up the DbContext

Here is the code for the Emp. cs class.

public class Emp
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public EmployeeType Type { get; set; }
    public string Mno { get; set; }
    public decimal Salary { get; set; }
}

Here is the code for the EmpDBContext.cs class.

public class EmpDBContext : DbContext
{
    public EmpDBContext(DbContextOptions<EmpDBContext> dbContextOptions)
        : base(dbContextOptions)
    {
    }
    public DbSet<Emp> Emps { get; set; }
    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
    }
}

As you can see, we have a matching table in the database represented by the Emps DbSet.

We are developing a constructor that takes a parameter called DbContextOptions. This will allow us to pass options to the Startup class.

builder.Services.AddDbContext<EmpDBContext>(
 options => options.UseSqlServer(builder.Configuration.GetConnectionString("SqlServer")));

We're going to add the following connection string to appsettings.json so that I can communicate with the database:

"ConnectionStrings": {
  "SqlServer": "Data Source=server_name;Initial Catalog=EmpDB;Integrated Security=True;TrustServerCertificate=True"
}

Constructing a generic repository

Similar to Entity Framework 6, DbContext is used in EF Core to query a database and aggregate changes that are to be written back to the store collectively.

The fantastic thing about the DbContext class is that it supports generics on methods that we'll use to communicate with the database because it is generic.

Here is the code for the IRepository.cs class.

public interface IRepository
{
    IQueryable<T> GetAll<T>(string sql);
    T Get<T>(string sql, params object[] parameters);
    int Create(string sql, params object[] parameters);
    T Create<T>(string sql, params object[] parameters);
    int Update(string sql, params object[] parameters);
    bool Delete(string sql, params object[] parameters);
}

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

public class Repository : IRepository
{
    private readonly EmpDBContext _empDBContext;
    public Repository(EmpDBContext empDBContext)
    {
        _empDBContext = empDBContext;
    }
    public int Create(string sql, params object[] parameters)
    {
        var result = _empDBContext.Database.ExecuteSqlRaw(sql, parameters);
        return result;
    }
    public T Create<T>(string sql, params object[] parameters)
    {
        var result = _empDBContext.Database.SqlQueryRaw<T>(sql, parameters);
        return result.AsEnumerable().FirstOrDefault()!;
    }
    public bool Delete(string sql, params object[] parameters)
    {
        var result = _empDBContext.Database.ExecuteSqlRaw(sql, parameters);
        return result > 0;
    }
    public T Get<T>(string sql, params object[] parameters)
    {
        var result = _empDBContext.Database.SqlQueryRaw<T>(sql, parameters);
        ArgumentNullException.ThrowIfNull(nameof(result));
        return result.AsEnumerable().FirstOrDefault()!;
    }
    public IQueryable<T> GetAll<T>(string sql)
    {
        return _empDBContext.Database.SqlQueryRaw<T>(sql);
    }
    public int Update(string sql, params object[] parameters)
    {
        return _empDBContext.Database.ExecuteSqlRaw(sql, parameters);
    }
}

Let's develop the Employee repository interface.

public interface IEmployeeRepository : IRepository
{
    //TODO: Write here custom methods that are required for specific requirements.
    bool Any(Expression<Func<Emp, bool>> predicate);
}

Since this inherits from the IRepository interface, all of these methods must be implemented.

All those methods from IRepository will be covered, though, if we build an EmployeeRepository that derives from Repository. Furthermore, we must implement IEmployeeRepository.

Here's how it would appear:

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!