Help ! Error: 42P08: could not determine data type of parameter $1

[EltonRst]

I need help, please !

// DbContext
DadosContexto db = new DadosContexto();

       //Model
          Usuario model = new Usuario() { usu_cod = "LUAN"};

            Usuario user = db.Usuarios.FirstOrDefault(u =>
                (!string.IsNullOrEmpty(model.usu_nom) && model.usu_nom.Equals(u.usu_nom) && model.usu_cod.Equals(u.usu_cod))
                ||
                (!string.IsNullOrEmpty(model.usu_cod) && model.usu_cod.Equals(u.usu_cod))
            );

// Usuario class
Usuario.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Databases.Models
{

    [Table("usucad", Schema ="frota")]
    public partial class Usuario
    {
        public Usuario()
        {
        }

        [Key]
        [StringLength(6)]
        public string usu_cod { get; set; }
        public string usu_nom { get; set; }
        public string usu_super { get; set; }
        public Nullable<short> usu_emp { get; set; }
        public string usu_acedat { get; set; }
        public string usu_aceemp { get; set; }
        public string usu_aceopc { get; set; }
        public string usu_nivel { get; set; }
        public string usu_tiplib { get; set; }
        public string usu_ageati { get; set; }
        public Nullable<int> usu_ultcon { get; set; }
        public string usu_ide { get; set; }
        public string usu_loc { get; set; }
        public string usu_stqsai { get; set; }
        public string usu_stqent { get; set; }
        public string usu_stqaju { get; set; }
        public string usu_stqtra { get; set; }
        public string usuemanom { get; set; }
        public string usuemaema { get; set; }
        public string usuemausu { get; set; }
        public string usuemapas { get; set; }
        public string usu_senepi { get; set; }
        public string usu_sensol { get; set; }
        public Nullable<int> usu_dep { get; set; }
        public Nullable<System.DateTime> usu_dua { get; set; }
        public string usu_grasel { get; set; }
        public string usu_colab { get; set; }
        public string usu_funcod { get; set; }

    }
}

[roji]

Can you please turn on PostgreSQL logging and post the query that generated the error?

[EltonRst]

@roji ,@franciscojunior, @Emill, @kenjiuno, @piksel

This is query being sent to postgresql:

SELECT
    "Extent1"."usu_cod",
    "Extent1"."usu_nom",
    "Extent1"."usu_super",
    "Extent1"."usu_emp",
    "Extent1"."usu_acedat",
    "Extent1"."usu_aceemp",
    "Extent1"."usu_aceopc",
    "Extent1"."usu_nivel",
    "Extent1"."usu_tiplib",
    "Extent1"."usu_ageati",
    "Extent1"."usu_ultcon",
    "Extent1"."usu_ide",
    "Extent1"."usu_loc",
    "Extent1"."usu_stqsai",
    "Extent1"."usu_stqent",
    "Extent1"."usu_stqaju",
    "Extent1"."usu_stqtra",
    "Extent1"."usuemanom",
    "Extent1"."usuemaema",
    "Extent1"."usuemausu",
    "Extent1"."usuemapas",
    "Extent1"."usu_senepi",
    "Extent1"."usu_sensol",
    "Extent1"."usu_dep",
    "Extent1"."usu_dua",
    "Extent1"."usu_grasel",
    "Extent1"."usu_colab",
    "Extent1"."usu_funcod"
FROM
    "frota"."usucad" AS "Extent1"
WHERE
    NOT (
        $1 IS NULL
        OR CAST (CHAR_LENGTH($1) AS int4) = 0
    )
AND (
    $2 = "Extent1"."usu_nom"
    OR $2 IS NULL
    AND "Extent1"."usu_nom" IS NULL
)
AND $3 = "Extent1"."usu_cod"
OR NOT (
    $4 IS NULL
    OR CAST (CHAR_LENGTH($4) AS int4) = 0
)
AND $5 = "Extent1"."usu_cod"
LIMIT 1

Here Linq query:

Usuario model = new Usuario() { usu_cod = "LUAN"};
Usuario user = db.Usuarios.FirstOrDefault(u =>
    (!string.IsNullOrEmpty(model.usu_nom) && model.usu_nom.Equals(u.usu_nom) && model.usu_cod.Equals(u.usu_cod))
    ||
    (!string.IsNullOrEmpty(model.usu_cod) && model.usu_cod.Equals(u.usu_cod))
);

