Processing a stored procedure in the Entity Framework

Question

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

Answer 1

Ef4 does not support a stored procedure with multiple return values ​​of different types. We can do this either by directly calling Sp via sql codes, or for a schema in linq.

Otherwise, we need to use EF 4.1 and higher.

Answer 2

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

In my example, I use

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

This general method takes as a general parameter the type that you would like to use for materialization. It also accepts the SQL expression that you want to execute as a parameter, as well as parameters as param'd.

Simplified use of SP (without input parameters):

 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 the 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 the help of the SP output results the way you want.

Answer 3

Click the "Update Model" button from the database, select the stored procedure and make sure that it has been added to the "Import Objects" ("Model Browser")

You can change the return values ​​from the import window of the edit function

and then just do db.myProcedure();

Answer 4

for example: just drag and drop your stored procedure and then

  db.procedure(element1, element2); db.SubmitChanges();