3

I am developing a report viewer as ap.net web api application. SO I need an entity framework dynamic query result.

public IEnumerable<dynamic> MyQuery()
{
    const string queryString = " SELECT ..... " ; // this is my complex sql query
    var result = context.Database.SqlQuery<dynamic>(queryString).ToList();
    return result;
}

My sql query returns any type of object like below:

(Name, Total)

(Name, Total, Category)
(Name, TOtal, CityOnoTotal, CityTwoTotal)

So I want to use this result in my asp.net web api action method. Query runs and gets some results.

I used to see response on Postman like this.

enter image description here

My query response properties are lose.

barteloma
  • 6,403
  • 14
  • 79
  • 173
  • Did you tried to send back `object`? (just curious for the result). Maybe it makes sense to revork an `dynamic` to something like `Dictionary`? – ASpirin Aug 13 '17 at 09:13
  • No, how can I get dynamic values from in Dictionary? Can u explain more clear wit code? – barteloma Aug 13 '17 at 09:21
  • @bookmarker did you try to return jsonresult by using Json.Net serialization like JsonConvert.SerializeObject(result) – hasan Aug 13 '17 at 09:46
  • Yes I used it but sql query result creates empty objects. So the result is like that. – barteloma Aug 13 '17 at 09:52
  • @bookmarker I searched a bit and it seems you can not use EF SqlQuery with unknown type, this link might help you https://stackoverflow.com/a/25497376/8175473 – hasan Aug 13 '17 at 09:58

1 Answers1

2

This I suppose wouldn't work, because EF is not returning properties in the dynamic object at all, for explained case.

It works other way around, First EF Takes all properties of provided Generic Type attribute, Then it tries to map properties to SQL request results.

So if you don't know what is the list of fields will be as result EF wouldn't generate it for you.

First solution will be to parse select list to define the fields and generate class dynamicaly. Check how to create dynamic class discussion. Than you can do something like

var dType = MyTypeBuilder.CompileResultType("sample", new Dictionary<string, Type>
{
    { "Id", typeof(int) }
});
var db = new Model1().Database;
var sql = db.GetType().GetMethods().Where(m => m.Name == "SqlQuery" && m.IsGenericMethod);
var gen = sql.First().MakeGenericMethod(dType);

var result = gen.Invoke(db, new object[] { "[Your SQL]", new object[0] });
var ie = (result as IEnumerable<object>);

that is really complex, but works

Second - you can do classic ADO SQLCommand using context.Database.Connection and parse results

Third way to solve the problem is to Return XML from SQL, if possible.

var query = db.SqlQuery<string>("[Your SQL] for XML PATH('item'), root('root')").First();

that will return all data in XML format Like:

<root>
    <item>
        <Id>5</Id>
        ...
    </item>
    <item>
        <Id>6</Id>
        ...
    </item>
</root>
ASpirin
  • 3,601
  • 1
  • 23
  • 32