Calling a stored procedure with Entity Framework Code First

Currently I’m doing a project that uses Entity Framework Code First for data access.  Though I’m not 100% convinced that this technology is mature enough, a coworker of mine recommended it.  For the read side of our project we want flattened dto’s that are made to fit our view’s need.  Since some of the views were a bit too complex to write with linq to entities we thought it would be best to write stored procedures for them.

The problem here is that Entity Framework Code First does not support Stored Procedures out of the box.  Well… it actually does, but in such a way that it is indistinguishable from plain ADO.NET.  Yet I wanted a more type safe solution.  I’m still not sure if this is the best solution for this problem but I thought I would share the code with you.

Imagine we have a stored procedure:

DECLARE @Iets int

DECLARE @NogIets bit

SET @Iets = 5

SET @NogIets = 1

EXECUTE [dbo].[sp_TestProcedure]

   @Iets

  ,@NogIets

GO

The standard way of calling the stored procedure in EF Code First would be something like this:

var ietsParameter = new SqlParameter("@Iets",5);

var nogIetsParameter = new SqlParameter("@NogIets",true);

DbContext.Database.SqlQuery<ProcedureResult>("sp_TestProcedure @Iets @Nogiets", ietsParameter, nogIetsParameter);

After creating the database extension you can write a more typesafe solution:

var testProcedureStoredProcedure = new TestProcedureStoredProcedure() { Iets = 5, NogIets = true };

var result = DbContext.Database.ExecuteStoredProcedure(testProcedureStoredProcedure);

The typed stored procedure looks like the following:

public class TestProcedureStoredProcedure : IStoredProcedure<ProcedureResult>

{
    public int Iets { get; set; }
    public bool NogIets { get; set; }
}

Notice that you have to inherit from a generic IStoredProcedure and supply the type of the result of the stored procedure.  The stored procedure class contains properties that are used as the parameters for the execution of the stored procedure.  The extension will use the name of the class to determine which stored procedure to execute.  The default behavior is ‘sp_’+classname but you can edit the source to your own naming convention.

You can download the database extension class here, or get it from github.  Just add a reference to the project and import the namespace to start using the extension method.

There is a drawback however.  The execution time is slower because before it can call the query it has to reflect the properties of the stored procedure class.  It’s still a work in progress but it can be undoubtedly be executed faster by adding some kind of cache for the stored procedure names into it.  If you have any improvements then I would be happy to hear about them!

A small note: if you have any questions about my blog posts or if there are any links broken then please add a comment to the blog post in question.  Mails often get lost in my mailbox and it can take a couple of weeks before I can answer them.

I've created a new extensions project for Entity Framework Code First.  You can read all about it here!