Populate multiple objects from JSON

Question

I have this code:

public class Customer
{
    Int32 id { get; set; } = 0;
    User user1 { get; set; } = null;
    User user2 { get; set; } = null;
}

/* ... */

using (MySqlConnection conn = new MySqlConnection(Costanti.connessione))
{
    conn.Open();
    MySqlCommand m = new MySqlCommand("
SELECT c1.*, u1.*, u2.*
FROM customers as c1
inner join utenti u1 on u1.customer_id = c1.id
inner join utenti u2 on u2.customer_id = c1.id
", conn);

    MySqlDataReader x = m.ExecuteReader();
    DataTable dataTable = new DataTable();
    dataTable.Load(x);
    String json_string = Newtonsoft.Json.JsonConvert.SerializeObject(dataTable);

    List<Customer> lista = new List<Customer>();
    Newtonsoft.Json.JsonConvert.PopulateObject(json_string, lista);
    conn.Close();
}

How could I map c1.* fields of select to a generic Customer customer_1, and then u1.* and u2.* into customer_1's properties? Newtonsoft.Json.JsonConvert.PopulateObject doesn't let me do it.

The intermediate json_string looks like:

[
   {
      "id":1,
      "id_user":8,
      "name":"manuel",
      "id_user1":2,
      "name1":"michael"
   },
   {
      "id":2,
      "id_user":3,
      "name":"friedrich",
      "id_user1":6,
      "name1":"antony"
   }
]

And the result has to be a list composed by:

• Customer(with id=1), with User1(8,"manuel") and User2(2,"michael");
• Customer(with id=2), with User1(3,"friedrich") and User2(6,"antony").

Answer 1

The main reason your call to PopulateObject() is not working is that your c# data model does not match the schema of your JSON. If I use one of the tools from How to auto-generate a C# class file from a JSON object string to auto-generate a data model from your JSON, I get:

public class RootObject
{
    public int id { get; set; }
    public int id_user { get; set; }
    public string name { get; set; }
    public int id_user1 { get; set; }
    public string name1 { get; set; }
}

This looks nothing like your Customer class.

The secondary reason that PopulateObject() fails is that Json.NET will only populate public members by default -- and yours are all private.

To fix this, I might suggest skipping the DataTable and json_string representations entirely, and building your list directly from the IDataReader interface that MySqlDataReader implements:

var lista = x
    .SelectRows(r =>
        // Extract the row data by name into a flat object
        new
        {
            id = Convert.ToInt32(r["id"], NumberFormatInfo.InvariantInfo),
            id_user = Convert.ToInt32(r["id_user"], NumberFormatInfo.InvariantInfo),
            name = r["name"].ToString(),
            id_user1 = Convert.ToInt32(r["id_user1"], NumberFormatInfo.InvariantInfo),
            name1 = r["name1"].ToString(),
        })
    .Select(r =>
        // Convert the flat object to a `Customer`.
        new Customer
        {
            id = r.id,
            user1 = new User { Id = r.id_user, Name = r.name },
            user2 = new User { Id = r.id_user1, Name = r.name1 },
        })
        .ToList();

Using the extension method:

public static class DataReaderExtensions
{
    // Adapted from this answer https://stackoverflow.com/a/1202973
    // To https://stackoverflow.com/questions/1202935/convert-rows-from-a-data-reader-into-typed-results
    // By https://stackoverflow.com/users/3043/joel-coehoorn
    public static IEnumerable<T> SelectRows<T>(this IDataReader reader, Func<IDataRecord, T> select)
    {
        while (reader.Read())
        {
            yield return select(reader);
        }
    }
}

This assumes that your Customer data model now looks like:

public class Customer
{
    public Int32 id { get; set; }
    public User user1 { get; set; }
    public User user2 { get; set; }
}

public class User
{
    public Int32 Id { get; set; }
    public string Name { get; set; }
}

You could also combine the SelectRows and Select calls into a single method; I separated them for clarity. Skipping the DataTable and JSON representations should be simpler and more performant than your current approach.

Demo fiddle using a mockup data reader here.