asp.net mvc 5 Dapper Json is mapping the whole model class

asp.net-mvc asp.net-mvc-4 dapper json

Question

I am using Dapper in my ASP.NET MVC 5 application and in my query I only want 2 fields to return but the Json returns all of the fields. This is my model

    public class thread
    {
        [Key]
        public int id { get; set; }
        public int? profileID { get; set; }
        public int numberkeeper { get; set; }
        public int? photocount { get; set; }
}

This is my controller..

[ResponseType(typeof(thread))]
    public IHttpActionResult Getstream()
    {

             string Connectionstring = ConfigurationManager.ConnectionStrings["db"].ConnectionString;

             using (System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(Connectionstring))
             {

                 sqlConnection.Open();
                 var statevi = sqlConnection.Query<thread>("Select top 5 id,numberkeeper from threads").ToList();

                 if (statevi == null)
                 {
                     return NotFound();
                 }
                 return Ok(statevi);

             }
    }

That code returns Json as it is using .Net Web API,as you can see from the query I only want 2 fields returned. When I run it and see the Json it displays all fields (4) and off course the 2 fields not selected show up as null . I wanted so that the Json only shows the returnn of id and numberkeeper

Accepted Answer

If you create a new model that exposes the only two members that you want to render, that will prevent Web API from returning back additional JSON.

You could also convert the data after loading it into a new anonymous model using LINQ.

return Ok(statevi.Select(s => new { s.id, s.numberkeeper }));

If you want to keep the same model, but suppress null valued members Web API allows you to configure the JSON formatting to exclude null properties.

config.Formatters.JsonFormatter.SerializerSettings = new JsonSerializerSettings
{
    NullValueHandling = NullValueHandling.Ignore
};

Popular Answer

Create a View Model class:

public class ThreadViewModel
{
        public int id { get; set; }
        public int numberkeeper { get; set; }
}

Let Dapper know you want it to create the ThreadViewModel for you:

var statevi = sqlConnection.Query<ThreadViewModel>("Select top 5 id,numberkeeper from threads").ToList();

This way you both query the database for the relevant properties and return just them to the client (without Dapper creating the full object with nulls).



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why