Deserialize JSON local file to a List that is converted to DataTable

Question

Overview :
I'm trying to make an app that deserializes a local JSON to a List that is then converted to a DataTable, which is displayed into a DataGridView.

The data I'm working with is for a trading cards game JSON contains a list of cards and each card object get a bunch properties, lang, name etc...
There will be more details on the datasource below.

Problem :
I've a semi-working project but I can't figure out why some properties and data values are not passing into my List at deserialization as soon as objects are not RootObjectLevel.

Every time an abject is encapsulated with { } deeper than root level such as  legalities in the code below, my DataGridView just shows one column with (Collection) or namespace.classname instead of all the columns of properties.

Sample shrunken JSON code to describe the above (full JSON available after)

{
  "lang": "en",
  "set": "lea",
  "set_name": "Limited Edition Alpha",
  "collector_number": "142",
  "name": "Dwarven Demolition Team",
  "legalities": {
    "standard": "not_legal",
    "future": "not_legal",
    "historic": "not_legal",
    "gladiator": "not_legal",
    "pioneer": "not_legal",
    "modern": "legal",
    "legacy": "legal",
    "pauper": "not_legal",
    "vintage": "legal",
    "penny": "not_legal",
    "commander": "legal",
    "brawl": "not_legal",
    "duel": "legal",
    "oldschool": "legal",
    "premodern": "not_legal"
  },
}

A full JSON example that is returned for a card so you can see the entire thing:
https://api.scryfall.com/cards/03482c9c-1f25-4d73-9243-17462ea37ac4

You have the option to see the Raw Data and even format it so its humanly readable like above.

JSON data source – https://scryfall.com/docs/api/bulk-data

I'm using the « Default Cards » and « All Cards » Files, the second one being 1,2Go

Goal :
The normal behavior or the result I want to achieve is displaying all the 15 different objects that are in "legalities": { } as headers

| standard | future | historic | etc...

and the legal or not legal value that goes with it for each cards on each row.

Instead of having this result :

My guess :
I do not have any errors it works for everything that is RootObject but data deeper than RootObjectLevel are just returning null or are not populating the list properly somehow.

I suspect I need a custom converter, a token reader, maybe using a Dictionary or something along those lines but after looking for a week I'm just clueless, I'm just too new to C# I guess, its been like 2 weeks since I started coding.

I'm not even sure I would be able to remember all the things I tired so far, and list them here but there are not that many links that show up as "non read" when I try to once more make a new search to fix my problem.

So far I've been able to find what I need on Google, but this time I need some help.

Now the CODE :
pictureBox_Click to open my local JSON and write its path to a Label.Text

private void pictureBoxScryfallOpenFile_Click(object sender, EventArgs e)
{
    using (var openFileDialog = new OpenFileDialog())
    {
        openFileDialog.Title = "Please select a JSON file";
        openFileDialog.Filter = "JSON files (*.json) | *.json";
        openFileDialog.RestoreDirectory = true;

        if (openFileDialog.ShowDialog() == DialogResult.OK)
        {
            string fileName = openFileDialog.FileName;

            labelScryfallOpenedFile.Visible = true;
            labelScryfallOpenedFile.Text = fileName;
        }
    }
}

Then I'm grabbing the path from the Label.Text with label.Text.ToString() on a new buttom_Click to start deserializing.

Along with that I also have a ComboBox with 4 choices, each of them under a If call to trigger the good Class that have the JSONProperty I want to read from the file

Then I deserialize my list of objects as a List, which is converted to a DataTable that is set as DataSource for the DataGridView.

I'm shrinking the code with no ComboBox and minimal code to reproduce the problem.

using Newtonsoft.Json;

public void buttonReadScryfall_Click(object sender, EventArgs e)
{
    if (labelScryfallOpenedFile.Visible == true)
    {
        string jsonFilePath = labelScryfallOpenedFile.Text.ToString();

        using (var jsonFile = new StreamReader(jsonFilePath))
        {                   
            var jsonTextReader = new JsonTextReader(jsonFile);

            var serializer = new JsonSerializer();
            
            var cardLegalities = serializer.Deserialize<List<CardLegalities>>(jsonTextReader);

            DataTable dtLegalities = ConvertToDataTable(cardLegalities);

            dataGridView.DataSource = null;
            dataGridView.DataSource = dtLegalities;
            dataGridView.Refresh();                  
        }
    }
    else
    {
        MessageBox.Show("!!! No File Selected !!!"
                        + Environment.NewLine +
                        "Open a Local File Using the Appropriate Folder Icon"
                        + Environment.NewLine +
                        "Before Attempting to Deserialize a JSON");
    }
}

Note:
I need that StreamReader, it's even mandatory in that case, if I use a standard reader.ReadAllText() it ends up with an Out of Memory exception as the biggest file I'm working with is 1.2Go and we're talking about 300k+ rows with over a hundred of columns if I decide to go with the entire JSON properties that are available for each card.

I also need to have a conversion to a DataTable or else I'm not able to setup a search filter with DefaultView.RowFilter.

ConvertToDatable

