Question 87411 in ASP.NET Question posted in
The official documentation can be found here.

How to serialize json in newsofton on recurring datatable value in ASP.NET Core

So I’ve been trying to serialize a json that I have so that the defining value I get from my dataset is a "key" in my Json array and that all the values are within that key if that makes sense.

I’m new to this and I haven’t found any other post that would help me with this so hopefully one of you knows the answer.

So the code that I’m using to try and achieve this is as following:

string query = @"
                  select Country.Name, City.Name from world.country 
                  inner join world.city on world.country.Code = world.city.CountryCode
                  where CountryCode = @CountryCode";

  DataSet dataSet = new DataSet("dataSet");
  dataSet.Namespace = "NetFrameWork";
  DataTable table = new DataTable();
  dataSet.Tables.Add(table);

  string sqlDataSource = _configuration.GetConnectionString("WorldAppConnection");
  MySqlDataReader myReader;
  using (MySqlConnection mySqlConnection = new MySqlConnection(sqlDataSource))
  {
    mySqlConnection.Open();
    using (MySqlCommand mySqlCommand = new MySqlCommand(query, mySqlConnection))
    {
      mySqlCommand.Parameters.AddWithValue("@CountryCode", CountryCode);
      myReader = mySqlCommand.ExecuteReader();
      table.Load(myReader);
      myReader.Close();
      mySqlConnection.Close();
    }
  }
  dataSet.AcceptChanges();
  string json = JsonConvert.SerializeObject(dataSet, Formatting.Indented);
  return json;
}

Now if I perform this query in SQL a small snippet of what the result is:

France | Paris
France | Marseille
France | Lyon
France | Toulouse
…

Now what this code results in as a Json is:

{
  "Table1": [
    {
    "Name": "France",
      "Name1": "Paris"
    },
    {
    "Name": "France",
      "Name1": "Marseille"
    },
    {
    "Name": "France",
      "Name1": "Lyon"
    },
    {
    "Name": "France",
      "Name1": "Toulouse"
    },
    {
    "Name": "France",
      "Name1": "Nice"
    },
    {
    "Name": "France",
      "Name1": "Nantes"
    },
    {
    "Name": "France",
      "Name1": "Strasbourg"
    }
  ]
}

As you can see it keeps on repeating the France field. However the result I would want to have is something like:

{
  "Table1": [ 
    {
     "France":[   
       {
         "Name1": "Paris"
       },
       {
         "Name1": "Marseille"
       },
       {
         "Name1": "Lyon"
       },
       {
         "Name1": "Toulouse"
       },
       {
         "Name1": "Nice"
       },
       {
         "Name1": "Nantes"
       },
       {
         "Name1": "Strasbourg"
       }
     ]
   }
 ]
}

Is there any way to achieve this in any way?

Thanks in advance.

Tags:

2

Answers


  1. 0

    try this

    dataSet.AcceptChanges();

//or this
string json = JsonConvert.SerializeObject(dataSet, Formatting.Indented);
var jsonParsed = JObject.Parse(json); 

//or better 
var jsonParsed = JObject.FromObject(dataSet); 

var result = new
{
Table1 = jsonParsed["Table1"].GroupBy(x => x["Name"])
       .Select(x => new Dictionary<string, string[]> { { (string)x.Key, 
       x.Select(i => (string)i["Name1"]).ToArray() }})
};

    result

    {
  "Table1": [
    {
      "France": [
        "Paris",
        "Marseille",
        "Lyon",
        "Toulouse",
        "Nice",
        "Nantes",
        "Strasbourg"
      ]
    }
  ]
}
    Login or Signup to reply.
  2. 0

    Before serialize the object, you can use LINQ to DataSet to group the result by the Name property, refer to the following sample code:

    Create CountryViewModel class:

    public class CountryViewModel
{
    public string Name1 { get; set; }
}

    Then use the following code (My application name is "Application1", you can change it to yours):

    //group the country by the name, 
var linqresult = dataSet.Tables[0].AsEnumerable()
                .GroupBy(c => c.Field<string>("Name"))
                .Select(c => new Dictionary<string, List<WebApplication1.Models.CountryViewModel>> {
                    {
                      (string)c.Key, 
                    c.Select(i => new WebApplication1.Models.CountryViewModel() { Name1 = (string)i["Name1"] }).ToList()
                    }}).ToList();
var newdic = new Dictionary<string, List<Dictionary<string, List<WebApplication1.Models.CountryViewModel>>>>();
newdic.Add("Table1", linqresult);
var linqjson = JsonConvert.SerializeObject(newdic, Formatting.Indented);

    The result is like this:

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search