Create generic repository for returning data from stored procedure with EFCore

Question

I'm trying to create a generic custom repository using the aspnetboilerplate framework. I want the repository to be able to call a stored procedure in SQL Server and return a set of data for use with an explicit type.

Custom repository:

public class PMStoredProcedureRepository<T> : IPMStoredProcedureRepository<T> where T : class
{
    private MyDbContext Context { get; set; }

    public PMStoredProcedureRepository(IDbContextProvider<MyDbContext> dbContextProvider)
    {
        Context = dbContextProvider.GetDbContext();
    }

    // When you expect a model back (async)
    public IQueryable<T> ExecuteSP(string query, params object[] parameters)
    {
        var type = Context.Set<T>().FromSql(query, parameters);
        return type;
    }
}

What my app service looks like:

public class DashboardAppService : MyAppServiceBase, IDashboardAppService
{
    // Entity repositories
    private readonly IPMStoredProcedureRepository<PMTestSP> _storedProcRepository;

    public DashboardAppService(
        IPMStoredProcedureRepository<PMTestSP> storedProcRepository
        )
    {
        _storedProcRepository = storedProcRepository;
    }

    public List<PMTestSP> GetTestSP()
    {
        var ret = _storedProcRepository.ExecuteSP("exec pme_TestProcedure", new SqlParameter("inputString", "abcde"));
        return ret.ToList();
    }
}

I added the return type to the DbContext as:

public virtual DbSet<PMTestSP> PMTestSP { get; set; }

When I call GetTestSP, I get this error:

ArgumentNullException: Value cannot be null. Parameter name: unitOfWork Abp.EntityFrameworkCore.Uow.UnitOfWorkExtensions.GetDbContext(IActiveUnitOfWork unitOfWork, Nullable multiTenancySide) Abp.EntityFrameworkCore.Uow.UnitOfWorkDbContextProvider.GetDbContext(Nullable multiTenancySide) Abp.EntityFrameworkCore.Uow.UnitOfWorkDbContextProvider.GetDbContext() Company.Name.EntityFrameworkCore.Repositories.PMStoredProcedureRepository..ctor(IDbContextProvider dbContextProvider) in PMStoredProcedureRepository.cs + Context = dbContextProvider.GetDbContext(); Castle.Proxies.PMStoredProcedureRepository`1Proxy..ctor(IInterceptor[] , IDbContextProvider )

Answer 1

Add [UnitOfWork] attribute and make it a virtual method:

[UnitOfWork]
public virtual List<PMTestSP> GetTestSP()
{
    // ...
}

You can inject IUnitOfWorkManager to begin a UnitOfWork explicitly:

public List<PMTestType> GetTestSP()
{
    using (var uow = UnitOfWorkManager.Begin())
    {
        var ret = _storedProcRepository.ExecuteSP("exec pme_TestProcedure", new SqlParameter("inputString", "abcde"));
        uow.Complete();
        return ret.ToList();
    }
}