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

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