asp.net mvc core how can I return a Json query

Question

First and foremost I Know how to return a Json Query by doing this

public JsonResult streams()
{
    using (var conn = new NpgsqlConnection(Connectionstring))
    {
        conn.Open();
        var credentials =  conn.Query<Streams>("select id,name from streams").ToList();                
        ViewData["Message"] = "Your application description page.";
        conn.Close();
        return Json(credentials);
    }
}

That code above returns Json for me from the database, however now I am actually returning the Json from the SQL code by changing the query to this

var credentials =  conn.Query<Streams>("select SELECT array_to_json(array_agg(t)) from (select id,name from streams) t").ToList();

That new query works correctly however it is returning null in the Controller as I have it like this now

public JsonResult streams()
{
    using (var conn = new NpgsqlConnection(Connectionstring))
    {
        conn.Open();
        var credentials =  conn.Query<Streams>("SELECT array_to_json(array_agg(t)) from (select id,name from streams) t").ToList();            
        ViewData["Message"] = "Your application description page.";
        conn.Close();
        return Json(credentials);
    }
}

How can I fix this ? My assumption is that using JsonResult and returning Json as an action is messing things up because the SQL query is already returning Json.

This is my Stream Class

  public class Streams
    {
        [Key]
        public int id { get; set; }
        public string name { get; set; }
        public int profile_id { get; set; }

        public DateTime created_on { get; set; }
        public int last_reply { get; set; }
        public int comments { get; set; }
        public string city { get; set; }
        public string state { get; set; }
        public float latitudes { get; set; }
        public float longitudes { get; set; }
        public int votes { get; set; }
    }

Is your SQL query (in second example) result set columns match with your Stream property names ? Can you share the result set ? — Shyju
– Shyju, Commented Feb 16, 2016 at 21:41
Why are you returning json from your Sql query. Return the normal result set and Let Json method takes care of converting it to JSON format — Shyju
– Shyju, Commented Feb 16, 2016 at 21:56
I was under the impression that returning Json from the SQL code is more efficient than converting it to Json in the application, especially when there are a lot of requests. — user1591668
– user1591668, Commented Feb 16, 2016 at 22:01

NikolaiDante · Accepted Answer · 2016-02-16 22:08:11Z

2

It's because you have Json in your string and the call to this.Json(object) is designed to serialize it to Json.

In MVC 5 you could:

return this.Content(credentials, "application/json");

The ContentResult class is in MVC Core, so I assume that the syntax remains the same.

answered Feb 16, 2016 at 22:08

NikolaiDante

18.7k14 gold badges83 silver badges120 bronze badges

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

1 Comment

user1591668 Over a year ago

Thanks a lot that is exactly what I needed

Collectives™ on Stack Overflow

asp.net mvc core how can I return a Json query

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related