how to call stored procedure using ef core and clean architecture

Question

Using asp.net ef core, clean architecture, repository/service & uow pattern

Considering that insert should not be done in repository class and On the other hand in the service layer we can not call dbContext directly Suppose we want to use stored procedures or raw queries in some part of the project,

For example, we want to do what is said in the link below

https://www.entityframeworktutorial.net/faq/how-to-set-explicit-value-to-id-property-in-ef-core.aspx

Or call a Stored Precedure that does the insert by Database.ExcecuteSqlCommand

How can we do this without disturbing the architecture? Where we should call the SP?

My project architecture is so alike this tutorial

Repository Pattern Done Right

Edit: for more clarification: this is add method of user repository class:

 public class UserRepository : IUserRepository
{
    private readonly tablighkadeDbContext _dbContext;

    public UserRepository(tablighkadeDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public async Task Add(User user)
    {
            Entities.User newUser = new Entities.User
            {
                
                Email = user.Email,
                ...
            };
           await _dbContext.AddAsync(newUser);          
    }

CompeleteAsync method in unitOfWork class:

 public async  Task CompeleteAsync()
    {
        return await _dbContext.SaveChangesAsync();
    }

and finally I have service class in core layer, I persist entity like this:

 public async Task RegisterUser(UserAddVM userAddVM)
    {           
        
        var user = await _unitOfWork.Users.GetByEmail(userAddVM.Email);
        if (user != null)
        {
         //...
        }             
        else {
            var u = new User
            {
                Email = userAddVM.Email,
                //...               
            };
           await _unitOfWork.Users.Add(u);
           await _unitOfWork.CompeleteAsync();              
        }
        
    }

But I don't know what to do the same using stored procedure,

considering we should not save/update entities in repository class based on this article and some other articles I read

https://programmingwithmosh.com/net/common-mistakes-with-the-repository-pattern/

user_6748190 · Answer

You can extend generic EFRepository class to have your own implementation. You should expose this implementation via Interface.

Here is an example of OrderRepository: https://github.com/dotnet-architecture/eShopOnWeb/blob/master/src/Infrastructure/Data/OrderRepository.cs

In this example they have implemented GetByIdWithItemsAsync(int id), similarly you can have your own implementation.

You can add a method in UserRepository. If your insert logic is in Stored Proc then you don't require to commit using _dbContext.SaveChanges(). Commit will happen in Stored Proc which will not be in your control. To refresh the state, you can have select for inserted entity at the end of stored proc and which should update the persistent entity .

public async Task Add(User user)
{
    var user = _dbContext.Users.SqlQuery("dbo.AddUser @p0 ", user.Name).Single();
}

how to call stored procedure using ef core and clean architecture

1 Answers