C#: Creating child objects

I've got a PostgreSQL database that I've built a ADO.NET Entity Data Model attached

Code-first or database-first?

If database-first, how does your data model look like? What are its entities? Also explain the cardinality 0..1 of your subdivions. Seems strange to me.

 database-first

Here is how I created the 2 database tables

CREATE TABLE geonames_countrySubdivision
(
    id SERIAL PRIMARY KEY, 
    countryCode varchar, 
    countryName varchar,                
    adminName1 varchar,      
    adminCode1 varchar,  
    adminName2 varchar, 
    adminCode2 varchar,   
    adminName3 varchar, 
    adminCode3 varchar, 
    adminName4 varchar,    
    adminCode4 varchar,   
    adminName5 varchar, 
    adminCode5 varchar, 
    distance integer,
    pointonsurface_id integer,
    subdivision_code varchar, 

);

CREATE TABLE countrySubdivision_code
(
    id SERIAL PRIMARY KEY, 
    code varchar, 
    level varchar, 
    type varchar,
      CONSTRAINT fk_geonames_countrySubdivision
      FOREIGN KEY(id) 
     REFERENCES geonames_countrySubdivision(id) 
);

Select allOpen in new window

- Why are all columns nullable?
- geonames_countrySubdivision(countryName) seems redundant. The geonames_countrySubdivision table should point to a contry tables for that.
- geonames_countrySubdivision(distance) is the distance to what?
- countrySubdivision_code(code) or countrySubdivision_code(code, type) look like they must be unique.
- Why adminXXX1 to adminXXX5? Looks like a repeating group.
- geonames_countrySubdivision.subdivision_code and countrySubdivision_code.id seem to be redundant:

CREATE TABLE geonames_countrySubdivision
(
    id SERIAL PRIMARY KEY,
    pointonsurface_id integer,
    subdivision_code varchar
);

CREATE TABLE countrySubdivision_code
(
    id SERIAL PRIMARY KEY,
    code varchar,
    level varchar,
    type varchar,
    CONSTRAINT fk_geonames_countrySubdivision
        FOREIGN KEY(id) REFERENCES geonames_countrySubdivision(id)
);

Select allOpen in new window

For the missing Add() method. You're using an array for the codes. Switch to a List<Code>, then you'll have an Add() method.

To answer your question bellow is a sample from the geonames.org api the docs  suggested there might be 6 admin levels hence adminName5 in my  code
depending on the location of your point will  return different admin levels this is why they are nullable

<geonames>
<countrySubdivision>
<countryCode>GB</countryCode>
<countryName>United Kingdom</countryName>
<adminCode1>ENG</adminCode1>
<adminName1>England</adminName1>
<code level="1" type="ISO3166-2">ENG</code>
<adminCode2>C3</adminCode2>
<adminName2>Cambridgeshire</adminName2>
<code level="2" type="ISO3166-2">CAM</code>
<adminCode3>12UE</adminCode3>
<adminName3>Huntingdonshire</adminName3>
<adminCode4>12UE085</adminCode4>
<adminName4>Yelling</adminName4>
<distance>0</distance>

</countrySubdivision>
</geonames>

Select allOpen in new window

For the missing add method. Your using an array for the codes. Switch to a List<Code>, then you'll have an Add method.

Do you mean something like this? Which errors

List<Rootobject> CountrySubdivision = new List<Rootobject>();

                        CountrySubdivision = JsonConvert.DeserializeObject<Rootobject>(myJSON);

Select allOpen in new window

From the given XML, I think the data model should look like this, as there are three entities:

countrySubdivisions ( countryCode, countryName, distance )
                    PK ( countryCode )

admins              ( countryCode, number, code, name )
                    PK ( countryCode, number )
                    FK ( countryCode -> countrySubdivision.countryCode )

codes               ( countryCode, level, type, value )
                    PK ( countryCode, level )
                    FK ( countryCode -> countrySubdivision.countryCode )

Select allOpen in new window

I've updated CountrySubdivision.cs  as suggested I still don't  have access to "add" or "code" here

                        foreach (var item in CountrySubdivision.codes)
                        {
                            var NewCountrySubdivisionCode = new countrysubdivision_code
                            {
                                code = item.code,
                                type = item.type,
                                level = item.level
                            };
                            NewGeonamesCountrySubdivision. // no add
                        }

Select allOpen in new window

Not certain your DB structure  will work in this application
Is this the reason why I can't add to child table?

NewGeonamesCountrySubdivision. // no add

Select allOpen in new window

Requires a list or the DBContext.

I'm inside

using (var contex = new gazetteerEntities())
{
...........
   foreach (var item in CountrySubdivision.codes)
    {                             
      var NewCountrySubdivisionCode = new countrysubdivision_code                             {
         code = item.code,                                 
         type = item.type,
         level = item.level                             
      };
        NewGeonamesCountrySubdivision. // no add
    }

}

Select allOpen in new window

So I'm not certain what you mean

HI
I believe I've resolved the problem by adding a column to hold the  FOREIGN KEY so  the new child table  looks like this

CREATE TABLE geonames_countrysubdivision_code
(
    id SERIAL PRIMARY KEY, 
    gcs_id integer,
    code varchar, 
    level varchar, 
    type varchar,
      CONSTRAINT fk_geonames_countrysubdivision
      FOREIGN KEY(gcs_id) 
     REFERENCES geonames_countrysubdivision(id) 
);

Select allOpen in new window

and the data model looks like  


