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:
Next we’ll need a service that can use that DbContext implementation:
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:
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.
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:
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!