0

I want to run a dynamic custom query which is not bound to any object or class in my project. It will always return null or 1 record.

I tried many things but without success so far:

// this is working function with hard-coded query
private Dictionary<string, string> getRecord(string id, string viewName)
{
    Peron p = db.Person.Where(e => e.ID == id).FirstOrDefault();
    var json = JsonConvert.SerializeObject(p);
    return JsonConvert.DeserializeObject<Dictionary<string, string>>(json);
}

but I need this with dynamic query on a database view while not adding the model in EF models.edmx.

private Dictionary<string, string> getRecord(string id, string viewName)
{
    string qry = "select * from " + viewName + " where ID='" + id + "'";
    var p = db.Database.SqlQuery<dynamic>(qry).FirstOrDefault();

    // p sometimes returns null on dynamic object, many times it throws 
    // an error "Cannot convert this to this" ....
    var json = JsonConvert.SerializeObject(p);
    return JsonConvert.DeserializeObject<Dictionary<string, string>>(json);
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Please [edit] your question to include a [mcve] which can be compiled and tested, that shows the error you get. Also, include the full error message and stack trace of the exception. – Progman Feb 03 '19 at 10:22
  • `if (p == null) return new Dictionary();` – Alexander Petrov Feb 03 '19 at 13:57
  • From Microsoft docs: `SqlQuery` _Creates a raw SQL query that will return elements of the given generic type. The type can be any type that has properties that match the names of the columns returned from the query, or can be a simple primitive type_. So, `dynamic` is not either a "type with properties that match the names of the columns returned from the query" nor a "simple primitive type". What you want to do is not posible because Entity Framework is strongly typed. You need a type to map the result of the query. – Balde Feb 04 '19 at 22:21

0 Answers0