zondag 11 maart 2012

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 { getset; }
    public bool NogIets { getset; }
}

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!

15 opmerkingen:

  1. Thanks, this was perfect for my EF Code First approach and works great.

    BeantwoordenVerwijderen
  2. Deze reactie is verwijderd door een blogbeheerder.

    BeantwoordenVerwijderen
  3. Awesome.. This is exactly what i am looking for.. Thanks for helping me out .. You Rock

    BeantwoordenVerwijderen
  4. Thanks, if you have any improvements then be sure to submit the changes.

    BeantwoordenVerwijderen
  5. what is procedureresult in this post pls let me know

    BeantwoordenVerwijderen
  6. ProcedureResult is the return value of a single record. If you for example have a query

    'SELECT Name, FirstName FROM dbo.Person'

    then your ProcedureResult would be:

    class MyStoredProcedureResult
    {
    public string Name {get;set;}
    public string FirstName {get;set;}
    }

    The return value of your executed stored procedure will be of IEnumerable< ProcedureResult>

    BeantwoordenVerwijderen
  7. Accidently removed some comments, sorry if you're one of them -_-

    BeantwoordenVerwijderen
  8. Don't name your stored procedures starting with "sp_".

    BeantwoordenVerwijderen
    Reacties
    1. http://sqlserverpedia.com/blog/sql-server-bloggers/stored-procedure-performance-using-%E2%80%9Csp_%E2%80%9D-prefix-%E2%80%93-myth-or-fact/

      Verwijderen
    2. Ok, thx for the link. I wasn't aware of that.

      It's open source though so you can use whatever naming convention you'd like.

      Verwijderen
  9. DbContext.Database.ExecuteStoredProcedure here i don't get the
    ExecuteStoredProcedure

    What is the ProcedureResult ?

    please update the code related to calling stored procedure using Entity framework

    BeantwoordenVerwijderen
  10. Deze reactie is verwijderd door de auteur.

    BeantwoordenVerwijderen
  11. Hi,

    I have a custom class like
    class MyStoredProcedureResult
    {
    public string Title {get;set;}
    public string FirstName {get;set;}
    Public List &lt Numbers &gt {get;set;}

    }

    Here Can I get the List Numbers from my SP?
    or your sample can work on simple type of classes?

    My table data is like below
    Person Table
    --------------
    ID Title FirstName
    1 Mr. James
    2 Mr. Roger

    Numbers Table
    ----------------
    PersonId Number
    1 98989898
    1 99999999
    1 14528965
    2 14526398
    2 14587956


    So here how can I get the related data for my class.?

    BeantwoordenVerwijderen