Selecting string literals throws Exception with message "42P18: could not determine data type of parameter $1"

[fredimachado]

I made a unit test and it's available in my fork: https://github.com/Fredi/EntityFramework6.Npgsql/tree/select_string_literal

[Test]
public void TestProjectionWithStringLiteral()
{
    using (var context = new BloggingContext(ConnectionString))
    {
        context.Database.Log = Console.Out.WriteLine;

        context.Blogs.Add(new Blog { Name = "_" });
        context.SaveChanges();

        var name = "test";

        var results = context.Blogs.Select(b => new
        {
            Id = b.BlogId,
            Name = name
        }).ToList();

        Assert.AreEqual(1, results.Count);
    }
}

Details:

Test Name: TestProjectionWithStringLiteral
Test Outcome: Failed
Result Message:
System.Data.Entity.Core.EntityCommandExecutionException : An error occurred while executing the command definition. See the inner exception for details.
----> Npgsql.PostgresException : 42P18: could not determine data type of parameter $1
Result StandardOutput:
Opened connection to etab-system.etab.local:5432
Executing statement(s):
select count() from pg_catalog.pg_database where datname = 'npgsql_tests_ef6'
Connection closed
Executing statement(s):
SELECT "GroupBy1"."A1" AS "C1" FROM (SELECT CAST (count(1) AS int4) AS "A1" FROM "dbo"."__MigrationHistory" AS "Extent1" WHERE "Extent1"."ContextKey" = $1) AS "GroupBy1"
Parameters:
$1: EntityFramework6.Npgsql.Tests.BloggingContext
Connection closed
Executing statement(s):
SELECT "Project1"."C1", "Project1"."MigrationId", "Project1"."Model", "Project1"."ProductVersion" FROM (SELECT "Extent1"."MigrationId", "Extent1"."Model", "Extent1"."ProductVersion", 1 AS "C1" FROM "dbo"."__MigrationHistory" AS "Extent1" WHERE "Extent1"."ContextKey" = $1) AS "Project1" ORDER BY "Project1"."MigrationId" DESC LIMIT 1
Parameters:
$1: EntityFramework6.Npgsql.Tests.BloggingContext
Connection closed
Executing statement(s):
SELECT "Extent1"."BlogId", "Extent1"."Name", "Extent1"."IntComputedValue", "Extent1"."User_Id" FROM "dbo"."Blogs" AS "Extent1"
Connection closed
Executing statement(s):
SELECT "Extent1"."PostId", "Extent1"."Title", "Extent1"."Content", CAST ("Extent1"."Rating" AS int2) AS "C1", "Extent1"."CreationDate", "Extent1"."VarbitColumn", "Extent1"."BlogId" FROM "dbo"."Posts" AS "Extent1"
Connection closed
Opened connection to etab-system.etab.local:5432
Executing statement(s):
select count() from pg_catalog.pg_database where datname = 'npgsql_tests_ef6'
Connection closed
Opened connection at 12/12/2016 10:18:01 -02:00

SELECT "GroupBy1"."A1" AS "C1" FROM (SELECT CAST (count(1) AS int4) AS "A1" FROM "dbo"."__MigrationHistory" AS "Extent1" WHERE "Extent1"."ContextKey" = @p__linq__0) AS "GroupBy1"

-- p__linq__0: 'EntityFramework6.Npgsql.Tests.BloggingContext' (Type = Object)

-- Executing at 12/12/2016 10:18:01 -02:00

Executing statement(s):
SELECT "GroupBy1"."A1" AS "C1" FROM (SELECT CAST (count(1) AS int4) AS "A1" FROM "dbo"."__MigrationHistory" AS "Extent1" WHERE "Extent1"."ContextKey" = $1) AS "GroupBy1"
Parameters:
$1: EntityFramework6.Npgsql.Tests.BloggingContext
-- Completed in 1 ms with result: NpgsqlDataReader

