CODEWITHSUNDEEP
c#asp.netasp.net-core-mvcaspnetboilerplate
user1104087
user1104087

Reputation: 57

Create generic repository for returning data from stored procedure with EFCore

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 )

Upvotes: 2

Views: 3004

Answers (2)

tiennguyen
tiennguyen

Reputation: 165

I think the issue is your custom repository don't get right dbcontext. Look at document Create custom repository

public class SimpleTaskSystemRepositoryBase<TEntity, TPrimaryKey> : EfRepositoryBase<SimpleTaskSystemDbContext, TEntity, TPrimaryKey>
    where TEntity : class, IEntity<TPrimaryKey>
{
    public SimpleTaskSystemRepositoryBase(IDbContextProvider<SimpleTaskSystemDbContext> dbContextProvider)
        : base(dbContextProvider)
    {
    }

    //add common methods for all repositories
}

Note: the custom repository must inherit EfRepositoryBase<SimpleTaskSystemDbContext, TEntity, TPrimaryKey>. You could try this.

Upvotes: 0

aaron
aaron

Reputation: 43098

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();
    }
}

Upvotes: 2

Related Questions