Calling Stored Procedure in Entity Framework using Aspnet boilerplate

Question

I am using aspnetboilerplate template

i have a student service class. i am getting a student profile List from stored procedure. how can i call a stored procedure in aspnetboilerplate template

  public class StudentRepository : TabonoRepositoryBase<User, long>
    {
        private readonly IActiveTransactionProvider _transactionProvider;

        public StudentRepository(IDbContextProvider<TabonoDbContext> dbContextProvider, IActiveTransactionProvider transactionProvider)
            : base(dbContextProvider)
        {
            _transactionProvider = transactionProvider;
        }

        //TODO: Make async!
        public async Task<int> GetProfileCompletePercentage(int studentid)
        {
            EnsureConnectionOpen();

            using (var command = CreateCommand("Sp_GetStudentprofilepercentage", CommandType.StoredProcedure, new SqlParameter("StudentId", studentid)))
            {
                using (var dataReader = await command.ExecuteReaderAsync())
                {
                    while (dataReader.Read())
                    {
                        return Convert.ToInt16(dataReader["TotalPer"].ToString());
                    }
                    return 0;
                }
            }
        }

        private DbCommand CreateCommand(string commandText, CommandType commandType, params SqlParameter[] parameters)
        {
            var command = Context.Database.GetDbConnection().CreateCommand();

            command.CommandText = commandText;
            command.CommandType = commandType;
            command.Transaction = GetActiveTransaction();

            foreach (var parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }

            return command;
        }

        private void EnsureConnectionOpen()
        {
            var connection = Context.Database.GetDbConnection();

            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
        }

        private DbTransaction GetActiveTransaction()
        {
            return (DbTransaction)_transactionProvider.GetActiveTransaction(new ActiveTransactionProviderArgs
            {
                {"ContextType", typeof(TabonoDbContext) },
                {"MultiTenancySide", MultiTenancySide }
            });
        }
    }

And this is the service class

 public class StudentService : AsyncCrudAppService<StudentCore, StudentDto, int, PagedResultRequestDto, StudentCreateDto, StudentUpdateDto>, IStudentService
    {
        public readonly IRepository<StudentCore> _studentRepository;
        private readonly UserManager _userManager;
        private readonly IStudentService _studentservice;

        public StudentService(IRepository<StudentCore> repository, UserManager um, IStudentService studentservice) : base(repository)
        {
            _studentRepository = repository;
            _userManager = um;
            _studentservice = studentservice;
        }
 public Task GetProfileCompletePercentage(int studentid)
        {
          return  _studentservice.GetProfileCompletePercentage(studentid);
        }
    }

Answer 1

Create an interface:

public interface IStudentRepository : IRepository<StudentCore>
{
    Task<int> GetProfileCompletePercentage(int studentid);
}

Implement the interface:

public class StudentRepository : TabonoRepositoryBase<StudentCore>, IStudentRepository
{
    // ...
}

Inject the interface and call the method:

public class StudentService : ...
{
    private readonly IStudentRepository _studentRepository;

    public StudentService(IStudentRepository repository) : base(repository)
    {
        _studentRepository = repository;
    }

    public Task GetProfileCompletePercentage(int studentid)
    {
        return _studentRepository.GetProfileCompletePercentage(studentid);
    }
}

Note: StudentService must not inject IStudentService in constructor → infinite recursion!

For reference: https://www.codeproject.com/Articles/1199648/Using-Stored-Procedure-User-Defined-Function-and-V