Convert string JSON property to Object on Web API Call

Question

I have the following class that represents a table in an MSSQL database:

public string firstname { get; set; }
public string lastname { get; set; }
public string events { get; set; }

The 'events' property is actually an array of JSON objects serialized before storing in the table. Example row:

FirstName: Test
LastName: User
Events: [{"eventname":"event1","eventtype":"1"},{"eventname":"event2","eventtype":"2"}]

I need to return the contents of this table from a Web API call in JSON format to resemble:

[{
  "firstname":"Test",
  "lastname":"User",
  "Events":[{"eventname":"event1","eventtype":"1"},{"eventname":"event2","eventtype":"2"}]
},
{...}]

Problem is when I do this, the events JSON property gets escaped and comes out as single string like this:

[{
  "firstname":"Test",
  "lastname":"User",
  "Events":"[{\"eventname\":\"event1\",\"eventtype\":\"1\"},{\"eventname\":\"event2\",\"eventtype\":\"2\"}]
},
{...}]"

Obviously it's because the class for the events property is a string variable but not sure what I can do about that without having to loop through each row to re-format?

Luke T Brooks · Answer 1 · 2017-10-02T23:38:40.610

When handling your object in C#, I would recommend creating an Event class to make a List of Event objects:

public class Event
{
    public string EventName { get; set; }
    public string EventType { get; set; }
}

public class YourClass
{
    public string firstname { get; set; }
    public string lastname { get; set; }
    public List<Event> events { get; set; }
}

If you need that object stored as a JSON object in your MSSQL database, you can convert it when you go to save it to your database:

List<Event> yourEvents = new List<Event>();

Newtonsoft.Json.JsonConvert.SerializeObject(yourEvents).ToString();

When retrieving from your MSSQL database, you can then use the inverse of the Serialize function to create your C# List:

Newtonsoft.Json.JsonConvert.DeserializeObject(yourRetrievedEvents);

To Deserialize all of your retrieved data you can use Linq:

public class RetrievedClass
{
    public string firstname { get; set; }
    public string lastname { get; set; }
    public string events { get; set; }
}

List<RetrievedClass> dbData = mssql()

List<YourClass> result = dbData.Select(x => new YourClass
    {
        firstname = x.firstname,
        lastname = x.lastname,
        events = Newtonsoft.Json.JsonConvert.DeserializeObject(x.events)
    });

Unfortunately I don't see a way without using Linq or a loop.

This approach is fine, in case you want to have the model in sync, you can have a string property events which will have a getter which serializes the collection of events. This would give you model binding and db sync in an easy go — Saravanan, Oct 02 '17 at 17:56
That is effectively what I have already for creating the data in MSSQL. To deserialize afterwards would mean looping through each row though wouldn't it? — Nick Reeve, Oct 02 '17 at 22:44
Ah I see. I edited my answer to include how I would approach deserializing the data. — Luke T Brooks, Oct 02 '17 at 23:39

Convert string JSON property to Object on Web API Call

1 Answers1