Allow multiple values for the same key when creating JSON using ExpandoObject and IDictionary

Question

I am trying to create dynamic JSON using ExpandoObject and IDictionary.

During the dynamic creation of JSON there could be instances when the Name or Value would repeat. However when adding the repeated Name or Value to the ExpandoObject, it gives an error:

An item with the same key has already been added.

Below is my code snippet :

DataTable dt_MappedColumns = (DataTable)ViewState["MappedColumns"];
dynamic ManCols = new ExpandoObject();
var dictionary1 = (IDictionary<string, object>)ManCols;
foreach (DataRow dr in dt_MappedColumns.Rows)
{
     dictionary1.Add(dr["TColumnName"].ToString(), dr["AColumnName"].ToString());
}
string Manjson = Newtonsoft.Json.JsonConvert.SerializeObject(dictionary1);

The DataTable looks like this:

Sr.No TColumnName AColumnName
----- ----------- -----------
1     Apple       Lion
2     Orange      Tiger
3     Mango       Fox
4     Orange      Wolf

In the above table the first 3 Rows are added successfully into dictionary1; however, when we try to add the fourth Row, it gives the error.

My desired JSON structure for repeated values would look like this:

{"Apple":"Lion", "Orange":["Tiger","Wolf"], "Mango":"Fox"}

Is it possible to create this JSON structure from the table?

Rather than just providing a code snippet with data that we don't know about, it would be far more useful to create a [mcve] that we could run, and that includes all the data needed to reproduce the problem - ideally with an explanation of what you want the output to be too. — Jon Skeet, Dec 06 '18 at 11:29
Not very much - I still can't copy/paste/compile/run that code. (Does your question actually rely on it being in a DataTable to start with? I don't see why it should.) But fundamentally your desired JSON is a bad one, with explicitly non-deterministic results - see https://tools.ietf.org/html/rfc7159#section-4. — Jon Skeet, Dec 06 '18 at 12:17
To clarify JonSkeet''s comment: its a bad idea to create a json that *sometimes* has a list, but other times just has a single value. This is a serious burden on whoever receives that json. The correct solution is to *always* create a list, for any property that *permits* multiple copies. Here, "Orange" is allowed to have duplicates, so **always** make it a list. One value: `{"Orange": ["Tiger"]}`. If *any* key is allowed to have duplicates, than make them all lists. In that case, output would be `{"Apple": ["Lion"], "Orange": ["Tiger", "Wolf"], "Mango": ["Fox"]}`. Much easier to process. — ToolmakerSteve, Mar 10 '21 at 00:43

score 4 · Accepted Answer · answered Dec 06 '18 at 16:06

Sure this is possible. Inside your loop you just need to check whether the key already exists in the dictionary and take the appropriate action. There are three cases:

The key doesn't exist, so add it as you are doing now.
The key exists and the existing value is a string, in which case you need to replace it with a list containing the old string value and the new string value.
The key exists and the existing value is a list, in which case you just need to add the new string to the list.

Here is what the code looks like:

foreach (DataRow dr in dt_MappedColumns.Rows)
{
    string key = dr["TColumnName"].ToString();
    string value = dr["AColumnName"].ToString();

    if (!dictionary1.ContainsKey(key))
    {
        // key does not already exist, so add it
        dictionary1.Add(key, value);
    }
    else
    {
        // key exists, get the existing value
        object existingValue = dictionary1[key];

        if (existingValue is string)
        {
            // replace the existing string value with a list
            dictionary1[key] = new List<string> { (string)existingValue, value }; 
        }
        else
        {
            // the existing value is a list, so add the new value to it
            ((List<string>)existingValue).Add(value);
        }
    }
}

Fiddle: https://dotnetfiddle.net/PERc0D

Just Awsome .... works like a Charm :) Thanks @BrainRogers for the invaluable help.. After running the above code, I get the Json String with escape characters since I am using Visual Studio... "{\"Apple\":\"Lion\",\"Orange\":[\"Tiger\",\"Wolf\"],\"Mango\":\"Fox\"}" How can we get remove the Escape '\' characters from the Json string.. — Mufaddal, Dec 07 '18 at 08:47
@Mufaddal If you are getting escape characters in the JSON you are probably double-serializing the data. This happens when you use `JsonConvert.SerializeObject()` to serialize your data AND you also return the data using a framework method like `Json()` in ASP.NET MVC. See [Invalid format with Json when converting DataTable with Json.Net](https://stackoverflow.com/q/19156485/10263) for an example and how to fix it. — Brian Rogers, Dec 07 '18 at 16:12
Thank you for the response, however I resolved the issue by converting it into a JObject and then using it as follows: var Varjson = JObject.Parse(Manjson); and then using the Varjson Object. :) Cheers. — Mufaddal, Dec 10 '18 at 13:04

Allow multiple values for the same key when creating JSON using ExpandoObject and IDictionary

1 Answers1

Linked