Entity Framework 6 Custom query to IDictionary

Question

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);   
   }
}

Answer 1

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();