npgsql
Error 42P08: could not determine data type of parameter when using subqueries and string variable parameter
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.
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
These commits seem suspicious: https://github.com/npgsql/npgsql/commit/94d99fad7c4bdbdfbf32fea85d0ef6ede15801b5 https://github.com/npgsql/npgsql/commit/26acce17147fe9f9367c17b2675a118601348f4e
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)