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.

Answer 1

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.