stored procedure with EF 4.1

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.

Hi : I used following code var rs = context.Database.SqlQuery("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 ? — DotnetSparrow, Jun 30 '11 at 20:06

score 0 · Accepted Answer · answered Jul 03 '11 at 10:04

0

I don't know what part of "Code First doesn't support stored procedures" people don't understand, but this keeps coming up and it shouldn't. You can muddle your way around it (kind of), but really if you need to execute stored procedures then you shouldn't be using code first at this point.

answered Jul 03 '11 at 10:04

Tridus

5,021
1
19
19

1

Just because Microsoft didn't include it in 4.1 doesn't mean people don't need it. For my own similar purposes, I already have all my connection information tied to my POCO set up. The last thing I want to do is spool up an entirely separate database pattern just to execute a few arbitrary SP's that don't fit my POCO model. – Tom Halladay Sep 22 '11 at 15:59
I never said people don't need it. But need it or not, it's not there. – Tridus Sep 23 '11 at 09:32

score 0 · Answer 2 · edited May 23 '17 at 12:22

You might check this post as well. It might help with at least the first step: executing a proc with parameters in EF 4.1

How to use DbContext.Database.SqlQuery<TElement>(sql, params) with stored procedure? EF Code First CTP5

My Code:

context.Database.SqlQuery<EntityType>(
    "EXEC ProcName @param1, @param2", 
    new SqlParameter("param1", param1), 
    new SqlParameter("param2", param2));

score 0 · Answer 3 · answered Mar 27 '12 at 08:38

I'm not sure if this will help you but here's how I call an Oracle proc using EF 4.1 and the DevArt driver. This just returns a single value but you may be able to adapt it to return multiple rows. I have a package.proc called P_SID.SID_PGet:

PROCEDURE SID_PGet(io_SID OUT varchar2) is
Begin
   io_SID:=GetSID;
End;

Below is how I call it and retrieve the SID value:

var parameter = new Devart.Data.Oracle.OracleParameter("io_SID", Devart.Data.Oracle.OracleDbType.VarChar, ParameterDirection.Output);
this.Database.ExecuteSqlCommand("BEGIN P_SID.SID_PGet(:io_SID); END;", parameter);
var sid = parameter.Value as string;

return sid;

stored procedure with EF 4.1

3 Answers3