public static DataTable ConvertToDataTable<T>(IList<T> data)
{
    PropertyDescriptorCollection properties =
        TypeDescriptor.GetProperties(typeof(T));
    
    DataTable table = new DataTable();

    foreach (PropertyDescriptor prop in properties)
        table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
    foreach (T item in data)
    {
        DataRow row = table.NewRow();
        foreach (PropertyDescriptor prop in properties)
            row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
        table.Rows.Add(row);
    }
    return table;
}

And finally the Class that goes with it:

using Newtonsoft.Json;

public class CardLegalities
{
    [JsonProperty("lang")]
    public string Lang { get; set; }

    [JsonProperty("set")]
    public string Set { get; set; }

    [JsonProperty("set_name")]
    public string SetName { get; set; }

    [JsonProperty("collector_number")]
    public string CollectorNumber { get; set; }

    [JsonProperty("name")]
    public string Name { get; set; }

    /// <summary>
    /// An object describing the legality of this card in different formats
    /// </summary>
    [JsonProperty("legalities")]
    public Legalities Legalities { get; set; }
}

public class Legalities
{
    [JsonProperty("standard")]
    public string Standard { get; set; }

    [JsonProperty("future")]
    public string Future { get; set; }

    [JsonProperty("historic")]
    public string Historic { get; set; }

    [JsonProperty("gladiator")]
    public string Gladiator { get; set; }

    [JsonProperty("pioneer")]
    public string Pioneer { get; set; }

    [JsonProperty("modern")]
    public string Modern { get; set; }

    [JsonProperty("legacy")]
    public string Legacy { get; set; }

    [JsonProperty("pauper")]
    public string Pauper { get; set; }

    [JsonProperty("vintage")]
    public string Vintage { get; set; }

    [JsonProperty("penny")]
    public string Penny { get; set; }

    [JsonProperty("commander")]
    public string Commander { get; set; }

    [JsonProperty("brawl")]
    public string Brawl { get; set; }

    [JsonProperty("duel")]
    public string Duel { get; set; }

    [JsonProperty("oldschool")]
    public string Oldschool { get; set; }

    [JsonProperty("premodern")]
    public string Premodern { get; set; }
}

I'm really curious to know how fix the problem, I'm pretty sure I'll see something that I've seen in my Google searches but unable to adapt to my needs since I'm just too new in C# coding.
I could eventually push the app to GitHub for you to clone the repository if that is more convenient.

Answer 1

To answer your main question, you could use the JsonPathConverter described in the second part of this answer to allow you to move all the Legalities properties into your CardLegalities class to make it easier to handle. You would just need to add the [JsonConverter(typeof(JsonPathConverter))] attribute to the CardLegalities class, and then prefix all the JsonProperty names for the nested properties with legalities., for example [JsonProperty("legalities.standard")].

This will get you most of the way there. However, you mentioned in the comments that some of your properties are arrays, such as colors, and you are not seeing any output for those in your DataGridView.

The problem here is that the DataGridView control ignores array columns when binding to a DataTable. You can test this easily enough with the following code:

DataTable dt = new DataTable();
dt.Columns.Add("Simple String", typeof(string));
dt.Columns.Add("Simple Int", typeof(int));
dt.Columns.Add("Array of String", typeof(string[]));
dt.Rows.Add("Foo", 25, new string[] { "A", "B" });

dataGridView1.DataSource = null;
dataGridView1.DataSource = dt;
dataGridView1.Refresh();

If you run the above code in a new Form containing a DataGridView, you'll see the following output, with the "Array of String" column conspicuously missing:

What this means is that you'll need to convert your array properties into simple strings (by concatenating the values) when you are doing your data table conversion. Here is an updated version of your ConvertToDataTable<T>() method which should do the trick:

public static DataTable ConvertToDataTable<T>(IList<T> data)
{
    PropertyDescriptorCollection properties =
        TypeDescriptor.GetProperties(typeof(T));

    DataTable table = new DataTable();

    foreach (PropertyDescriptor prop in properties)
    {
        Type dataType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
        if (dataType.IsArray) dataType = typeof(string);
        table.Columns.Add(prop.Name, dataType);
    }

    foreach (T item in data)
    {
        DataRow row = table.NewRow();
        foreach (PropertyDescriptor prop in properties)
        {
            object val = prop.GetValue(item);
            if (val != null && prop.PropertyType.IsArray)
            {
                var items = ((IEnumerable)val)
                    .Cast<object>()
                    .Where(o => o != null)
                    .Select(o => o.ToString());

                val = string.Join(", ", items);
            }
            row[prop.Name] = val ?? DBNull.Value;
        }
        table.Rows.Add(row);
    }
    return table;
}

Answer 2

You could also give a try Cinchoo ETL, an open source library for this type of conversion

Here is the sample code

using ChoETL;

ChoETLSettings.NestedKeySeparator = '.';
using (var r = new ChoJSONReader<CardLegalities>("*** YOUR JSON FILE PATH ***")
    )
{
    var dt = r.AsDataTable();
    Console.WriteLine(dt.Dump());
}

Disclaimer: I'm author of this library