none
CTP5 - DbContext + Stored Procedure

    Question

  • I'm using Code First and the DbContext.

    Basically, I do need to call a stored procedure or user function, and it returns a flat recordset.

    Can you please give me an example of how to do this? There are examples of EF4 + Stored Procedures, but I'm using the repository pattern in my code and it holds a DbContext... so what does the API / attributes / mapping look like to let me call a stored procedure, and how do I consume the results?

    My understanding is that Stored Procedure is not supported??? What does this mean, exactly? That we can call the stored proc, it returns the result in ??? In LINQ 2 SQL, it was in an ISingleResult. What's the equivalent for us?

    Please don't say it isn't supported -- I need a valid mechanism/work-around. I'm happy to massage the data all I want. I'll get fired for suggesting we use EF4 Code-First only to tell my bosses that it is impossible to call a stored proc. Code samples would be greatly appreciated.

    Thanks,

    Ray

    Thursday, January 20, 2011 12:31 AM

Answers

  • Hi Ray,

    We don't have native support for sprocs in CTP5, but you can still use a sproc to get back entity objects using the DbSet.SqlQuery method.

    Something like:
        var people = context.People.SqlQuery("EXECUTE [dbo].[GetAllPeople]");

    The objects that are returned will still have their changes tracked by the context, the same as objects fetched by LINQ.

    ~Rowan

    Friday, January 21, 2011 8:49 PM
    Moderator

All replies

  • Hi Ray,

    We don't have native support for sprocs in CTP5, but you can still use a sproc to get back entity objects using the DbSet.SqlQuery method.

    Something like:
        var people = context.People.SqlQuery("EXECUTE [dbo].[GetAllPeople]");

    The objects that are returned will still have their changes tracked by the context, the same as objects fetched by LINQ.

    ~Rowan

    Friday, January 21, 2011 8:49 PM
    Moderator
  • Holly Molly Rowan!

    CTP5 doesn't support SP's? Yet, the current EF (3.5?) does support it? I don't get it :( it's like .. this is going backwards?! 

    With that workaround, above .. that's doesn't look like it can handle complex return types.

    Please say this will be included soon/next release .... ??

     

    /me is *very* scared .. and doesn't want to roll back to the current EF :(


    -Pure Krome-
    Tuesday, January 25, 2011 4:29 AM
  • Hi,

    Full SP support is not going to be in the first RTM of Code First. We realize that SP support is important to folks mapping to an existing database and it's a high priority on our task list. We have been getting strong feedback from the community to ship what we have, which is why we've drawn a line and will ship our current feature set.

    You can materialize to complex types in CTP5 using the context.Database.SqlQuery<T>() method.

    ~Rowan

    Tuesday, January 25, 2011 8:49 PM
    Moderator
  • If we have imported the stored procedure in your model for existing database, we can drop down to the objectcontext and use ExecuteFunction to grab the results.

    Is that correct or would there be something that wont work if we drop down to the ObjectContext.


    Zeeshan Hirani Entity Framework 4.0 Recipes by Apress
    http://weblogs.asp.net/zeeshanhirani
    Wednesday, January 26, 2011 6:23 AM
  • @Rowan - cheers :) We'll give that a go, also.

     

    @Zeeshan - Good question dude! (and great book - I bought a digital version, last year. very nice!)


    -Pure Krome-
    Wednesday, January 26, 2011 10:52 PM
  • any news on the return parameters in stored procedures ? thanks in advance .


    Tuesday, February 01, 2011 2:27 PM
  • Hi,

    Yes, if you are using Database First or Model First then the stored procedure will be there and you can drop down to ObjectContext to use it.

    For Code First there is currently no way to get the stored procedure into your model.

    ~Rowan

    Friday, February 04, 2011 6:35 PM
    Moderator
  • Hi,

    SqlCommand accepts a array of parameters, these can be either normal values or SqlParameter instances. SqlParameter supports specifying our parameters.

    (SqlCommand is basically just a thin wrapper over DbCommand.ExecuteNonQuery)

    ~Rowan

    Friday, February 04, 2011 6:42 PM
    Moderator
  • I know this is a dated discussion, but I was just curious if there's a way yet to use Code First (dbContext) and SPs? If not, when's the planned release schedule?

     

    Thanks.

    Tuesday, November 08, 2011 11:31 AM