The linq query seems to be not being transformed or parameterized correctly when generating sql.

Now using IQueryable:

Usuario model = new Usuario() { usu_cod = "LUAN"};
IQueryable<Usuario> users = db.Usuarios.Where(u =>
    (!string.IsNullOrEmpty(model.usu_nom) && model.usu_nom.Equals(u.usu_nom) && model.usu_cod.Equals(u.usu_cod))
    ||
    (!string.IsNullOrEmpty(model.usu_cod) && model.usu_cod.Equals(u.usu_cod))
);

Sql result from IQueryable:

SELECT
    "Extent1"."usu_cod",
    "Extent1"."usu_nom",
    "Extent1"."usu_super",
    "Extent1"."usu_emp",
    "Extent1"."usu_acedat",
    "Extent1"."usu_aceemp",
    "Extent1"."usu_aceopc",
    "Extent1"."usu_nivel",
    "Extent1"."usu_tiplib",
    "Extent1"."usu_ageati",
    "Extent1"."usu_ultcon",
    "Extent1"."usu_ide",
    "Extent1"."usu_loc",
    "Extent1"."usu_stqsai",
    "Extent1"."usu_stqent",
    "Extent1"."usu_stqaju",
    "Extent1"."usu_stqtra",
    "Extent1"."usuemanom",
    "Extent1"."usuemaema",
    "Extent1"."usuemausu",
    "Extent1"."usuemapas",
    "Extent1"."usu_senepi",
    "Extent1"."usu_sensol",
    "Extent1"."usu_dep",
    "Extent1"."usu_dua",
    "Extent1"."usu_grasel",
    "Extent1"."usu_colab",
    "Extent1"."usu_funcod"
FROM
    "frota"."usucad" AS "Extent1"
WHERE
    NOT (
        @p__linq__0 IS NULL
        OR CAST (
            CHAR_LENGTH (@p__linq__0) AS int4
        ) = 0
    )
AND (
    @p__linq__1 = "Extent1"."usu_nom"
    OR @p__linq__1 IS NULL
    AND "Extent1"."usu_nom" IS NULL
)
AND @p__linq__2 = "Extent1"."usu_cod"
OR NOT (
    @p__linq__3 IS NULL
    OR CAST (
        CHAR_LENGTH (@p__linq__3) AS int4
    ) = 0
)
AND @p__linq__4 = "Extent1"."usu_cod"

I believe that the query is not being parameterized the linq query.

[blacksnake-rus]

Its global problem #62

[EltonRst]

@blacksnake-rus thanks, but it's not about parameter conversion since all operations with string are not transformed into SQL.

Example:

List<User> users = context.Users.Where(user => user.Name.Equals("EltonRst")).ToList();

[EltonRst]

@blacksnake-rus

Here a correct SQL Statement

SELECT
    "Extent1"."usu_cod",
    "Extent1"."usu_nom",
    "Extent1"."usu_super",
    "Extent1"."usu_emp",
    "Extent1"."usu_acedat",
    "Extent1"."usu_aceemp",
    "Extent1"."usu_aceopc",
    "Extent1"."usu_nivel",
    "Extent1"."usu_tiplib",
    "Extent1"."usu_ageati",
    "Extent1"."usu_ultcon",
    "Extent1"."usu_ide",
    "Extent1"."usu_loc",
    "Extent1"."usu_stqsai",
    "Extent1"."usu_stqent",
    "Extent1"."usu_stqaju",
    "Extent1"."usu_stqtra",
    "Extent1"."usuemanom",
    "Extent1"."usuemaema",
    "Extent1"."usuemausu",
    "Extent1"."usuemapas",
    "Extent1"."usu_senepi",
    "Extent1"."usu_sensol",
    "Extent1"."usu_dep",
    "Extent1"."usu_dua",
    "Extent1"."usu_grasel",
    "Extent1"."usu_colab",
    "Extent1"."usu_funcod"
FROM
    "frota"."usucad" AS "Extent1"
