none
stored procedure with EF 4.1 RRS feed

  • Question

  •  I have a parameterized stored procedure which is executing a view and return the results. The view is showing results of join of two tables. I need to pass parameters to this stored procedure and call it from MVC3 controller action using EF 4.1 code first approach and return the results. How can I do this. Please suggest step by step.

    Thanks.

    Thursday, June 30, 2011 6:50 PM

Answers

All replies

  • On 6/30/2011 2:50 PM, sparrow37 wrote:
    > I have a parameterized stored procedure which is executing a view and
    > return the results. The view is showing results of join of two tables. I
    > need to pass parameters to this stored procedure and call it from MVC3
    > controller action using EF 4.1 code first approach and return the
    > results. How can I do this. Please suggest step by step.
     
    Code First doesn't have support for sprocs that I have read. You may
    have to resort to executing the sproc the old fashion ADO.NET SQL
    command objects and a datareader way. You can created your own custom
    object just like you would create a POCO, poulate it from the datareader
    and return  the custom object or objects in a List<T>.
     
    Thursday, June 30, 2011 7:34 PM
  • Hi darnold:

     

    I used following code 

     

     var rs = context.Database.SqlQuery<Person>("EXECUTE AuthenticateUser").ToList();

     

    It seems to work but have issue as view returns a column names "Registry Id" where as in my poco class person Id it is defined as:

     

            [Key]

            [Column("Registry ID")]   

            public long RegistryID { get; set; }

     

    Should the number of columns and their names returned by the stored proc be the same as the poco class ?

     

    Thursday, June 30, 2011 8:05 PM
  • On 6/30/2011 4:05 PM, sparrow37 wrote:
    > Hi darnold:
    >
    > I used following code
    >
    > var rs = context.Database.SqlQuery<Person>("EXECUTE
    > AuthenticateUser").ToList();
    >
    > It seems to work but have issue as view returns a column names "Registry
    > Id" where as in my poco class person Id it is defined as:
    >
    > [Key]
    >
    > [Column("Registry ID")]
    >
    > public long RegistryID { get; set; }
    >
    > Should the number of columns and their names returned by the stored proc
    > be the same as the poco class ?
    >
     
    Yeah, if you ever expect to get the entity by its Registry ID if you
    where to query the a collection of Registry objects by its ID.
     
    Thursday, June 30, 2011 8:15 PM
  • Hi,

    You may experience the same problem as described in this post:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/8f71deed-0441-4e77-b6ad-f7b1ca08e435/#2e6453dd-e061-4ec7-a352-2df414f8802c

    The SQL Query call is not suited for use with Code First classes with attributed Column names.

    Try my solution there and it may work.


    --Rune
    Thursday, June 30, 2011 8:24 PM