Cast problem in Postgres 9.0

Cast problem in Postgres 9.0

Postby ACARVALHO » Wed 19 Mar 2014 12:22

Hello,

I'm using Postgres 9.0 and dotConnect 7.2.103. When executing a query with Entity Framework is returned the following error:

failed to find conversion function from unknown to text

Description: An unhandled exception occurred During the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Devart.Data.PostgreSql.PgSqlException: failed to find conversion function from unknown to text

Stack Trace:


[PgSqlException (0x80004005): failed to find conversion function from unknown to text]
Devart.Data.PostgreSql.ae.f(ac A_0) +179
Devart.Data.PostgreSql.ac.n() +216
Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords) +1094
Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery) +838
Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior) +43
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
Devart.Data.PostgreSql.Entity.r.a(CommandBehavior A_0) +356
Devart.Common.Entity.ab.b(CommandBehavior A_0) +51
Devart.Data.PostgreSql.Entity.r.b(CommandBehavior A_0) +47
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
System.Data.Entity.Infrastructure.Interception.<>c__DisplayClassb.<Reader>b__8() +69
System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch(Func`1 operation, TInterceptionContext interceptionContext, Action`1 executing, Action`1 executed) +93
System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext) +320
System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior) +240
System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10
System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) +97




What can I do to solve the problem?
ACARVALHO
 
Posts: 6
Joined: Tue 25 Feb 2014 20:53

Re: Cast problem in Postgres 9.0

Postby MariiaI » Fri 21 Mar 2014 14:01

Please specify the following information:
- the version of Entity Framework you are working with;
- the LINQ query you are trying to execute;
- the generated SQL, e.g. via the dbMonitor tool:
http://www.devart.com/dotconnect/postgresql/docs/?dbmonitor.html
http://www.devart.com/dbmonitor/dbmon3.exe
- the DDL scripts for the corresponding database objects, etc.

If possible, please send us a small test project, with which this issue is reproducible.
Also, some useful information can be found here.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Cast problem in Postgres 9.0

Postby ACARVALHO » Tue 25 Mar 2014 16:27

Hi, here is the information you asked

Please specify the following information:
- the version of Entity Framework you are working with;
Entity Framework 6
- the LINQ query you are trying to execute;
the executed query was


Code: Select all
   var candidato = unitOfWork.CandidatoRepository.GetSingleOrDefault(
                                    filter: c => c.PessoaID == id,
                                    includeProperties: "Telefones, Enderecos");


Code: Select all
public virtual TEntity GetSingleOrDefault(
            Expression<Func<TEntity, bool>> filter = null,
            string includeProperties = "")
        {
            IQueryable<TEntity> query = dbSet;

            if (filter != null)
            {
                query = query.Where(filter);
            }

            foreach (var includeProperty in includeProperties.Split
                (new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
            {
                query = query.Include(includeProperty);
            }

            return query.SingleOrDefault();
        }






- the generated SQL, e.g. via the dbMonitor tool:
SELECT
"UnionAll1"."PessoaID" AS "C1",
"UnionAll1"."C2",
"UnionAll1"."PessoaID1" AS "C3",
"UnionAll1"."Nome" AS "C4",
"UnionAll1"."DataNascimento" AS "C5",
"UnionAll1"."Cpf" AS "C6",
"UnionAll1"."Sexo" AS "C7",
"UnionAll1"."Nacionalidade" AS "C8",
"UnionAll1"."EstadoCivil" AS "C9",
"UnionAll1"."Email" AS "C10",
"UnionAll1"."Senha" AS "C11",
"UnionAll1"."C1" AS "C12",
"UnionAll1"."TelefoneID" AS "C13",
"UnionAll1"."TipoTelefone" AS "C14",
"UnionAll1"."Numero" AS "C15",
"UnionAll1"."PessoaID2" AS "C16",
"UnionAll1"."C3" AS "C17",
"UnionAll1"."C4" AS "C18",
"UnionAll1"."C5" AS "C19",
"UnionAll1"."C6" AS "C20",
"UnionAll1"."C7" AS "C21",
"UnionAll1"."C8" AS "C22",
"UnionAll1"."C9" AS "C23",
"UnionAll1"."C10" AS "C24",
"UnionAll1"."C11" AS "C25"
FROM (SELECT
CASE WHEN "Extent2"."TelefoneID" IS NULL THEN CAST(NULL AS int) ELSE 1 END AS "C1",
"Limit1"."PessoaID",
"Limit1"."C1" AS "C2",
"Limit1"."PessoaID" AS "PessoaID1",
"Limit1"."Nome",
"Limit1"."DataNascimento",
"Limit1"."Cpf",
"Limit1"."Sexo",
"Limit1"."Nacionalidade",
"Limit1"."EstadoCivil",
"Limit1"."Email",
"Limit1"."Senha",
"Extent2"."TelefoneID",
"Extent2"."TipoTelefone",
"Extent2"."Numero",
"Extent2"."PessoaID" AS "PessoaID2",
CAST(NULL AS int) AS "C3",
CAST(NULL AS varchar) AS "C4",
CAST(NULL AS int) AS "C5",
CAST(NULL AS varchar) AS "C6",
CAST(NULL AS varchar) AS "C7",
CAST(NULL AS varchar) AS "C8",
CAST(NULL AS int) AS "C9",
CAST(NULL AS varchar) AS "C10",
CAST(NULL AS int) AS "C11"
FROM (SELECT
"Extent1"."PessoaID",
"Extent1"."Nome",
"Extent1"."DataNascimento",
"Extent1"."Cpf",
"Extent1"."Sexo",
"Extent1"."Nacionalidade",
"Extent1"."EstadoCivil",
"Extent1"."Email",
"Extent1"."Senha",
'0X0X0X' AS "C1"
FROM "CURRICULO_pessoa" AS "Extent1"
WHERE ("Extent1"."Discriminator" = 'Candidato') AND (((( CAST("Extent1"."PessoaID" AS bigint)) = :p__linq__0) AND ( NOT (( CAST("Extent1"."PessoaID" AS bigint) IS NULL) OR ( CAST(:p__linq__0 AS bigint) IS NULL)))) OR (( CAST("Extent1"."PessoaID" AS bigint) IS NULL) AND ( CAST(:p__linq__0 AS bigint) IS NULL)))
LIMIT 2 ) AS "Limit1"
LEFT OUTER JOIN "CURRICULO_telefone" AS "Extent2" ON "Limit1"."PessoaID" = "Extent2"."PessoaID"
UNION ALL
SELECT
2 AS "C1",
"Limit2"."PessoaID",
"Limit2"."C1" AS "C2",
"Limit2"."PessoaID" AS "PessoaID1",
"Limit2"."Nome",
"Limit2"."DataNascimento",
"Limit2"."Cpf",
"Limit2"."Sexo",
"Limit2"."Nacionalidade",
"Limit2"."EstadoCivil",
"Limit2"."Email",
"Limit2"."Senha",
CAST(NULL AS int) AS "C3",
CAST(NULL AS int) AS "C4",
CAST(NULL AS varchar) AS "C5",
CAST(NULL AS int) AS "C6",
"Extent4"."EnderecoID",
"Extent4"."Logradouro",
"Extent4"."Numero",
"Extent4"."Bairro",
"Extent4"."Complemento",
"Extent4"."Cep",
"Extent4"."Estado",
"Extent4"."Cidade",
"Extent4"."PessoaID" AS "PessoaID2"
FROM (SELECT
"Extent3"."PessoaID",
"Extent3"."Nome",
"Extent3"."DataNascimento",
"Extent3"."Cpf",
"Extent3"."Sexo",
"Extent3"."Nacionalidade",
"Extent3"."EstadoCivil",
"Extent3"."Email",
"Extent3"."Senha",
'0X0X0X' AS "C1"
FROM "CURRICULO_pessoa" AS "Extent3"
WHERE ("Extent3"."Discriminator" = 'Candidato') AND (((( CAST("Extent3"."PessoaID" AS bigint)) = :p__linq__0) AND ( NOT (( CAST("Extent3"."PessoaID" AS bigint) IS NULL) OR ( CAST(:p__linq__0 AS bigint) IS NULL)))) OR (( CAST("Extent3"."PessoaID" AS bigint) IS NULL) AND ( CAST(:p__linq__0 AS bigint) IS NULL)))
LIMIT 2 ) AS "Limit2"
INNER JOIN "CURRICULO_endereco" AS "Extent4" ON "Limit2"."PessoaID" = "Extent4"."PessoaID") AS "UnionAll1"
ORDER BY "UnionAll1"."PessoaID1" ASC, "UnionAll1"."C1" ASC
----------------------------------------


----------------------------------------
-- p__linq__0: '1' (Type = Int64)

----------------------------------------
-- Executing at 25/03/2014 11:04:42 -03:00

----------------------------------------
-- Failed in 232 ms with error: failed to find conversion function from unknown to text

----------------------------------------


- the DDL scripts for the corresponding database objects, etc.

I have a table that is related to two other ones, the first one stores the candidates and the two other ones are storing the candidates phone number and adress.
A candidate can have multiple phone numbers and also multiple adresses, configuring a MxN relationship.

With theese information I believe that you guys can simulate the problem


If possible, please send us a small test project, with which this issue is reproducible.
Also, some useful information can be found here.

My problem is exactly the one you linked here, but we are looking for the solution to the problem using LINQ, the one with sql does not solve my problem.

I think the solution is related to maybe making changes on the driver.
ACARVALHO
 
Posts: 6
Joined: Tue 25 Feb 2014 20:53

Re: Cast problem in Postgres 9.0

Postby MariiaI » Wed 26 Mar 2014 13:31

Thank you for the additional information. We couldn't reproduce this issue in our environment.
We are sending you a sample project to the e-mail address you have provided in your forum profile. Please check that the letter is not blocked by your mail filter.
Please modify our sample project so that the issue could be reproduced and send it back to us, or send us your sample project.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Cast problem in Postgres 9.0

Postby ACARVALHO » Wed 26 Mar 2014 16:26

returned to your email
ACARVALHO
 
Posts: 6
Joined: Tue 25 Feb 2014 20:53

Re: Cast problem in Postgres 9.0

Postby MariiaI » Thu 27 Mar 2014 13:11

Thank you for the sample project. We have reproduced this issue. We will investigate it and inform you about the results as soon as any are available.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Cast problem in Postgres 9.0

Postby MariiaI » Wed 09 Apr 2014 11:16

Exception Details: Devart.Data.PostgreSql.PgSqlException: failed to find conversion function from unknown to text

We have fixed this issue. We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Cast problem in Postgres 9.0

Postby ACARVALHO » Wed 09 Apr 2014 12:08

Thanks...
ACARVALHO
 
Posts: 6
Joined: Tue 25 Feb 2014 20:53

Re: Cast problem in Postgres 9.0

Postby MariiaI » Thu 10 Apr 2014 12:52

New build of dotConnect for PostgreSQL 7.3.135 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/postgresql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=3&t=29346.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for PostgreSQL