Lambda queries with string concatenation throws Exception with message "42P18: could not determine data type of parameter $1"

[lboross]

I have run into an issue similar to #60, however I get the above error when concatenating strings within my query.

Example

string a = "AAA";
string b = "BBB";

var selection = Entities.SOMETHING.Where( a => a.NAME == a + b );

This generates this SQL query as seen in the Exception

SELECT "Extent1"."ID", "Extent1"."NAME" FROM "SCHEMA"."SOMETHING" AS "Extent1" WHERE "Extent1"."NAME" = CASE WHEN ( CASE WHEN ($1 IS NULL) THEN (E'') ELSE ($1) END || CASE WHEN ($2 IS NULL) THEN (E'') ELSE ($2) END IS NULL) THEN (E'') ELSE ( CASE WHEN ($1 IS NULL) THEN (E'') ELSE ($1) END || CASE WHEN ($2 IS NULL) THEN (E'') ELSE ($2) END ) END

You can see that Npgsql is concatenating the strings within the SQL query, and this throws the 42P08: could not determine data type of parameter $1 Error.

If I put the result of the concatenation into a variable, there seem to be no issue.

string a = "AAA";
string b = "BBB";
string c = a + b;

var selection = Entities.SOMETHING.Where( a => a.NAME == c );

OK - Empty Result Set as expected

I also tried the following methods, manually casting the result, but they all seem to fail, unless I assign a value to a string variable first.

var dims1 = Entities.SOMETHING.Where(d => d.NAME == (a + b).ToString());

SELECT "Extent1"."ID", "Extent1"."NAME" FROM "SCHEMA"."SOMETHING" AS "Extent1" 
WHERE "Extent1"."NAME" =  CASE  WHEN ( CASE  WHEN ($1 IS NULL) THEN (E'') ELSE ($1) END  ||  CASE  WHEN ($2 IS NULL) THEN (E'') ELSE ($2) END  IS NULL) THEN (E'') ELSE ( CASE  WHEN ($1 IS NULL) THEN (E'') ELSE ($1) END  ||  CASE  WHEN ($2 IS NULL) THEN (E'') ELSE ($2) END ) END 

could not determine data type of parameter $1

var dims2 = Entities.SOMETHING.Where(d => d.NAME == (string)(a + b));
 
SELECT "Extent1"."ID", "Extent1"."NAME" FROM "SCHEMA"."SOMETHING" AS "Extent1" 
WHERE "Extent1"."NAME" =  CASE  WHEN ($1 IS NULL) THEN (E'') ELSE ($1) END  ||  CASE  WHEN ($2 IS NULL) THEN (E'') ELSE ($2) END 

could not determine data type of parameter $1

var dims4 = Entities.SOMETHING.Where(d => d.NAME == string.Format("{0}_{1}", a, b));

LINQ to Entities does not recognize the method 'System.String Format(System.String, System.Object, System.Object)' method, and this method cannot be translated into a store expression.

Any suggestions to work around this without re-writing our LINQ queries?

[CelsoLivero]

I have the same problem

I created an expression, and included it in the query, without success
Expression<Func<Pessoa, bool>> expression = x => ( !string.IsNullOrEmpty(Nome) ? x.Nome.Contains(Nome) : x.Nome != null ) && ( !string.IsNullOrEmpty(NroCpf) ? x.NroCpf.Contains(NroCpf) : x.NroCpf != null ) && ( !string.IsNullOrEmpty(Telefone) ? x.Telefone.Contains(Telefone) : x.Id > 0 ) && ( _idade > 0 ? x.DataNascimento.Year == DateTime.Now.Year - Idade : x.Id > 0 ) && ( _escolaridadeId.HasValue ? x.Escolaridade == EscolaridadeId : x.Id > 0 ) && ( _statusId.HasValue ? x.Status == StatusId : x.Id > 0 );

My query:
using (var db = new SoftwareDbContext()) var dados = db.Pessoas.Where(expression).Include(x => x.Resultados).OrderBy(x => x.Nome).ToList();

No success, unfortunately

[CelsoLivero]

Statement (part of)
CASE WHEN (NOT ($1 IS NULL OR CAST (char_length($1) AS int4) = 0)) THEN ( CASE WHEN ("Extent1"."nome" LIKE $2) THEN (TRUE) WHEN ("Extent1"."nome" NOT LIKE $2) THEN (FALSE) END ) ELSE (TRUE) END = TRUE

