How to execute a stored procedure returning a pivot table in Web API

Question

How to execute a stored procedure returning a pivot table and return data as JSON in a API. I want to display its data on a web page using API. What I'm getting is just a list of objects but not the data in it. My procedure is returning 983 records of table rows in it... But column change according to passed parameters So for one kind of parameter i can have 20 column buy for the other i can have just 1 By executing procedure using

    var ls = db.Database.SqlQuery<dynamic>("exec [Sp_StoreItemParty] 'Amount','01-Apr-2020', '06-Sep-2020','', '', '', ''").ToList();
    return Json(ls, JsonRequestBehavior.AllowGet);

How to execute a stored procedure returning a pivot table and return it as Web API in JSON format . All i get is a list of objects but none of the objects can further be accessed or further displayed in Postman on On Firefox.

Answer 1

SqlQuery works only with known types, not dynamic. You can

1. Either create a class that represents your result record and use that. Or

2. Use a data reader as shown here and copy pasted below.

   public static IEnumerable<dynamic> CollectionFromSql(this DbContext dbContext, string Sql, Dictionary<string, object> Parameters)
   {
       using (var cmd = dbContext.Database.GetDbConnection().CreateCommand())
       {
           cmd.CommandText = Sql;
           if (cmd.Connection.State != ConnectionState.Open)
               cmd.Connection.Open();
   
           foreach (KeyValuePair<string, object> param in Parameters)
           {
               DbParameter dbParameter = cmd.CreateParameter();
               dbParameter.ParameterName = param.Key;
               dbParameter.Value = param.Value;
               cmd.Parameters.Add(dbParameter);
           }
   
           //var retObject = new List<dynamic>();
           using (var dataReader = cmd.ExecuteReader())
           {
   
               while (dataReader.Read())
               {
                   var dataRow = GetDataRow(dataReader);
                   yield return dataRow ;
   
               }
           }
   
   
       }
   }
   
   private static dynamic GetDataRow(DbDataReader dataReader)
   {
       var dataRow = new ExpandoObject() as IDictionary<string, object>;
       for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
           dataRow.Add(dataReader.GetName(fieldCount), dataReader[fieldCount]);
       return dataRow;
   }
   

   Usage:

   List<dynamic> MyList = MyDbContext.CollectionFromSql("SELECT * FROM \"User\" WHERE UserID = @UserID",
       new Dictionary<string, object> { { "@UserID", 1 } }).ToList();