Parse JSON Object / Array of Strings into new Custom Class

Question

I'm trying to parse some JSON data and ultimatley store it in a database.

I'm having issues when storing a collection of strings / values which are not objects themselves.

For example - The "callingCodes" & "altSpellings"

I want to store these in an SQL table which will have reference to the country they belong to.

This is an example of the JSON:

{
   "name":"Puerto Rico",
   "topLevelDomain":[
      ".pr"
   ],
   "alpha2Code":"PR",
   "alpha3Code":"PRI",
   "callingCodes":[
      "1787",
      "1939"
   ],
   "capital":"San Juan",
   "altSpellings":[
      "PR",
      "Commonwealth of Puerto Rico",
      "Estado Libre Asociado de Puerto Rico"
   ],
   "region":"Americas",
   "subregion":"Caribbean",
   "population":3474182,
   "latlng":[
      18.25,
      -66.5
   ]
},

I originally created some C# classes to represent the data using http://json2csharp.com/

This sugguested I store the values as a list of strings, which I did:

 public List<string> CallingCodes { get; set; }

I now want to store the data in a table, so I created a class "TopLevelDomain" to store / link the data to the parent country:

 public class CallingCode
    {
        public int ID { get; set; }
        public int CountryID { get; set; }
        public string Code{ get; set; }
    }

So I altered the parent to be as follows:

public ICollection<CallingCode> CallingCodes { get; set; }

Is it possible to direct the string values into the "Code" property of my new class?

Or am I trying to crowbar two pieces of logic into one?

Is the correct way to have models for the JSON, and manually restructure these into my new DB / Entity Framework Models?

EF Core does not support collection of primitive types directly. Specifically each property in the class which is not navigation is mapped to one column in database. It is not possible to store string array in its form to any relational database. In future when document databases are supported in EF Core, you can map JSON directly to that. — Smit
– Smit, Commented Jan 28, 2018 at 9:27
If you want to store above data in a relational database, you can mark the list of string as notmapped property and have another property which would serialize/de-serialize List<String> into single string which can be stored on server. — Smit
– Smit, Commented Jan 28, 2018 at 9:28
@Smit - I think you've best understood what I'm trying to achieve. I am extremely new to all this, so can you explain the best way to have another property. I have created a new class and mapped the items to this. But it doesn't populate when the rootobject when being called from the database — Andrew Birks
– Andrew Birks, Commented Jan 28, 2018 at 14:12

Smit · Accepted Answer · 2018-04-25 22:07:28Z

This is the auto-generated class you get from such JSON. The tricky bit here is List of primitive types.

public class RootObject
{
    public string name { get; set; }
    public List<string> topLevelDomain { get; set; }
    public string alpha2Code { get; set; }
    public string alpha3Code { get; set; }
    public List<string> callingCodes { get; set; }
    public string capital { get; set; }
    public List<string> altSpellings { get; set; }
    public string region { get; set; }
    public string subregion { get; set; }
    public int population { get; set; }
    public List<double> latlng { get; set; }
}

Certain databases like PostgreSQL supports array as primitive type. If you are using PostgreSQL then you can perhaps make those properties array of primitive type and store them on server as is.

For other databases which does not support array, you cannot store a list of primitive values into single column of database. The easiest way to deal with it is to introduce serialization and create single string which can be stored to server. So looking at above class, for public List<string> topLevelDomain property, you can rewrite it in following way,

[NotMapped]
public List<string> topLevelDomain
{
    get => Deserialize(TopLevelDomainString);
    set => TopLevelDomainString = Serialize(value);
}
public string TopLevelDomainString { get; set; }

