Call stored procedures using DbContext

Staying Warm and Dry

Sometimes I find myself needing to create a complicated bit of logic in the SQL layer (say, for statistical reports.) Later, perhaps someone will ask to see these statistics exposed on a screen through the UI. Suddenly I need this same logic further up the stack. While I’m a fan of using LINQ, too much code duplication of complex logic can result in a maintenance headache. This is even more true when the duplication crosses languages, as when going from SQL to C#. Staying DRY is a good thing.

While I’d prefer to confine most of my coding logic to C# and keep my databases as simple as possible, sometimes this is not practical. In this article I will demonstrate how to call stored procedures using DbContext.

Putting Things In Context

My organization likes to use dependency injection. We do a certain amount of TDD. So we generally program to interfaces, not to implementations. We also use Entity Framework. Let’s say we had the following code to set up our DbContext:

    public interface IMyAppContext
    {
        IDbSet<Episode> Episodes { get; set; }
        IDbSet<Service> Services { get; set; }
    }

    public class MyAppContext : DbContext, IMyAppContext
    {
        public MyAppContext() : base(MyAppConstants.ConnectionStringName)
        {
        }

        public IDbSet<Episode> Episodes { get; set; }
        public IDbSet<Service> Services { get; set; }
    }
Script 1

Next we’ll need a service that can use that DbContext implementation:

    public class EpisodeService : IEpisodeService
    {
        private readonly IMyAppContext _db;

        public EpisodeService(IMyAppContext db)
        {
            _db = db;
        }

        //public GetEpisodeStatistics(int episodeID)
        //{
            // Some code using _db will go here.
            // Perhaps we'd be tempted to use _db.Episodes
            // and some LINQ statements, for example.
        //}
    }
Script 2

There’s our basic setup. Also, let’s say we already created a stored procedure named spGetEpisodeStatistics (code not shown.) This stored procedure accepts an EpisodeID as a parameter, and is used in a report. It does some gnarly calculations, and returns some rows and columns of data. It has all the information we need for our GetEpisodeStatistics method. Do we really want to code equivalent logic in our business layer?

Asking the Question

Fortunately for us, the Entity Framework has helpfully provided a method named SqlQuery. We can use this method to call our stored procedure.

For this technique to work though, we need to use the Database property that is implemented in the DbContext class. The _db object is only an interface reference. When we try to access this property through _db, it is not there! Fortunately there is a simple fix for this. The Database property already exists on the DbContext base class. All we need to do is expose this property via our interface definition. The new code will look like this:

    public interface IMyAppContext
    {
        IDbSet<Episode> Episodes { get; set; }
        IDbSet<Service> Services { get; set; }

        Database Database { get; }
    }

    public class MyAppContext : DbContext, IMyAppContext
    {
        public MyAppContext() : base(MyAppConstants.ConnectionStringName)
        {
        }

        public IDbSet<Episode> Episodes { get; set; }
        public IDbSet<Service> Services { get; set; }
    }
Script 3

You’ll see the change highlighted on line 6 of Script 3. That’s all there is to it! No changes are needed to the MyAppContext class at all.

We Will Receive

It’s helpful to have a receiving class for the expected query results. Let’s assume that the spGetEpisodeStatistics return the following columns: EpisodeName, MyGnarlyStat, and MyOtherGnarlyStat. We can code up a DTO ready to receive that output.

    public class EpisodeStatsDTO
    {
        string EpisodeName { get; set; }
        float MyGnarlyStat { get; set; }
        float MyOtherGnarlyStat { get; set; }
    }
Script 4

With that in place, we’re ready to move on to our goal.

Call Stored Procedures Using DbContext

Now that we can get to the Database property, and also have the EpisodeStatsDTO defined, we’re ready to call SQLQuery. Let’s go ahead and extend that EpisodeService class:

    public class EpisodeService : IEpisodeService
    {
        private readonly IMyAppContext _db;

        public EpisodeService(IMyAppContext db)
        {
            _db = db;
        }

        public List<EpisodeStatsDTO> GetEpisodeStatistics(int episodeID)
        {
            const string query = "EXEC dbo.spGetEpisodeStatistics @EpisodeID={0}";
            var stats = _db.Database.SqlQuery<EpisodeStatsDTO>(query, episodeID);
            return stats.ToList();
        }
    }
Script 5

There’s a lot going on in these few short lines of code. Let’s break this down a bit.

Line 12 is simply a version of the SQL statement we would need to execute the stored procedure as if we were using SSMS. We’re setting up a type of parameterized query. Notice that we are naming the parameters here. If we needed more parameters, we would just add them right there in the query string.

Line 13 gets to the heart of matter. We are using the generic form of the SqlQuery method. In the type argument we are telling the compiler what return type to expect. We pass in the query string and the required parameter. If we needed more parameters, we’d just put a comma after episodeID and add the next parameter inside the parentheses.

Finally on Line 14 we turn the results of the query into a list, as our method signature requires.

And that’s how easy it is to call stored procedures using DbContext!

Tags: , ,

No comments yet.

Leave a Reply

%d bloggers like this: