Using Generic Repository and Stored Procedures

Question

I am working on an existing application the uses the Generic Repo pattern and EF6 database first. I am calling a stored proc that returns a complex type that is not an existing entity in my entity models and therefore I am not sure what type to give.

This is how my sp is being called from my service layer

_unitOfWork.Repository<Model>()
            .SqlQuery("sp_Get @FromDateTime, @ToDateTime, @CountyId",
                         new SqlParameter("FromDateTime", SqlDbType.DateTime) { Value = Request.FromDateTime },
                         new SqlParameter("ToDateTime", SqlDbType.DateTime) { Value = Request.TripToDateTime },
                         new SqlParameter("CountyId", SqlDbType.Int) { Value = Convert.ToInt32(Request.County) }
           ).ToList();

Do I create an Entity in my data layer to map to or what is the best approach for stored procedures returning complex types. If so is there custom mapping needed or is it just a case of creating the Entity class

thank you

Solution 1

If you have an entity with those fields you can call SqlQuery method as you show above, if not, then I suggest creating a new class to map the result:

public class Result
{
    public int CountyId { get; set; }

    public DateTime FromDateTime { get; set; }

    public DateTime ToDateTime { get; set; }
}

I don't know how is implemented the UnitOfWork pattern in your case, but I assume that you have access to your Context. In your UnitOfWork class you could create a generic method like this:

public class UnitOfWork 
{
    private YourContext Context { get; set; }

    public DbRawSqlQuery<T> SQLQuery<T>(string sql, params object[] parameters)
    {
       return Context.Database.SqlQuery<T>(sql, parameters);
    }
}

This way, you can execute your store procedures as I show below:

var result= _unitOfWork.SqlQuery<Result>("sp_Get @FromDateTime, @ToDateTime, @CountyId",
                     new SqlParameter("FromDateTime", SqlDbType.DateTime) { Value = Request.FromDateTime },
                     new SqlParameter("ToDateTime", SqlDbType.DateTime) { Value = Request.TripToDateTime },
                     new SqlParameter("CountyId", SqlDbType.Int) { Value = Convert.ToInt32(Request.County) }
       ).ToList();

Solution 2

The purpose of the Repository Pattern is to abstract away the storage & retrieval of data to protect your client code e.g. business layer (service layer in your case) from needing to know anything about how data is persisted. SQL statements, for example, would only exist inside your Repository classes, and not ripple throughout your code.

If you expose SQL, Stored Procedure names and parameters to your client code your are not getting much benefit from the Repository Pattern, and if fact you can't really call it a Repository at all. You lose the benefit of being able to mock the repository and test your business layer independently of your data access layer. This means integration tests (requiring a full database instance) are required to verify business logic.

Consider re-factoring so that you have a CountryRepository class which has a GetCountry(int CountryId, DateTime fromDate, DateTime toDate) method that returns a Country entity, or similar. I think you'll agree the readability of your code will be much improved compared to the code in your question.

public class CountryRepository
{
  public Country GetCountry(int CountryId, DateTime fromDate, DateTime toDate)
  {
    // EF or ADO.NET code here
  }
}

Client code would then be e.g.

var c = unitOfWork.CountryRepository.GetCountry(1, DateTime.Now.AddYears(-1), DateTime.Now);

See also this SO question

Comments

[user2329438]

This approach worked for me. I had to add my new class/entity to my context class also. public DbSet<ModelType> modelName { get; set; } and I had to ensure my new entity had a [key] and inherited from EntityBase

[Adam Plocher]

He's using database first, no DbContext, no DbSet