Entity Framework calling stored procedure

Question

I have a stored procedure that I would to call from Entity Framework. In the result there is three table being returned. The stored procedure takes in two parameters, UserId and BlogId. Do I need the database open and the command.ExecuteReader? Can Entity Framework handle multiple data reads?

public List<Models.BlogInfo> GetBlogListing(int UserId, int BlogId)
{
    using (var db = new Entities())
    {
        using (DbContextTransaction dbTran = db.Database.BeginTransaction())
        {
            ResultStatus resultStatus = new ResultStatus();

            try
            {
                var cmd = db.Database.Connection.CreateCommand();
                cmd.CommandText = "[coi].[usp_Public_Select_GetPanelBlog]";

                SqlParameter paramMeetingId = new SqlParameter("@UserId", UserId);
                SqlParameter paramGroupId = new SqlParameter("@BlogId", BlogId);

                Database.OpenConnection();

                using (var result = command.ExecuteReader())
                {
                }
            }
            catch (Exception ex)
            {
                dbTran.Rollback();
            }
        }
    }
}

Does this answer your question? [How to call Stored Procedure in Entity Framework 6 (Code-First)?](https://stackoverflow.com/questions/20901419/how-to-call-stored-procedure-in-entity-framework-6-code-first) — Connell.O'Donnell, Nov 16 '20 at 16:38

score 0 · Accepted Answer · answered Nov 16 '20 at 16:45

can you try this?

 using (var context = new TestEntities())
        {
            using (var con = context.Database.GetDbConnection())
            {
                try
                {
                    if (con.State != ConnectionState.Open)
                        con.Open();

                    using (var cmd = con.CreateCommand())
                    {
                        //postgres
                        cmd.CommandText = "SELECT * FROM \"USP_Test\"()";

                        using (System.Data.Common.DbDataReader dr = 
                       cmd.ExecuteReader())
                        {
                            var tb = new DataTable();
                            tb.Load(dr);
                            var json = JsonConvert.SerializeObject(tb);

                            var result = JsonConvert.DeserializeObject<List<TestClass>>(json);

                            return result;
                        }
                    }
                }
                catch (Exception exc)
                {
                    throw exc;
                }
                finally
                {
                    if (con.State != ConnectionState.Closed)
                        con.Close();
                }
            }
        }

Entity Framework calling stored procedure

1 Answers1