How can I get Entity Framework dynamic query result for web api result

Question

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.

My query response properties are lose.

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

ASpirin · Accepted Answer · 2017-08-13 11:03:30Z

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>

edited Aug 13, 2017 at 11:03

answered Aug 13, 2017 at 9:53

ASpirin

3,6711 gold badge26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

barteloma Over a year ago

So I could parse DataRows to an object as dynamically. But how?

ASpirin Over a year ago

Or generate class dynamically, or call SQLCmmand via ADO and parse DataTable

ASpirin Over a year ago

check also a possibility to use XML result from SQL

Shady Over a year ago

Forth: Return JSON from SQL var query = db.SqlQuery<string>("[Your SQL] for json path").First();

ASpirin Over a year ago

@Shady good point. Just don't forget to mention the version, I suppose SQL Server starting with 2016

humbads · Accepted Answer · 2024-07-14 13:04:20Z

-1

Maybe just return a string and parse it when received. For example:

SELECT 
CONVERT(NVARCHAR(MAX), Name) + '|' + CONVERT(NVARCHAR(MAX), Total) + '|' + CONVERT(NVARCHAR(MAX), Category)
FROM TABLE_NAME

Then, in the front end:

string[] fields  = Results.Split('|');

You'd then need to the convert the elements to int, boolean etc.

edited Jul 14, 2024 at 13:04

humbads

3,4521 gold badge29 silver badges22 bronze badges

answered Jul 14, 2024 at 0:05

Geoff Davison

1

1 Comment

Gert Arnold Over a year ago

No. I'm sure they're not going to change each query to accommodate splitting. Also, what if strings contain "|"? And what is Results? What if there are multiple records? What if they do want typed data in the reports?

Collectives™ on Stack Overflow

How can I get Entity Framework dynamic query result for web api result

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related