Getting Data from Stored Procedure with Entity Framework

Retrieve table data from stored procedure using entity framework

You need to Import the stored procedure as a Function. Right-click on the workspace area of your Entity model and choose Add -> Function Import.
In the Add Function Import dialog, enter the name you want your stored procedure to be referred to in your model for example GetCountryListSP, choose your procedure from the drop down list, and choose the return value of the procedure to be Entities and choose CountryName from the drop down list.
Then in the code:
```
var result = db.GetCountryListSP();//Send parameters too
```
With this approach you prevent returning -1 of the stored procedure. Please check this post for more details about stored procedure problem with Entity Framework.

How to get output result from stored procedure use Entity Framework in C#?

Declare your store procedure using this syntax:

    USE yourDataBaseNAme
    GO

    CREATE PROCEDURE [dbo].yourStoreProcedureName
    @startDate nvarchar(30), 
    @endDate   nvarchar(30)
    AS 
       SELECT Cast(date_rec_slash as datetime) AS 'date_rec_slash', count(code_marz) as total,
              CASE
                  WHEN code_marz = 1 THEN 'a'
                  WHEN code_marz = 2 THEN 'b'
                  WHEN code_marz = 3 THEN 'c'
                  WHEN code_marz = 4 THEN 'd'
                  WHEN code_marz = 5 THEN 'e'
              END AS 'code_marz'
      FROM dbo.tbl_bar 
     WHERE Cast(date_rec_slash as datetime) between @startDate 
                                                AND @endDate
     GROUP BY Cast(date_rec_slash as datetime), code_marz
     ORDER BY Cast(date_rec_slash as datetime) ASC;
    GO

Call this store procedure in EF:

db.Database.SqlQuery<yourObjectNameToCAST>("yourStoreProcedureName");

Call store procedure with parameter in EF:

SqlParameter startDate= new SqlParameter("@startDate", "Value");
SqlParameter endDate= new SqlParameter("@endDate", "Value");
db.Database.SqlQuery<yourObjectNameToCAST>("exec yourStoreProcedureName @startDate, @endDate", startDate, endDate).ToList();

your Object to Cast:

public class yourObjectNameToCAST
{
     public datetime date_rec_slash { get; set; }
     public int total { get; set; }
     public string code_marz { get; set; }
}

Entity Framework : Use stored procedure to return raw table result

EF is built on top of ADO.NET, so whenever you need to you can directly access the DbConnection for a DbContext and use it directly. Eg

        using (var db = new MyDbContext())
        {
            db.Database.Connection.Open();
            var con = (SqlConnection)db.Database.Connection;
            var cmd = new SqlCommand("exec MyProc", con);
            DataTable dt = new DataTable();
            using (var rdr = cmd.ExecuteReader())
            {
                dt.Load(rdr);
            }
            //. . .

Getting data from stored procedure with Entity Framework

Use the following steps to solve this issue:

You need to Import the stored procedure as a Function. Right-click on the workspace area of your Entity model and choose Add -> Function Import.
In the Add Function Import dialog, enter the name you want your stored procedure to be referred to in your model for example Search_Products, choose your procedure from the drop down list, and choose the return value of the procedure to be Entities and choose Products from the drop down list.

Then in the code behind:

var db = new MyEntities();
var TEST_SEARCH_TERM = "product";
var result = db.Search_Products(TEST_SEARCH_TERM);//Search_Products is the name that you specified in Function Import dialog

MyGridView.DataSource = result;
MyGridView.DataBind();

The reason that you get -1 for result is that Entity Framework cannot support Stored Procedure Return values out of the box. I think support of stored procedure return values depends on version of Entity framework. Also Entity Framework doesn't have rich stored procedure support because its an ORM, not a SQL replacement.

.NET Core Entity Framework stored procedures

At the moment, the way to execute stored procedures that return data is to use the DbSet.FromSql method.

using (var context = new SampleContext())
{
    var data= context.MyEntity
        .FromSql("EXEC GetData")
        .ToList();
}

This has certain limitations:

It must be called on a DbSet
The returned data must map to all properties on the DbSet type
It does not support ad hoc objects.

Or you can fall back to plain ADO.NET:

using (var context = new SampleContext())
using (var command = context.Database.GetDbConnection().CreateCommand())
{
    command.CommandText = "GetData";
    command.CommandType = CommandType.StoredProcedure;
    context.Database.OpenConnection();
    using (var result = command.ExecuteReader())
    {
        // do something with result
    }
}

There are plans to introduce support for returning ad hoc types from SQL queries at some stage.

how to run stored procedure in Entity framework

As per my understanding, you want to execute a stored procedure that run on multiple tables, and then return Json Data to View. You can actually do something like below:

Approach 1: (Using ExecuteSqlCommand)

SqlParameter param1 = new SqlParameter("@query", query);        
var result = db.Database.ExecuteSqlCommand("SP_DynamicCtrl @query", 
                              param1);

Approach 2: (Using Object Type on SqlQuery)

SqlParameter param1 = new SqlParameter("@query", query);
Var result = db.Database.SqlQuery<Object>("exec SP_DynamicCtrl @query", param1);

Approach 3: (Cleaner Approach)

1.) Create a model as per your return parameters from stored procedure, let's call it YourType class.
2.) Use the below code to call stored pocedure:

SqlParameter param1 = new SqlParameter("@query", query);
Var result = db.Database.SqlQuery<YourType>("exec SP_DynamicCtrl @query", param1);

After you get the result from above query, you can convert it to JSON befor returning in controller:

return Json(result, JsonRequestBehavior.AllowGet); //Typecast the result as per your need

Please modify code as per your need.