WHERE
    NOT (
        'Luan' IS NULL
        OR CAST (
            CHAR_LENGTH ('Luan') AS int4
        ) = 0
    )
AND (
    'Luan' = "Extent1"."usu_nom"
    OR 'Luan' IS NULL
    AND "Extent1"."usu_nom" IS NULL
)
AND 'LUAN' = "Extent1"."usu_cod"
OR NOT (
    'LUAN' IS NULL
    OR CAST (
        CHAR_LENGTH ('LUAN') AS int4
    ) = 0
)
AND 'LUAN' = "Extent1"."usu_cod"

[blacksnake-rus]

@EltonRst Similar problem #84

I have problem if model.usu_nom = null in string.IsNullOrEmpty(model.usu_nom)

Its very big problem. I have to return on https://www.nuget.org/packages/Npgsql.EntityFramework/ with old Npgsql (= 2.2.7)

[EltonRst]

@blacksnake-rus Does this problem occur in the npgsql/EntityFramework6.Npgsql project, or the npgsql/npgsql project?

we need to find where the problem occurs and fix it.

[EltonRst]

@blacksnake-rus Can you help me with my App.config ? I need to include these packages and configure them where ?

Its very big problem. I have to return on https://www.nuget.org/packages/Npgsql.EntityFramework/ with old Npgsql (= 2.2.7)

[EltonRst]

@roji @franciscojunior @Emill @kenjiuno @piksel @blacksnake-rus

I see that the project is abandoned, where is the support when we need it? I do not understand anything about the project so I do not know where to start to solve this problem.

[blacksnake-rus]

Does this problem occur in the npgsql/EntityFramework6.Npgsql project, or the npgsql/npgsql project? we need to find where the problem occurs and fix it.

I think that not problem npgsql because https://github.com/npgsql/npgsql/issues/1780

I need to include these packages and configure them where ?

Yes. Install this package instead EntityFramework6.Npgsql

Can you help me with my App.config ?

Paste in App.cpnfig `

</system.data>`

`

</entityFramework>`

[EltonRst]

Tank's @blacksnake-rus

https://www.nuget.org/packages/Npgsql.EntityFramework/

<system.data> <DbProviderFactories> <add name="Npgsql Data Provider" invariant="Npgsql" description="Data Provider for PostgreSQL" type="Npgsql.NpgsqlFactory, Npgsql" /> </DbProviderFactories> </system.data>

<entityFramework> <providers> <provider invariantName="Npgsql" type="Npgsql.NpgsqlServices, Npgsql.EntityFramework" /> </providers> </entityFramework>

This worked, but I have to do all mapping manually using data annotations, is there any way to work with ADO.NET Entity Data Model in this version 2.2.7 of Npgsql.EntityFramework ? I want to generate a .edmx with a .cd class diagram to save time and not cause errors in navigation properties and object relationships.

When I try to create .edmx, I get an error message when I'm going to select the database tables.

Apparently the connection is ok, it must be something of the version of the .VSIX extension that conflicts with this version of Npgsql.EntityFramework.

[blacksnake-rus]

This worked, but I have to do all mapping manually using data annotations, is there any way to work with ADO.NET Entity Data Model in this version 2.2.7 of EntityFramework.Npgsql ?

I dont have .edmx. I use CodeFirst Model. All relation in Context as in this article https://msdn.microsoft.com/en-us/library/jj200620(v=vs.113).aspx

[EltonRst]

@blacksnake-rus

Even following these steps, the problem persists.

Next:

Next:

Next:

Next:

[EvilSakray]

@EltonRst did you manage to find a decent solution?

I'm getting the same error as you ( or as #60 / #62 that are over a year old now... ) I can't go back to npgsql 2.2.7 at the moment because the .edmx file won't work with VS2017 and I can't edit the service querying the postgre db to make an explicit cast.

[EltonRst]

@EvilSakray Dude, the only thing I got was going back to version 2.2.7, and doing all the mapping code manually using the data annotations.

[EvilSakray]

@EltonRst ah well that sucks, thanks for the feedback.

[mattsteinRRD]

We at RRD are also trying to upgrade to use with VS 2017 and we are running into this issue. Introducing a breaking change this way is not a good sign.

[glebasos]

Still present I guess, using .AsEnumerable() before .Where() kinda fixes it