Connection closed
Closed connection at 12/12/2016 10:18:01 -02:00

Opened connection at 12/12/2016 10:18:01 -02:00

SELECT "Project1"."C1", "Project1"."MigrationId", "Project1"."Model", "Project1"."ProductVersion" FROM (SELECT "Extent1"."MigrationId", "Extent1"."Model", "Extent1"."ProductVersion", 1 AS "C1" FROM "dbo"."__MigrationHistory" AS "Extent1" WHERE "Extent1"."ContextKey" = @p__linq__0) AS "Project1" ORDER BY "Project1"."MigrationId" DESC LIMIT 1

-- p__linq__0: 'EntityFramework6.Npgsql.Tests.BloggingContext' (Type = Object)

-- Executing at 12/12/2016 10:18:01 -02:00

Executing statement(s):
SELECT "Project1"."C1", "Project1"."MigrationId", "Project1"."Model", "Project1"."ProductVersion" FROM (SELECT "Extent1"."MigrationId", "Extent1"."Model", "Extent1"."ProductVersion", 1 AS "C1" FROM "dbo"."__MigrationHistory" AS "Extent1" WHERE "Extent1"."ContextKey" = $1) AS "Project1" ORDER BY "Project1"."MigrationId" DESC LIMIT 1
Parameters:
$1: EntityFramework6.Npgsql.Tests.BloggingContext
-- Completed in 1 ms with result: NpgsqlDataReader

Connection closed
Closed connection at 12/12/2016 10:18:01 -02:00

Opened connection at 12/12/2016 10:18:01 -02:00

Beginning transaction with isolation level Unspecified
Started transaction at 12/12/2016 10:18:01 -02:00

INSERT INTO "dbo"."Blogs"("Name","User_Id") VALUES (@p_0,NULL) RETURNING "BlogId","IntComputedValue"

-- p_0: '_' (Type = Object, IsNullable = false)

-- Executing at 12/12/2016 10:18:01 -02:00

Executing statement(s):
INSERT INTO "dbo"."Blogs"("Name","User_Id") VALUES ($1,NULL) RETURNING "BlogId","IntComputedValue"
Parameters:
$1: _
-- Completed in 2 ms with result: NpgsqlDataReader

Commit transaction
Committed transaction at 12/12/2016 10:18:01 -02:00

Connection closed
Closed connection at 12/12/2016 10:18:01 -02:00

Opened connection at 12/12/2016 10:18:01 -02:00

SELECT "Extent1"."BlogId", @p__linq__0 AS "C1" FROM "dbo"."Blogs" AS "Extent1"

-- p__linq__0: 'test' (Type = Object)

-- Executing at 12/12/2016 10:18:01 -02:00

Executing statement(s):
SELECT "Extent1"."BlogId", $1 AS "C1" FROM "dbo"."Blogs" AS "Extent1"
Parameters:
$1: test
-- Failed in 2 ms with error: 42P18: could not determine data type of parameter $1

[a-einstein]

I got the same error message "42P18: could not determine data type of parameter $1" and it might be related to string comparison as suggested here. My linq query contains this expression: "product.Name.Contains(searchString)".

I hope you can help. Currently this is blocking me, and I have not found any solution so far. I am glad to see that I am not the only one with the problem and a way of direct contact with the developers.

Is there any workaround?

This is my first attempt to convert my application to use Postgres instead of Sql Server with Entity Framework. I was glad to find this software, which apparently is necessary. But I have been stumbling with a number of problems to get this far, without restoring my application to working order again. I might have to enter some issues about those.

[Emill]

Hi. Could you post your query and the generated sql query?

[a-einstein]

How can I catch that SQL query?

[a-einstein]

By the way, it seems there are of couple of issues related to the same problem.

[a-einstein]

As proof of that indeed it is caused by string comparison I finally got it working by removing ALL string comparison in my query.

At least I now have a proof of concept.

I hope you will be able to fix the problem, or supply me with a workaround. Thanks.