Problem saving accent with Entity Framework and Postgresql

Question

I need to migrate a system with SQLSERVER database to a Postgresql 9.3. I’ve never worked with Postgre before so I’m using Entity Framework 6 with model-first to make the process easier. The migration application is being developed in WPF. My first two tables imported quietly, but in one of the records of the third table it is necessary to be inserted "NAY".

IMPORTANT: I don’t have the option to insert without accent because the system that uses the Postgre base is a legacy system that will only understand with accent. In a production base of this system is with accent and with the same configuration in the image below.

 // produtor rural
 if (objPessoa.pes_cliProdutorRural == true)
       objNovoCliente.produtorrural = "SIM";
 else
       objNovoCliente.produtorrural = "NÃO";

  // adicionar no contexto
  this.objCtxMSP2.tbcadclientes.Add(objNovoCliente);
  this.objCtxMSP2.SaveChanges();

But gives the following error in Savechanges:

Innerexception = {"ERROR: 22001: very long value for type Character(3)"}

Searching on the Internet saw that it can be something related to "collation" and/or "encoding".

This is the current database configuration:

When trying to create the database with encoding LATIN1 as seen in another forum gives the following error

Any idea how to fix this?

Answer 1

It is likely that your environment (IIS) is in UTF8 and you are trying to use latin1 (which is compatible only with windows-1252 and iso-8859-1).

In case if you are going to use Latin1 you can configure the web config. thus:

<system.web>
     ...
     <globalization
         requestEncoding="iso-8859-1"
         responseEncoding="iso-8859-1"
         fileEncoding="iso-8859-1"
         culture="pt-BR"
         uiCulture="pt-BR" />
     ...
</system.web>

If you decide to migrate the bank to utf8 (or already using it) then use it like this:

<system.web>
     ...
     <globalization
         requestEncoding="utf-8"
         responseEncoding="utf-8"
         fileEncoding="utf-8"
         culture="pt-BR"
         uiCulture="pt-BR" />
     ...
</system.web>

The ... are just to say it is anywhere in XML, do not copy them.

Answer 2

I had another problem recently with postgresql and npgsql, but this time I posted in stackoverflow in English, getting contact with the responsible staff, and will release an update that should also solve this problem that is practically the same. The fix will be in version 3.1.8 of npgsql.

https://stackoverflow.com/questions/39145146/cannot-select-some-rows-with-npgsql-on-a-portuguese-database/39153317#39153317

Answer 3

If possible configure your Entity in the WebConfig or your OnModelCreating set the creation of the bank in a form that meets this instruction:

CREATE DATABASE nome_banco WITH OWNER = usuario_banco ENCODING = 'LATIN1' TABLESPACE = pg_default LC_COLLATE = 'C' LC_CTYPE = 'C' CONNECTION LIMIT = -1 TEMPLATE template0;