C#: ADO.NET Entity Data Model with PostgreSQL crashes

Looks like you are using EF Core not ADO. Please confirm.

Yes apologies Entity Framework with an  ADO Data  model 

Please post your entire code (remove passwords/addresses/names etc).

Please note I'm using npgsql for PostgrSQL I have not been able to successfully crate a project that can access the PostGIS geometry columns

using GeoNameToPostgreSQL;
using Newtonsoft.Json;
using Npgsql;
using System;
using System.Linq;
using System.Net;
using System.Text;


using System.Configuration;
using System.Threading.Tasks;
using static GetGeoNamesData.CountrySubdivision;


namespace GetGeoNamesData
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.OutputEncoding = System.Text.Encoding.UTF8;
            try
            {
                using (var contex = new gazetteerEntities ())
                {
                    var PointOnSurfaces = contex.admin_areas
                        .Where(a => a.fcode == "ADM2" && a.subdivision_code == null).Take(5);


                    if (PointOnSurfaces == null)
                    {
                        Console.WriteLine("Boo Who");
                    }
                    else
                    {
                        foreach (var PointOnSurface in PointOnSurfaces)
                        {
                            var POS_ID = PointOnSurface.id;
                            var db_lat = PointOnSurface.latitude;
                            var db_Long = PointOnSurface.longitude;


                            Console.WriteLine("ID:{0}, Lat: {1}, Long: {2}", POS_ID, db_lat, db_Long);
                            string URL = "http://api.geonames.org/countrySubdivisionJSON?lat=" + db_lat + "&lng=" + db_Long + "&username=PASSWORD&level=5";


                            WebClient client = new WebClient();
                            client.Encoding = Encoding.UTF8;
                            string myJSON = client.DownloadString(URL);


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


                            string CountryName = CountrySubdivision.CountryName;
                            string iso_3166_alpha2 = CountrySubdivision.CountryCode;


                            string AdminName1 = CountrySubdivision.AdminName1;
                            string AdminCode1 = CountrySubdivision.AdminCode1;


                            string AdminName2 = CountrySubdivision.AdminName2;
                            string AdminCode2 = CountrySubdivision.AdminCode2;


                            string AdminName3 = CountrySubdivision.AdminName3;
                            string AdminCode3 = CountrySubdivision.AdminCode3;




                            string AdminName4 = CountrySubdivision.AdminName4;
                            string AdminCode4 = CountrySubdivision.AdminCode4;
                            string AdminName5 = CountrySubdivision.AdminName5;
                            string AdminCode5 = CountrySubdivision.AdminCode5;
                            (string fcode, string CurrentCode, string CurrentName) = FindAdminCode(CountrySubdivision);
                            string subdivision_code = iso_3166_alpha2 + "-" + CountrySubdivision.Codes[CountrySubdivision.Codes.Count - 1].code;


                            Console.WriteLine(CountrySubdivision.CountryName);


                            Console.WriteLine("CountryName: {0}", CountryName);
                            Console.WriteLine("iso_3166_alpha2: {0}", iso_3166_alpha2);


                            Console.WriteLine("AdminName1: {0}", AdminName1);
                            Console.WriteLine("AdminCode1: {0}", AdminCode1);


                            Console.WriteLine("AdminName2: {0}", AdminName2);
                            Console.WriteLine("AdminCode2: {0}", AdminCode2);
                            Console.WriteLine("AdminName3: {0}", AdminName3);
                            Console.WriteLine("AdminCode3: {0}", AdminCode3);
                            Console.WriteLine("AdminName4: {0}", AdminName4);
                            Console.WriteLine("AdminCode4: {0}", AdminCode4);
                            Console.WriteLine("AdminName5: {0}", AdminName5);
                            Console.WriteLine("AdminCode5: {0}", AdminCode5);


                            Console.WriteLine("fCode: {0}", fcode);




                            Console.WriteLine("subdivision_code: {0}", subdivision_code);
                           // Update existing rows
                            PointOnSurface.subdivision_code = subdivision_code;


                            // Not able to do this here so created a method
                            InsertSubdivision_code(CountrySubdivision, subdivision_code);




                            // ToDo Update the database with new entries


                        }
                        contex.SaveChanges();


                    }
                    Console.ReadLine();
                } // End var contex
                Console.ReadLine();


            }
            catch (Exception e)
            {


                Console.WriteLine("Error: " + e.Message);
            }


        }


        private static void InsertSubdivision_code(Rootobject countrySubdivision, string subdivision_code)
        {
            var cs = ConfigurationManager.ConnectionStrings["DB_Con"].ToString();


            var DB_con = new NpgsqlConnection(cs);




            string Subdivision_Code;


            foreach (var item in countrySubdivision.Codes)
            {
                if (item.level == "1")
                {
                    Subdivision_Code = countrySubdivision.CountryCode + "-" + item.code;
                }
                else
                {
                    Subdivision_Code = subdivision_code;
                }
                bool SDC_exists = false;
                DB_con.Open();
                using (var scmd = new NpgsqlCommand("select id from admin_area_subdivision_code where subdivision_code = @p_subdivision_code;", DB_con))
                {
                    scmd.CommandType = System.Data.CommandType.Text;
                    scmd.Parameters.AddWithValue("@p_subdivision_code", Subdivision_Code);


                    NpgsqlDataReader rdr = scmd.ExecuteReader();
                    if (rdr.HasRows)
                    {
                        SDC_exists = true;
                    }
                    scmd.Dispose();
                }
                DB_con.Close();
                if (SDC_exists == false)
                {
                    DB_con.Open();
                    using (var cmd = new NpgsqlCommand("insert_subdivision_code", DB_con))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@p_code", item.code);
                        cmd.Parameters.AddWithValue("@p_level", item.level);
                        cmd.Parameters.AddWithValue("p_type", item.type);
                        cmd.Parameters.AddWithValue("@p_subdivision_code", Subdivision_Code);


                        var LastID = cmd.ExecuteScalar();
                        Console.WriteLine("Insert with LastID {0}", LastID);


                    }
                    DB_con.Close();


                }


            }
        }


        private static (string, string, string) FindAdminCode(Rootobject countrySubdivision)
        {
            var LastADM = countrySubdivision.GetType().GetProperties().OrderByDescending(s => s.Name).Where(c => c.Name.StartsWith("AdminCode") && c.GetValue(countrySubdivision) != null).First().Name;
            string num = LastADM.Last().ToString();
            string LastCode = countrySubdivision.GetType().GetProperties().Where(c => c.Name.Equals("AdminCode" + num)).FirstOrDefault().ToString();
            string LastName = countrySubdivision.GetType().GetProperties().Where(c => c.Name.Equals("AdminName" + num)).FirstOrDefault().ToString();


            return ("ADM" + num, LastCode, LastName);
        }
    }
}

