How do I execute RAW SQL query and dump it to JSON in ASP.NET MVC 5 (EF 6)?

Question

I want to execute a raw SQL query as shown below:

select material, method, count(*) 
from treatment 
group by material, method

and return it as a JSON object.

Because EF 6 allows the execution of raw query (i.e., Database.SQLQuery()), I use it to create a JSON Object.

My code looks like this.

// GET: Dummy
public string Dummy()
{
    string query = "select material, method, count(*) as cnt from Treatment "
                   + " group by material, method";
    var result = db.Database.SqlQuery<List<String>>(query);
    var jsonResult = JsonConvert.SerializeObject(result);

    return jsonResult;
}

However, instead of getting a JSON object with the material and methods, I get an empty json object instead.

[[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[]]

Is it possible to return the correct JSON object without having the model for the the raw query? If so, how to update my code to return the correct result?

Thanks in advance

Are you sure your result has data in it? By the looks of it, it appears that the result was empty? I know it sounds obvious, but just step through the code and check if result contains anything? — WynDiesel
– WynDiesel, Commented Aug 23, 2018 at 10:42
Yes. There are several row of data in the table and the amount of empty JSON object created is the same number of rows of the result. — Saber Alex
– Saber Alex, Commented Aug 23, 2018 at 10:52
try chaning db.Database.SqlQuery<List<String>>(query) to db.Database.SqlQuery<List<object>>(query). — 3dd
– 3dd, Commented Aug 23, 2018 at 11:02
List<String> only returns single list of strings while your query has 3 data sets. What do you mean of "without having the model" - are you don't want to create additional class with properties to handle query result before converting to JSON? — Tetsuya Yamamoto
– Tetsuya Yamamoto, Commented Aug 23, 2018 at 11:48

WynDiesel · Accepted Answer · 2018-08-23 12:05:27Z

2

You can't resolve that query to a list of strings, you need to resolve it to a (list of) class(es), like this :

public static string Dummy()
{
    using (var db = new TestDbEntities())
    {
        string query = "select 'material' as material , 'method' as method, 1  as cnt  ";
        var result = db.Database.SqlQuery<MyClass>(query);
        var res = result.ToList();
        var jsonResult = JsonConvert.SerializeObject(res);
        return jsonResult;
    }
}

where your class will be something like this :

public class MyClass
{
    public string Material { get; set; }

    public string Method { get; set; }

    public int Cnt { get; set; }
}

answered Aug 23, 2018 at 12:05

WynDiesel

1,2141 gold badge10 silver badges44 bronze badges

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

Comments

Tetsuya Yamamoto · Accepted Answer · 2018-08-23 12:30:15Z

You need to capture query result sets with multiple columns using a class with matching property names (make sure they have exactly same name and proper casing) and parameterless constructor:

public string Dummy()
{
    string query = "select material as Material, method as Method, count(*) as Cnt from Treatment group by material, method";
    var result = db.Database.SqlQuery<ResultSet>(query).ToList();
    var jsonResult = JsonConvert.SerializeObject(result);
    return jsonResult;
}

public class ResultSet
{
    public string Material { get; set; }

    public string Method { get; set; }

    public int Cnt { get; set; }
}

Note that Database.SqlQuery<T> will return an IEnumerable<T> with type System.Data.Entity.Internal.InternalSqlQuery<T>, so that a List<string> is not fit in this context. If you just return single result set, Database.SqlQuery<string>(...).ToList() should be used instead of Database.SqlQuery<List<string>>.

Similar issue:

Entity framework raw SQL Query

user10038293 · Accepted Answer · 2018-08-23 14:19:04Z

0

Use FOR JSON AUTO:

     string query = "select material, method, count(*) as cnt from Treatment "
        + " group by material, method FOR JSON AUTO";

answered Aug 23, 2018 at 14:19

user10038293

Collectives™ on Stack Overflow

How do I execute RAW SQL query and dump it to JSON in ASP.NET MVC 5 (EF 6)?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related