Error 42P08: could not determine data type of parameter when using subqueries and string variable parameter

[dlevis85]

Steps to reproduce

I am executing a LINQ to Entities query with Npgsql 4.0.6, EntityFramework 6.2.0 and EntityFramework6.Npgsql 3.2.0.0

This query has a master type (in_flag) and a subquery for getting the inner collection type (Settings).
in_flag entity is 1 to N relation with in_flag_setting entity in the database.

   var curFlagPreQuery =
                    (
                     from mFlag in db.Context.in_flag
                     join mFlagType in db.Context.in_flag_type
                     on mFlag.type equals mFlagType.id
                     where
                      (mFlag.id == flagInfoItem.FlagId)
                     select new
                     {
                         Settings = (from mFlagSetting in mFlag.in_flag_setting
                                     from mFlagSettingGrant in mFlagSetting.in_flag_settings_grants
                                     where mFlagSettingGrant.role == usr.RoleStr && mFlagSettingGrant.visible
                                     orderby mFlagSetting.creation_date ascending
                                     select new FlagSetting
                                     {
                                         Id = mFlagSetting.id,
                                         FlagId = mFlagSetting.flag,
                                         Name = mFlagSetting.name,
                                         Code = mFlagSetting.code,
                                         EnglishName = mFlagSetting.name,
                                         CreationDate = mFlagSetting.creation_date,
                                         CreationUser = mFlagSetting.creation_user,
                                         LastUpdateUser = mFlagSetting.last_upd_user,
                                         UpdateDate = mFlagSetting.last_upd_date,
                                         Value = mFlagSetting.value
                                     })
                     }).Single();

The issue

The execution of query throws an exception

Exception message:

| An error occurred while executing the command definition. See the inner exception for details.

InnerException | PostgresException 42P08: could not determine data type of parameter $1

Stack trace:

in System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)   in System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)   in System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClassb.<GetResults>b__a()   in System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)   in System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClassb.<GetResults>b__9()   in System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)   in System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)   in System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()   in System.Lazy`1.CreateValue()   in System.Lazy`1.LazyInitValue()   in System.Lazy`1.get_Value()   in System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()   in System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source)   in System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__2[TResult](IEnumerable`1 sequence)   in System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)   in System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)   in System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)   in System.Linq.Queryable.SingleOrDefault[TSource](IQueryable`1 source)   in UserQuery.Main() in c:\Users\dlevis\AppData\Local\Temp\LINQPad\_dlxghkka\query_yifnan.cs:riga 40   in LINQPad.ExecutionModel.ClrQueryRunner.Run()   in LINQPad.ExecutionModel.Server.RunQuery(QueryRunner runner)
--




### Further technical details

Npgsql version: 4.0.6
PostgreSQL version: 9.4
Operating system: Windows 10

It seems to be related to the parameter **usr.RoleStr** in the subquery. If I use a string constant, it works.
If I split the query in two subsequent queries, it works.
With old version of Npgsql 2.2.7, the same query works.

[roji]

Which version of EntityFramework6.Npgsql are you using?

[dlevis85]

EntityFramework6.Npgsql version 3.2.0.0

[jamend]

I think I've run into the same issue. Here is a more concise example to reproduce it:

EntityFramework6.Npgsql 6.4.0
Npgsql 4.1.2
PostgreSQL 10.6

For context, I'm also upgrading from Npgsql 2.2.7 where it worked.

Code:

class TestDbContext : DbContext
{
	public DbSet<TestItem> TestItems { get; set; }

	public class TestItem
	{
		[Key]
		public int Id { get; set; }
		public string Code { get; set; }
	}
}

public void Test()
{
	using (var db = new TestDbContext())
	{
		var code = "X";
		var query = db.TestItems
			.Where(r => code != null && r.Code == code)
			.Select(r => r.Id);

		var sql = query.ToString();

		var result = query
			.ToList();
	}
}

Resulting SQL:

SELECT "Extent1"."Id" FROM "public"."TestItems" AS "Extent1" WHERE @p__linq__0 IS NOT NULL AND ("Extent1"."Code" = @p__linq__1 OR "Extent1"."Code" IS NULL AND @p__linq__1 IS NULL)
 -- p__linq__0: 'X' (Type = Object)
 -- p__linq__1: 'X' (Type = Object)
 -- Failed in 1 ms with error: 42P08: could not determine data type of parameter $1

Exception:
System.Data.Entity.Core.EntityCommandExecutionException: 'An error occurred while executing the command definition. See the inner exception for details.'
Inner Exception
PostgresException: 42P08: could not determine data type of parameter $1

[jamend]

These commits seem suspicious:
npgsql/npgsql@94d99fa
npgsql/npgsql@26acce1

Specifically, the handling of PrimitiveTypeKind.String and DbType.String as NpgsqlDbType.Unknown.

Trying with 3.0.1, I get these parameters and it works:
p__linq__0: 'X' (Type = String)
p__linq__1: 'X' (Type = String)

In 3.0.2, it changes and results in the exception:
p__linq__0: 'X' (Type = Object)
p__linq__1: 'X' (Type = Object)