Handle Stored Procedure in Entity Framework

Question

How to handle stored procedure that returns different outputs in Entity Framwork. For example , on sp, there is if condition and else condition. if condition return integer and else condition return datatable. How to handle this using entity framework in vs. Please advice.

possible duplicate of [Entity Framework - stored procedure return value](http://stackoverflow.com/questions/10339750/entity-framework-stored-procedure-return-value) — Ed Chapel, Jun 18 '13 at 08:46
possible duplicate of [Stored procedures in entity framework](http://stackoverflow.com/questions/1506193/stored-procedures-in-entity-framework) — gbjbaanb, Jun 18 '13 at 08:49

score 3 · Answer 1 · answered Jun 18 '13 at 12:46

Starting from EF 4.1 this is possible. The only requirement is to know what the SP is going to return in each case.

In my example I use

DbContext.Database.SqlQuery<TElement>(string sql, params object[] parameters)

This generic method takes as a generic parameter the type you would like to use for materialization. It also takes SQL expression you would like to execute as a parameter, along with it's parameters as param'd array.

Simplified SP (no input parameters) usage is:

var res = ctx.Database.SqlQuery<MyResultType1>("dbo.MyStoredProcedure");

foreach (var r in res)
{
    System.Console.Out.WriteLine(
        "col1:{0}; col2:{1}; col3={2}", 
        r.Col1,
        r.Col2,
        r.Col3);
}

So, you can do following:

IEnumerable res
if(...your logic...) 
{
    res = ctx.Database.SqlQuery<MyResultType1>(...your SP call...);
}
else
{
    res = ctx.Database.SqlQuery<MyResultType2>(...your SP call...);
}

This way you are going to fill your collection with SP output resultset the way you want.

score 2 · Answer 2 · answered Jun 18 '13 at 08:54

2

Click update model from database select your stored procedure and make sure it procedure has been added in Function Imports(Model Browser)

You can change return values from Edit function import window

and then just execute db.myProcedure();

answered Jun 18 '13 at 08:54

Irakli Lekishvili

33,492
33
111
169

score 1 · Answer 3 · answered Jun 18 '13 at 08:50

1

eg: just drag and drop your stored procedure and then

 db.procedure(element1, element2);

 db.SubmitChanges();

answered Jun 18 '13 at 08:50

gasroot

515
3
15

Raju S Nair · Accepted Answer · 2013-06-19T12:58:30.343

1

Ef4 does not support stored procedure with multiple returns of different types. We can do it either by direct calling of Sp via sql codes or can do the schema in linq.

Else we needs to use EF 4.1 and above.

edited Jun 19 '13 at 12:58

answered Jun 18 '13 at 12:21

Raju S Nair

333
2
5
17

Handle Stored Procedure in Entity Framework

4 Answers4

Linked