Select allOpen in new window

CountrySubdivision.cs

using System.Collections.Generic;

namespace GetGeoNamesData
{
    class CountrySubdivision
    {
        public class Rootobject
        {
            public string AdminCode2 { get; set; }
            public List<Code> Codes { get; set; }
            public string AdminCode3 { get; set; }
            public string AdminName4 { get; set; }
            public string AdminName3 { get; set; }
            public string AdminCode1 { get; set; }
            public string AdminName2 { get; set; }
            public int Distance { get; set; }
            public string CountryCode { get; set; }
            public string CountryName { get; set; }
            public string AdminName1 { get; set; }
            public string AdminCode4 { get; set; }
            public string AdminName5 { get; set; }
            public string AdminCode5 { get; set; }
        }

        public class Code
        {
#pragma warning disable IDE1006 // Naming Styles
            public string code { get; set; }
#pragma warning restore IDE1006 // Naming Styles
#pragma warning disable IDE1006 // Naming Styles
            public string level { get; set; }
#pragma warning restore IDE1006 // Naming Styles
#pragma warning disable IDE1006 // Naming Styles
            public string type { get; set; }
#pragma warning restore IDE1006 // Naming Styles
        }
    }
}

Select allOpen in new window

Nuget Packages 

Changing  contex.SaveChanges(); to contex.SaveChangesAsync(); stops crashes but fails to execute the query / database isn't updated

Don't use "using" to set up the transaction:

using (var contex = new gazetteerEntities())

Your error message is telling you that the transaction has -already- been completed so you cannot use/save it again. I've heard of the "using" statement causing this on other database types, so I'm guessing it's the same cause here.

OK that's how I've  done it with an MS SQL server
Not sure how else to write it but will investigate 

Just don't wrap it in a using statement. Just do:
var contex = new gazetteerEntities();

... And take out the { and } for that using block.

Nothing else should change.

It's going to be next week now before I can try it.
This might be a bit unorthodox do you know anything about connecting and using the PostGIS spatial capabilities via EF framework?
This question seems  to have been abandoned

 

Sorry, I don't have any experience with that.

Hi
Apologies for the delay in responding

In order to test I've created a new project with the same nuget packages but connecting to a test database

I'm getting the same error with

        static void Main(string[] args)
        {
            var contex = new testEntities();
            var Countries = contex.geonames_country.Where(c => c.country_code == "GBR");
            foreach (var Country in Countries)
            {
                Console.WriteLine(Country.country_name);
            }

            var Frog = new geonames_country
            {
                country_code = "FRA",
                country_name = "France"
            };
            contex.geonames_country.Add(Frog);
            contex.SaveChanges();
        }

Select allOpen in new window

what's the code for testEntities 

PG_ConectTest.7z

Not totally sure what your asking for so I uploaded the project  Note: II had to remove the *.exe   files first

Here's the table

CREATE TABLE public.geonames_country (
     id       SERIAL PRIMARY KEY,
    country_code character varying(4) NOT NULL,
    country_name character varying NOT NULL
);



INSERT INTO public.geonames_country VALUES ('GBR', 'United Kingdom');
INSERT INTO public.geonames_country VALUES ('FRA', 'France');

Select allOpen in new window

@gr8gonzo 
Was the info in my last post what you needed?

I've kinda got around the problem by going old school and writing and executing SQL