[rwasef1830]

Manually cast your parameter (https://github.com/npgsql/EntityFramework6.Npgsql/blob/master/src/EntityFramework6.Npgsql/NpgsqlTypeFunctions.cs) to text type. This will emit explicit cast which will succeed and will not affect query performance.

This might be related to changes made in the EF6 driver to support unknown types sent as text. A non-breaking fix might be possible but until I investigate it, this workaround should do for now (unfortunately though it makes your code Npgsql-specific).

[lucalooz]

I have this issue too, i have noticed that npgsql binds string parameter as Object while the mssql provider uses String. Is there a reason?

[EltonRst]

@rwasef1830
I do not understand, how can I use the function to cast a string?

Manually cast your parameter (https://github.com/npgsql/EntityFramework6.Npgsql/blob/master/src/EntityFramework6.Npgsql/NpgsqlTypeFunctions.cs) to text type. This will emit explicit cast which will succeed and will not affect query performance.

This might be related to changes made in the EF6 driver to support unknown types sent as text. A non-breaking fix might be possible but until I investigate it, this workaround should do for now (unfortunately though it makes your code Npgsql-specific).

[rwasef1830]

Use NpgsqlTypeFunctions.Cast(yourVar, "text") in your LINQ @EltonRst

[EltonRst]

@rwasef1830
How do I develop the Cast function of NpgsqlTypes Functions?

[CelsoLivero]

In my expression 'Name' is a property of type 'string', I changed to use a variable of type 'string' and I solved my problem.

var name = Nome;
Expression<Func<Pessoa, bool>> expression = 
	x => ( !string.IsNullOrEmpty(name ) ? x.Nome.Contains(name) : x.Nome != null ) .....

I can not use a direct NPGSQL function because my software was designed to use other types of database (PostgreSql, SqlServer, Oracle) by changing only one value in the .config file

[dbeuchler]

@rwasef1830 Are there any plans to fix this problem? My workaround is to replace a null string with string.empty instead of using string.IsNullOrEmpty. We moved from devart DotConnect PostgreSQL to npgsql (CodeFirst) and have a lot of strings in our queries.

var searchStr = search ?? string.Empty;
var query = from history in set 
    where searchStr == string.Empty || [...] 
    select history;

[rwasef1830]

@dbeuchler "Fixing" this is extremely tricky as sending strings as strings will break using full-text search and Postgis and all other types that are sent as strings that EF6 doesn't explicitly support. If I am understanding you correctly, the issue is that even comparisons to literal parameters are giving type errors from PostgreSQL ?

Can you provide the smallest possible queries, one which fails and one with your workaround that succeeds ? This will help me find a test case to use in attempting to workaround this issue.

[dbeuchler]

There are already a few examples in this issue and in #60 but I can summarize:

The first sample uses string.IsNullOrEmpty within a query. A workaround is to source the database independet code out or set to empty if null:

var str = (string) null; // e.g. from method parameter

//Raises 42P18: could not determine data type of parameter $1:
var carFilter = dbSet.Where(c => !string.IsNullOrEmpty(str) && c.Name.Contains(str)).ToList();

//Workaround:
var carFilter = dbSet;
if (!string.IsNullOrEmpty(str)) 
{
    carFilter = carFilter.Where(c => c.Name.Contains(str));
}

//Or: 
str = str ?? string.Empty;
var carFilter = dbSet.Where(c => str != string.Empty && c.Name.Contains(str)).ToList();

The second sample uses string literals within a Linq selection. A workaround for this problem is to add another iteration after filtering.

var testStr = "Hello";

//Raises 42P18: could not determine data type of parameter $1:
var carFilter = dbSet.Select(c => new FilterDto
{
    Name = c.Name,
    Foo = testStr
}).ToList();

//Workaround
var carFilter = dbSet.Select(c => new FilterDto
{
    Name = c.Name,
}).ToList();

var cars = carFilter.ForEach(car => car.Foo = testStr);

The third sample uses concatenations within a Linq query. A workaround is to concat the string before executing the query:

var a = "C";
var b = "ar";

//Raises 42P18: could not determine data type of parameter $1:
var carFilter = carRepo.Linq.Where(c => c.Name.Contains(a + b));

//Workaround
var search = a + b;
var carFilter = dbSet.Where(c => c.Name.Contains(search));

I hope it helps you to find a workaround for this issue.