I can then do this to create the parent and child

                        // ToDo Update the database
                        var NewGeonamesCountrySubdivision = new geonames_countrysubdivision
                        {
                            pointonsurface_id = POS_ID,
                            countryname = countryName,
                            countrycode = iso_3166_alpha2,
                            admincode1  = adminCode1,
                            adminname1  = adminName1,
                            admincode2 = adminCode2,
                            adminname2 = adminName2,
                            admincode3 = adminCode3,
                            adminname3 = adminName3,
                            admincode4 = adminCode4,
                            adminname4 = adminName4,
                            admincode5 = adminCode5,
                            adminname5 = adminName5,
                            distance   = CountrySubdivision.distance,
                            subdivision_code = subdivision_code

                        };

                        foreach (var item in CountrySubdivision.codes)
                        {
                            var NewCountrySubdivisionCode = new geonames_countrysubdivision_code
                            {
                                code = item.code,
                                type = item.type,
                                level = item.level
                            };
                            NewGeonamesCountrySubdivision.geonames_countrysubdivision_code.Add(NewCountrySubdivisionCode);
                        }
                        contex.geonames_countrysubdivision.Add(NewGeonamesCountrySubdivision);
                        contex.SaveChanges();

Select allOpen in new window

That's why I've asked you about the model and the cardinality.

But the structure does still look a bit off. Are you sure that the hierarchies have the correct order?

In what way do you think they are "a bit off"?
The main geonames_countrysubdivision  table and the child geonames_countrysubdivision_code table are derived from the sample   from the geonames.org api I showed before

I don't have read that documentation, but the semantics your model make me think the the direction of the relationships is inverse.

OK I get what you mean now Hierarchical logic would be something like this

countryCode => GB countryName => United Kingdom
      adminName1 => England adminCode1 => ENG code => ENG level =>1
         adminName2 => Cambridgeshire  adminCode2 => C3 code => CAM level =>2
            adminName3 => Huntingdonshire adminCode3 => 12UE
               adminName4 ect
      

Select allOpen in new window

With the pointonsurface_id inserted at whatever lowest admin level (Highest adminName number) is returned from the api
For the UK it would be adminName4 parts of the US adminName3 where as Thailand adminName1

Example

<geonames>
<countrySubdivision>
<countryCode>TH</countryCode>
<countryName>Thailand</countryName>
<adminCode1>63</adminCode1>
<adminName1>Krabi</adminName1>
<code level="1" type="ISO3166-2">81</code>
<distance>0</distance>
</countrySubdivision>
</geonames>

Select allOpen in new window

I'm guessing I can do  that with a Look up table (admin table name, level ID,  pointonsurface_id )

I'm going to have to think about this more

Sorry if going off topic

No problemo.

Hi
I'm undecided whether to simple  inverse the two tables so that the codes table becomes the parent of admin or have multiple tables
In both options:
The C# thing I don't quite    understand is how the EF handles unique parents


The iso-3166 codes are unique so if this was the parent table an entry for  the UK would be created once
In pure SQL before creating an entry for England you would need to search for the primary key for the UK then use it as foreign key

In C# you'd create the UK object & add an England Object to it before saving changes
What happens when the UK entry is already  stored in the database?

Hi
I've been playing in order to simplify & test I  created a mock up bellow
What I've discovered you need to test if parent (Country) exists before creating a new 1 if not you can create it's child (Admin_1) & grand children (Admin2) etc If it dose exist the code gets complicated very quickly with testing at each stage

Is there a way to avoid the nested 'if/else' statements

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using EFchildParent;

namespace TestInsert
{
    class Program
    {
        static void Main(string[] args)
        {
            var Places = new Dictionary<string, string>();
            Places.Add("TRE", "Trevorshire" );
            Places.Add("KYL", "Kylieshire" );

            foreach(var county in Places)
            {
                Console.WriteLine(county.Key + " " + county.Value);
                using (var contex = new EFchildParentEntities())
                {
                    var Results = contex.Countrys.SingleOrDefault(c => c.countryCode == "GB");
                    if (Results == null)
                    {
                        var NewCountry = new EFchildParent.Country
                        {
                            countryCode = "GB",
                            countryName = "United Kingdom"
                        };
                        var NewAdmin1 = new EFchildParent.Admin_1
                        {
                            countryCode= "GB",
                            adminCode1 = county.Key,
                            adminName1 = county.Value,
                        };
                        NewCountry.Admin_1.Add(NewAdmin1);
                        // create next child admin2 add to Admin1 etc.
                        contex.Countrys.Add(NewCountry);
                    }
                    else
                    {
                        var results = contex.Admin_1.SingleOrDefault(a => a.adminCode1 == county.Key);
                        if (results == null) 
                        {
                            var NewAdmin1 = new EFchildParent.Admin_1
                            {
                                countryCode = "GB",
                                adminCode1 = county.Key,
                                adminName1 = county.Value,
                            };
                            // create next child admin2 add to Admin1
                            //      create next child admin3 add to Admin2
                            contex.Admin_1.Add(NewAdmin1);
                        }
                        else
                        {
                            // Test if Admin_2 exist in Database 
                            Console.WriteLine("create new child admin2 + admin3 etc");
                            // else Test if Admin_3 exist in Database
                            //          if Admin_3 exist create admin4

                            //   else   create next child admin3 + admin4
                        }

                    }

                    contex.SaveChanges();
                }

            }
            Console.WriteLine("Finished");
            Console.ReadLine();
        }
    }
}

Select allOpen in new window

 

Do you know of an online tutorial that I could work through?
I clearly have a lot to learn