Dynamic Entity for Raw Sql Query using Entity Framework

Question

I am scratching my head here. I have a bootstrap modal that has a field for entering in a sql query. The query I pass uses an AJAX call to a controller and executes the sql query using the code below and returns a JSON object. Something like this:

context.Database.SqlQuery(typeof(object), query);

But all I get are empty objects. I can't pass a type when I don't know the columns. For instance I could do something like this:

public class CusType {
    public CusType(){}
    public int Id { get; set; }
    public string Name { get; set; }
}

and then do something like this:

context.Database.SqlQuery(typeof(CusType), query);

But that won't help me when I don't know or can't know the columns. I even tried an idea using ExpandoObjects. Example:

List<string> columns = new List<string>();
string tmpCol = query;
string[] seperator = new string[] { "from" };
tmpCol = query.ToLower()
    .Replace(@"\s+", "")
    .Replace("select", "");
tmpCol = tmpCol.Split(seperator, 1, StringSplitOptions.RemoveEmptyEntries)[0];
for (int i = 0; i < tmpCol.Split(',').Length; i++)
{
    columns.Add(tmpCol.Split(',')[i]);
}
dynamic data = new ExpandoObject();
IDictionary<string, object> props = (IDictionary<string, object>)data;
foreach (var column in columns)
{
    props.Add(column.ToString(), column.ToString());
}

return context.Database.SqlQuery(data.GetType(), query);

But what do I do when I want to execute the query?

context.Database.SqlQuery(data.GetType()??, query);

Even when I do put in the data.GetType() it returns empty objects.

In both examples (using typeof(object) and typeof(data.GetType()) I get this JSON object returned:

[{},{},{},{},{},{},{},{},{},{},{},{},{}]

Any ideas/suggestions?

Answer 1

Entity Framework populates a model based on the type specified. You'll need to use something else or go old school with a DataReader.