With NotMapped attribute EF will not map topLevelDomain property. But TopLevelDomainString will be persisted to database and it will get values from topLevelDomain. As for Serialize/Deserialize methods, you can use any serialization method. You can use JsonSerializer directly (since you are already using JSON objects. Or you can just combine strings using , as delimiter and split string from server using it.

Starting with EF Core 2.1 version, you can use Value-Conversion feature directly to provide funcs to do conversion (essentially serialization code like above) to EF and EF will do it while reading/saving data from/to server. This will avoid you having to create additional CLR property.

Alex · Accepted Answer · 2018-01-27 15:12:38Z

Here is your auto-generated class:

    public class RootObject
    {
        public string name { get; set; }
        public List<string> topLevelDomain { get; set; }
        public string alpha2Code { get; set; }
        public string alpha3Code { get; set; }
        public List<string> callingCodes { get; set; }
        public string capital { get; set; }
        public List<string> altSpellings { get; set; }
        public string region { get; set; }
        public string subregion { get; set; }
        public int population { get; set; }
        public List<double> latlng { get; set; }
    }

Let's prepare another simple one:

    public class MyRootObject
    {
        public MyRootObject(RootObject root)
        {
            Name = root.name;
            List<CallingCode> callingCodesConverted = new List<CallingCode>();

            foreach (string code in root.callingCodes)
            {
                CallingCode newCode = new CallingCode() { Code = code };
                callingCodesConverted.Add(newCode);
            }

            CallingCodes = callingCodesConverted;
        }

        public string Name { get; set; }

        public List<CallingCode> CallingCodes { get; set; }
    }

Now you could first do encoding from json to class RootObject, and then create MyRootObject based on it:

        string path = @"D:\test.txt";

        var r = new StreamReader(path);
        var myJson = r.ReadToEnd();

        RootObject root = Json.Decode<RootObject>(myJson);

        MyRootObject myroot = new MyRootObject(root);

Sure MyRootObject is only an example.

Hey24sheep · Accepted Answer · 2018-01-27 14:53:24Z

Is the correct way to have models for the JSON, and manually restructure these into my new DB / Entity Framework Models?

Well some might use that in their code and some might do even worse than that. But, I personally like to use models/dtos for the things I could and know about the data.

am I trying to crowbar two pieces of logic into one?

Yes. But, it depends. Either strongly type/define objects and all or just Ser/Deser everytime.

Your data is known (no unkown properties or anything, so why serialize and deserialize it everytime?)

Solution 1 : if you use the JSON as is to create a DB Entry

Entites

public class Country //assuming this is country data
{
    public int Id { get; set; }

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

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

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

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

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

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

    [JsonProperty("population")]
    public long Population { get; set; }

    [JsonProperty("topLevelDomain")]
    public virtual List<TopLevelDomain> TopLevelDomains { get; set; }

    [JsonProperty("callingCodes")]
    public virtual List<CallingCodes> CallingCodes { get; set; }

    [JsonProperty("altSpellings")]
    public virtual List<AltSpellings> AltSpellings { get; set; }

    [JsonProperty("latlng")]
    public virtual List<Coordinates> Coordinates { get; set; }
}

public class TopLevelDomain
{
    public int Id { get; set; }

    [ForeignKey("Country")]
    public int CountryId {get; set; }

    public virtual Country Country { get; set; }

    public string DomainName { get; set; }
}

public class CallingCodes
{
    public int Id { get; set; }

    [ForeignKey("Country")]
    public int CountryId {get; set; }

    public virtual Country Country { get; set; }

    public string Code { get; set;} // either store it as String
    //OR
    public long Code { get; set;}
}

public class AltSpellings
{
    public int Id { get; set; }

    [ForeignKey("Country")]
    public int CountryId {get; set; }

    public virtual Country Country { get; set; }

    public string AltSpelling { get; set; }
}

public class Coordinates
{
    public int Id { get; set; }

    [ForeignKey("Country")]
    public int CountryId {get; set; }

    public virtual Country Country { get; set; }

    public double Coordinates { get; set; } //again either as string or double, your wish. I would use double
}

Use it like so

//assuming using Newtonsoft

var myJson = ....assuming one Country;
var myJsonList = ...assuming List<Country>;

var country = JsonConvert.DeserializeObject<Country>(myJson);
var countries = JsonConvert.DeserializeObject<List<Country>>(myJson);

Solution 2 : First one can cause too many tables for a little data but First solution is a little more object based and typed, so Here is another one

Entity

public class Country //assuming this is country data
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string Alpha2Code { get; set; }

    public string Alpha3Code { get; set; }

    public string Capital { get; set; }

    public string Region { get; set; }

    public string Subregion { get; set; }

    public long Population { get; set; }

    public string TopLevelDomains { get; set; }

    public string CallingCodes { get; set;}

    public string AltSpellings { get; set; }

    public double Longitude { get; set;}

    public double Latitude { get; set; }
}

Model

public class CountryJson 
{
    public int Id { get; set; }

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

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

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

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

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

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

    [JsonProperty("population")]
    public long Population { get; set; }

    [JsonProperty("topLevelDomain")]
    public List<string> TopLevelDomains { get; set; }

    [JsonProperty("callingCodes")]
    public List<string> CallingCodes { get; set;}

    [JsonProperty("altSpellings")]
    public List<string> AltSpellings { get; set; }

    [JsonProperty("latlng")]
    public List<string> Latlng { get; set; }
}

Use it like so

//assuming using Newtonsoft

var myJson = ....assuming one Country;

var countryJson = JsonConvert.DeserializeObject<CountryJson>(myJson);
//Write a Mapper Or Manual Map like below
var countryEntity = new Country 
{
    Name = countryJson.Name,
    ...
    TopLevelDomains = JsonConvert.Serialize(countryJson.TopLevelDomains),
    CallingCodes = JsonConvert.Serialize(countryJson.CallingCodes),
    ...//same for all list (NOTE: YOU NEED TO DESERIALIZE IT WHEN YOU FETCH IT FROM DB
    Longitude = countryJson.Latlng.ElementAt(0),//assuming 0 is longi, 1 is lat
    Latitude = countryJson.Latlng.ElementAt(1)//you can do it like above as well as string if you want
}

Collectives™ on Stack Overflow

Parse JSON Object / Array of Strings into new Custom Class

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related