ASP.NET MVC: Use Entity Framework to return multiple result sets from a Generic Repository

If you have a simple generic repository, it doesn't make sense to put everything into one repository. In this post, we'll talk about how to use a reader to make specific SQL calls based on a repository.

When building your web apps, I'm sure a lot of you have used a repository pattern in your data layers. As you've used generic repositories, some developers experience problems with stored procedures.

Over the years, I've been using Entity Framework and have seen a number of ways to execute stored procedures. Some didn't work, but EF 6 seems to be better as the versions progress.

As a couple of developers said over the years, the best SQL call is no SQL call at all.

In this post, I'll show you how to take your generic repository and add a function to make your stored procedure a little more flexible.

Using Entity Framework

If you use Entity Framework, you know about the lazy loading of entities when you need them. Every time you access a data object within an data object and it's not available, you make a call to the database which makes it a little redundant and unnecessary.

How many times have you used Entity Framework and noticed an extra call that was made to the database from your entity? There are times when you can't detect when a database call is made from your application unless you run a profiler on SQL Server.

A best way to optimize your application is to only make one call and return multiple result sets. That way, you KNOW you are making that one call with expected query results.

But how do you access multiple record sets?

You simply use a reader.

Modify your Generic Repository

Your generic repository is the first place to start. Lay the groundwork, I say!

Use the generic repository from our post called ASP.NET MVC Controllers: Push the Envelope!

Add a new method to the repository called ExecuteReader.

Repository\Repository.cs

.
.
protected virtual T ExecuteReader<T>(Func<DbDataReader, T> mapEntities, 
    string exec, params object[] parameters)
{
    using (var conn = new SqlConnection(dbContext.Database.Connection.ConnectionString))
    {
        using (var command = new SqlCommand(exec, conn))
        {
            conn.Open();
            command.Parameters.AddRange(parameters);
            command.CommandType = CommandType.StoredProcedure;
            try
            {
                using (var reader = command.ExecuteReader())
                {
                    T data = mapEntities(reader);
                    return data;
                }
            }
            finally
            {
                conn.Close();
            }
 
        }
    }
}
.
.

Now we need to add our calls to each repository.

Updating other repositories

Let's say our FaqRepository needs to load other data besides a list of FAQs. Since our FaqRepository inherits from Repository<T>, we can create a method that loads everything into a complex object.

Each ResultSet requires two methods: the method to prepare the call and the actual reading of the data.

Repository\FaqRepository.cs

.
.
public FaqResultData GetFaqData(int pageNumber, int pageSize)
{
    var totalRecs = new SqlParameter("TotalRecords", DbType.Int32) { Direction = ParameterDirection.Output };
    var index = new SqlParameter { ParameterName = "PageNumber", Value = pageNumber };
    var size = new SqlParameter { ParameterName = "PageSize", Value = pageSize };
 
    var faqData = ExecuteReader(FaqMapper, "[dbo].[GetFaqData]",
        index,
        size,
        totalRecs
        );
    faqData.TotalRecords = (int)totalRecs.Value;
 
    faqData.PagedResults = new PagedList<Faq>(faqData.NonPagedResults, pageNumber, pageSize, faqData.TotalRecords);
    return faqData;
}
.
.

The object FaqResultData is just a POCO that is populates through the second mapper method for each view.

Models\FaqResultData.cs

public class FaqResultData
{
    public int TotalRecords { get; set; }
    public PagedList<Faq> PagedResults { get; set; }
    public Faq FaqItem { get; set; }
    public int SingleValue { get; set; }
    public IEnumerable<Faq> NonPagedResults { get; set; }
}

Mapper method in Repository\FaqRepository.cs

private FaqResultData FaqMapper(DbDataReader dataReader)
{
    var faqData = new FaqResultData
    {
        // Grabbing a recordset (EF)
        NonPagedResults = ((IObjectContextAdapter)dbContext)
            .ObjectContext
            .Translate<Faq>(dataReader)
            .ToList()
    };
    dataReader.NextResult();
 
    // Grabbing a single record (EF)
    faqData.FaqItem = ((IObjectContextAdapter)dbContext)
        .ObjectContext
        .Translate<Faq>(dataReader)
        .ToList()
        .FirstOrDefault();
    dataReader.NextResult();
 
    // Retrieve a list of items using the reader.
    var faqList = new List<Faq>();
    while (dataReader.Read())
    {
        faqList.Add(new Faq
        {
            Answer = dataReader["answer"].ToString(), 
            Question = dataReader["question"].ToString()
        });
    }
    dataReader.NextResult();
 
    return faqData;
}

I added a couple of ways to grab the data from a stored procedure. It makes things a whole hell of a lot easier to pull data with one call as opposed to multiple hits to the database.

Conclusion

In this post, I showed you how to expand on the generic repository to return multiple result sets through stored procedures. This approach can make your data layer really easy to return multiple result sets by using data readers. It allows a number of your custom repositories really simple and manageable.

An additional benefit of using this approach is that you can create a Cache Layer when these exact same parameters are used. You can store the FaqResultData in the Cache to remove even one more database call.

Has anyone else created multiple result sets from stored procedures? Is there a better way? Post your comments below!