1

I want to run custom query on a dynamic table or view which is not Modeled

I always need 1 record, but the field name are not known

some time it could be view_1, view_2, table_1 etc.

I need the result in key pair values (IDictionary<string,string>)

my current code is 

view_student1 v1= db.view_student1 .Where(e => e.rollNo==1).FirstOrDefault();
var json = JsonConvert.SerializeObject(v1);
return JsonConvert.DeserializeObject<Dictionary<string, string>>(json);

but in above code view_student1 is Modeled, I want this dynamic and add any other view at run time, I tried the following but could not get

string sql = "Select * from "+viewName+" where rollNo = '"+ rollNo+"'";
var student = db.Database.SqlQuery<dynamic>(sql).ToList();

IDictionary<string, string> strings = new Dictionary<string, string>();
foreach (var std in student ) {
   //dont know how to get all field names/values here    
   foreach (var fld in std) {      
       strings.Add(fld.Key,fld.value);   
   }
}

1 Answers1

0

I'll assume that you have modern SQL Server which supports json output. Result can be retrieved as json by adding for json path expression after your sql-query. Result is given as json string containing array of objects.

Following solution uses json-to-dictionary deserializing which you can get from Json.NET.

string sql = "Select * from "+viewName+" where rollNo = '"+ rollNo+"' for json path";

var student = db.Database.SqlQuery<string>(sql).FirstOrDefault();

IDictionary<string, string> strings  = JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(student).First();
Risto M
  • 2,919
  • 1
  • 14
  • 27
  • `for json path` skipping few fields, have any idea why? –  Mar 18 '19 at 06:53
  • Null values? Check this: https://learn.microsoft.com/en-us/sql/relational-databases/json/include-null-values-in-json-include-null-values-option?view=sql-server-2017 – Risto M Mar 18 '19 at 06:55