Question 109628 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

C# JSON deserialization of a typed dataset with foreign key constraints

I’m attempting to serialize and deserialize typed DataSets to JSON using C# and the NewtonSoft.Json package. This works fine except for DataSets that include a foreign key constraint and the parent table is added to the designer after the child table. In the auto-generated Designer.cs, the parent table is added to the base DataTableCollection after the child table.

When the DataSet is serialized to JSON the child table is serialized first. Then when deserializing the JSON back to an instance of the typed DataSet, an InvalidConstraintException is thrown ("ForeignKeyConstraint requires the child key values to exist in the parent table").

To setup a test scenario, I created a very simple typed DataSet named ChildParentDataSet. It contains two tables. The first table is named Child and it has one column named ParentId. The second table is named Parent and it has one column named Id. A foreign key is created between Parent.Id and Child.ParentId. It’s important for the Parent table to be added in the designer after the Child table. This code results in an InvalidConstraintException on the last line:

ChildParentDataSet ds = new ChildParentDataSet();
ds.Parent.Rows.Add(1);
ds.Child.Rows.Add(1);
string json = Newtonsoft.Json.JsonConvert.SerializeObject(ds);
ChildParentDataSet? ds2 = Newtonsoft.Json.JsonConvert.DeserializeObject<ChildParentDataSet>(json);

My preferred approach is to handle this during deserialization by implementing my own CustomCreationConverter that initializes DataSet.EnforceConstraints to false prior to performing the deserialization, and then when deserialization is complete I would set EnforceConstraints back to true. Here are the classes that I hoped would accomplish this:

public class DataSetDeserializer
{
   public static T DeserializeDataSet<T>(string json)
   {
      var ds = Newtonsoft.Json.JsonConvert.DeserializeObject<T>(json, new DataSetConverter<T>());
      System.Data.DataSet? dataSet = (System.Data.DataSet?)(Object?)ds;
      dataSet.EnforceConstraints = true;
      return ds;
   }

   private class DataSetConverter<T> : Newtonsoft.Json.Converters.CustomCreationConverter<T>
   {
      public override T Create(Type objectType)
      {
         var ds = Activator.CreateInstance(objectType);
         System.Data.DataSet? dataSet = (System.Data.DataSet?)ds;
         dataSet.EnforceConstraints = false;
         return (T)ds;
      }
   }
}

Then I changed the last line of my test code to use the DataSetDeserializer class rather than Newtonsoft for deserialization:

ChildParentDataSet? ds2 = DataSetDeserializer.DeserializeDataSet<ChildParentDataSet>(json);

When running this code a Newtonsoft.Json.JsonSerializationException is thrown from the call to DeserializeObject. The exception message is "Cannot populate JSON object onto type ‘ChildParentDataSet’. Path ‘Child’, line 1, position 9.’" I haven’t been able to figure this out.

Another option I considered is to handle this during serialization rather than deserialization. I implemented a method that sorts the tables in a DataSet such that the highest-level parent tables would be first and the lowest-level child tables would be last. Then I serialize each table one at a time in the correct order and concatenate all the JSON strings. That works, but critically it doesn’t handle the case when a table is a parent and child of itself via a self-referential foreign key. I can probably figure out how to handle that scenario but it would be substantially more difficult. Plus my current implementation is quite inefficient versus serializing an entire dataset with one call.

One option that would work but I really don’t want to do is to use XML instead of JSON. It would certainly be easier since DataSets have a GetXml function, but XML is much more verbose and I’m passing this data over the wire via gRPC. Plus it would require a very complicated redeployment of many clients and services.

I’m certainly open to suggestions. I’m sure there are better alternatives that I haven’t considered.

Edit: @Serge suggested that I post the code of ChildParentDataSet. Unfortunately that’s a lot of auto-generated code that is way too big to post here, but he then suggested that I create something equivalent in code. I did so and here it is:

internal class ChildParentDataSet2: DataSet
{
   public DataTable Child { get; set; }
   public DataTable Parent { get; set; }

   public ChildParentDataSet2()
   {
      this.Child = this.Tables.Add("Child");
      DataColumn childColumn = this.Child.Columns.Add("ParentId", typeof(int));
      this.Parent = this.Tables.Add("Parent");
      DataColumn parentColumn = this.Parent.Columns.Add("Id", typeof(int));
      this.Parent.PrimaryKey = new DataColumn[] { parentColumn };
      this.Child.ParentRelations.Add(parentColumn, childColumn);
   }
}

Strangely when using this class instead of the auto-generated one, I can deserialize it successfully when using my DataSetDeserializer and CustomCreationConverter. So the problem apparently lies in the typed dataset which is too large to post here.

