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.