Edit 2: The ChildParentDataSet (created using the DataSet designer in Visual Studio) and the ChildParentDataSet2 (created manually as shown above) both produce the same serialized JSON: {"Child":[{"ParentId":1}],"Parent":[{"Id":1}]}. Now I just need to figure out why my DataSetDeserializer works fine with ChildParentDataSet2 but not with ChildParentDataSet. Thanks everyone for your help so far!

Edit 3: I think I figured out why my CustomCreationConverter worked for the ChildParentDataSet2 that I wrote manually and not for the ChildParentDataSet that I created via the designer. By using a CustomCreationConverter I was missing out on all the functionality that Newtonsoft provides in its own DataSetConverter class. So my current solution is to create a fork of Newtonsoft’s DataSetConverter class on Github. That fork simply sets EnforceConstraints to false prior to deserialization and sets it back to true after. I also created a pull request but there might be performance implications or other reasons that the author won’t want to include my changes.

Tags:

2

Answers


  1. 0

    I recommend to change your DataSet constructor, adapting it to a json string. Since a json string doesn’t contain any relations, you will have to add them each time.

    public class ChildParentDataSet2 : DataSet
{
    public DataTable Child { get; set; }

    public DataTable Parent { get; set; }

    private void CreateDataSet(DataTable parent, DataTable child)
    {
        Tables.Add(child);
        this.Child = Tables[0];
        Tables.Add(parent);
        this.Parent = Tables[1];

        if (this.Parent.Columns.Count == 0)
        {
            this.Parent.Columns.Add("Id", typeof(int));
            this.Child.Columns.Add("ParentId", typeof(int));
        }

        var parentColumn = this.Parent.Columns["Id"];
        var childColumn = this.Child.Columns["ParentId"];

        this.Parent.PrimaryKey = new DataColumn[] { parentColumn };
        this.Child.ParentRelations.Add(parentColumn, childColumn);

        this.Parent.TableName = "Parent";
        this.Child.TableName = "Child";
    }

    public ChildParentDataSet2(string json)
    {      
            var jObj=JObject.Parse(json);
            CreateDataSet(jObj["Parent"].ToObject<DataTable>(), jObj["Child"].ToObject<DataTable>());
    }

    public ChildParentDataSet2(JObject jObj)
    {
        CreateDataSet(jObj["Parent"].ToObject<DataTable>(), jObj["Child"].ToObject<DataTable>());
    }
    public ChildParentDataSet2()
    {
        CreateDataSet(new DataTable(), new DataTable());
    }
}

    code for a test

        var ds = new ChildParentDataSet2();
    ds.Parent.Rows.Add(1);
    ds.Child.Rows.Add(1);

    string json = Newtonsoft.Json.JsonConvert.SerializeObject(ds);

    var ds2 = new ChildParentDataSet2(json);
    
     ds2.Child.Rows.Add(3); // causes error key violation

    If you like , you can create a custom converter

    var ds2 = JsonConvert.DeserializeObject<ChildParentDataSet2>(json, new DataSetConverter());


public class DataSetConverter : JsonConverter
{
    public override bool CanConvert(Type objectType)
    {
        return (objectType == typeof(DataSet) || objectType == typeof(ChildParentDataSet2));
    }

    public override ChildParentDataSet2 ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        var jObj = JObject.Load(reader);

        return new ChildParentDataSet2(jObj["Parent"].ToObject<DataTable>(), jObj["Child"].ToObject<DataTable>());
    }
    public override bool CanWrite
    {
        get { return false; }
    }
    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        throw new NotImplementedException();
    }
}

    for this converter you will need to add this constructor (or replace existing)

        public ChildParentDataSet2(DataTable parent, DataTable child)
    {
        CreateDataSet(parent, child);
    }
    Login or Signup to reply.
  2. 0

    i’m doing something similar in aspnet using dataset to json for angularjs and then back json to dataset. In case you have a foreign key constraints you need just make json conversion in the correct order. If table2 is a child of table1 it has to be oredered table1, table2 in json string. Then you put in data for each table. This is important for JsonToData conversion.. like that:

    .cs 
  dsjson = JsonConverter.DataSetToJson(ds);    
.js on init:
      $scope.data = {"table1": [],"table2": []} // in correct order
    then fill:
      var json = jQuery.parseJSON(dsjson);
      scope.data.table1= json.table1; // 
      scope.data.table2= json.table2;
    
.cs and when you go back to dataset, also correct order
    jds = JsonConverter.JsonToData<Ds_xxx>(json);
    var ds = new Ds_xxx()
    ds.Merge(jds.table1);
    ds.Merge(jds.table2);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search