Guid arrays doesn't work as parameters with Postgresql

[Evengard]

Referring the discussion from https://github.com/linq2db/linq2db/issues/1660 The following query fails with PostgreSQL (using Npgsql as provider):

var guids = new Guid[] { Guid.Parse("271425b1-ebe8-400d-b71d-a6e47a460ae3"),
    Guid.Parse("b75de94e-6d7b-4c70-bfa1-f8639a6a5b35") };

var result = await (
        from m in _context.Materials.ToLinqToDBTable()
        where Sql.Ext.PostgreSQL().Overlaps(m.ProgramIds, guids)
        select m
    )
    .ToArrayAsyncLinqToDB();

_context is an EF Core DbContext, Materials is a DbSet, m.ProgramIds is a Guid[]. EF Core (or is it Npgsql?) maps Guid[] into uuid[].

More information is found in the referred issue above.

[Evengard]

The suggested workaround

LinqToDBForEFTools.Implementation = new LinqToDBForEFToolsWithFix();

class LinqToDBForEFToolsWithFix : LinqToDBForEFToolsImplDefault
    {
        public override MappingSchema GetMappingSchema(IModel model, IMetadataReader metadataReader, IValueConverterSelector convertorSelector)
        {
            var ms = base.GetMappingSchema(model, metadataReader, convertorSelector);
            ms.SetConvertExpression<Guid[], DataParameter>(v => new DataParameter(null, v));
            return ms;
        }
    }

didn't work, resulting in 'Cannot convert value of type System.Byte[][] to SQL' exception.

Another working workaround - is to just create a TempTable instead of an array variable, joining with it and using ValueIsEqualToAny instead of Overlaps.

[MaceWindu]

That's strange that is didn't worked. At least it works for me using your test query. Maybe there is something more in your real query and in that case we need another not working example to fix it

[MaceWindu]

BTW, Cannot convert value of type System.Byte[][] to SQL error means that linq2db tries to generate SQL literal. I've checked code but cannot tell where it could be done for your case, so we definitely need failing sample

[Evengard]

I tried the exact same query though, and I had this error. Maybe there is something about dbContext configuration? That's actually strange, my first blocker with linq2db which otherwise was working just amazing.

[sdanyliv]

@Evengard, could you please post your model at least one failed class?

[MaceWindu]

Also could you post stack trace for last error?

[Evengard]

The class from my model:

[Table("Materials")]
public class Material
{
    [Key]
    public Guid MaterialId { get; set; }

    public Guid[] ProgramIds { get; set; }

    [NotMapped]
    public UserProfile Author => Versions
                .OrderBy(v => v.CreationDate)
                .First().Author;

    [NotMapped]
    public string Name => Versions
                .Where(v => (v.Status.HasFlag(Published) && !v.Status.HasFlag(Unpublished)))
                .DefaultIfEmpty( // If we didn't found any published versions, then just retrieve the latest unpublished
                    Versions
                        .OrderByDescending(v => v.CreationDate)
                        .First()
                )
                .OrderByDescending(v => v.CreationDate)
                .First().Name;

    [NotMapped]
    public DateTime CreationDate => Versions
                    .OrderBy(v => v.CreationDate)
                    .First().CreationDate;

    [InverseProperty("Material")]
    public virtual ICollection<AMaterialVersion> Versions { get; set; }

    public bool IsRemoved { get; set; }

    public Guid[] LinkedMaterialIds { get; set; }

    // This is a special field which is populated separately with a second query
    [NotMapped]
    public Guid[] LinkedFromMaterialIds { get; set; }
}

The stacktrace without the workaround enabled:

System.Exception: While trying to write an array, one of its elements failed validation. You may be trying to mix types in a non-generic IList, or to write a jagged array.
       ---> System.InvalidCastException: Can't write CLR type System.Byte[] with handler type UuidHandler
         at lambda_method100(Closure , NpgsqlTypeHandler , Object , NpgsqlLengthCache& , NpgsqlParameter )
         at Npgsql.TypeHandling.NpgsqlSimpleTypeHandler`1.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter)
         at Npgsql.TypeHandlers.ArrayHandler`1.ValidateAndGetLengthNonGeneric(ICollection value, NpgsqlLengthCache& lengthCache)
         --- End of inner exception stack trace ---
         at Npgsql.TypeHandlers.ArrayHandler`1.ValidateAndGetLengthNonGeneric(ICollection value, NpgsqlLengthCache& lengthCache)
         at Npgsql.TypeHandlers.ArrayHandler`1.ValidateAndGetLength(Object value, NpgsqlLengthCache& lengthCache)
         at Npgsql.TypeHandlers.ArrayHandler`1.ValidateObjectAndGetLength(Object value, NpgsqlLengthCache& lengthCache, NpgsqlParameter parameter)
         at Npgsql.NpgsqlParameter.ValidateAndGetLength()
         at Npgsql.NpgsqlCommand.ValidateParameters(ConnectorTypeMapper typeMapper)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
         at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at LinqToDB.Data.DataConnection.ExecuteReaderAsync(CommandBehavior commandBehavior, CancellationToken cancellationToken)

The stacktrace with the workaround enabled (the query seems to work fine, it seems to be crashing AFTER the query when converting data back from DB to entities):

LinqToDB.LinqToDBException: 'Cannot convert value of type System.Byte[][] to SQL'
   at LinqToDB.SqlProvider.ValueToSqlConverter.Convert(StringBuilder stringBuilder, SqlDataType dataType, Object value)
   at LinqToDB.EntityFrameworkCore.LinqToDBForEFToolsImplDefault.<>c__DisplayClass14_1.<DefineConvertors>b__3(StringBuilder sb, SqlDataType dt, Object v)
   at LinqToDB.SqlProvider.ValueToSqlConverter.TryConvertImpl(StringBuilder stringBuilder, SqlDataType dataType, Object value, Boolean tryBase)
   at LinqToDB.SqlProvider.ValueToSqlConverter.TryConvertImpl(StringBuilder stringBuilder, SqlDataType dataType, Object value, Boolean tryBase)
   at LinqToDB.SqlProvider.ValueToSqlConverter.TryConvert(StringBuilder stringBuilder, Object value)
   at LinqToDB.SqlProvider.BasicSqlBuilder.PrintParameters(StringBuilder sb, IEnumerable`1 parameters)
   at LinqToDB.Data.TraceInfo.get_SqlText()
   at LinqToDB.EntityFrameworkCore.LinqToDBForEFToolsImplDefault.LogConnectionTrace(TraceInfo info, ILogger logger)
   at LinqToDB.EntityFrameworkCore.LinqToDBForEFTools.<>c__DisplayClass32_0.<EnableTracing>b__0(TraceInfo t)
   at LinqToDB.Data.DataConnection.<ExecuteReaderAsync>d__14.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at LinqToDB.Data.DataConnection.QueryRunner.<ExecuteReaderAsync>d__35.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at LinqToDB.Linq.QueryRunner.<ExecuteQueryAsync>d__12`1.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at LinqToDB.Linq.QueryRunner.<ExecuteQueryAsync>d__12`1.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable.ConfiguredTaskAwaiter.GetResult()
   at LinqToDB.Linq.ExpressionQuery`1.<GetForEachAsync>d__19.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable.ConfiguredTaskAwaiter.GetResult()
   at LinqToDB.AsyncExtensions.<ToArrayAsync>d__9`1.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()

[MaceWindu]

That's interesting! basically it generated query properly, but failed to log executed sql (see it fail in LogConnectionTrace). I will look into it, but I think if you disable logging, it will help.

[MaceWindu]

@Evengard , as workaround you could add following conversion to workaround:

ms.SetValueToSqlConverter(typeof(Guid[]), (sb, type, v) => sb.Append("ERROR"));

note that it adds invalid Guid[] to literal conversion, so you shouldn't enable parameters inlining, or it will be used in real SQL (and fail).

@sdanyliv, something should be done about incorrect conversion import here. If we cannot detect that it is not correct, we should allow users to define import filter I suppose...

[Evengard]

Now the workaround seems to work fine, except the weird entry SET @guids = ERROR in the log - but it is apparently expected.

[Evengard]

Have no ideas about the correctness, but using

ms.SetValueToSqlConverter(typeof(Guid[]), (sb, type, v) =>
{
    sb.Append("ARRAY [");
    sb.Append(string.Join(',', ((Guid[])v).Select(g => $"\"{g}\"")));
    sb.Append(']');
});

instead of ms.SetValueToSqlConverter(typeof(Guid[]), (sb, type, v) => sb.Append("ERROR")); to have something at least mimicking real SQL syntax at this point...

Now I guess this is not related, but as a side note using .Include(m => m.Versions) doesn't seem to work.

[Evengard]

Well I'm still having troubles with it, now in the ValueIsEqualToAny. Consider this query:

var guids = new Guid[] { Guid.Parse("be8a458d-40f8-4a5d-b92c-6f15c3683b42") };
var result = await (
                    from m in _context.Materials.ToLinqToDBTable()
                    where Sql.Ext.PostgreSQL().ValueIsEqualToAny(m.MaterialId, guids)
                    select m
                )
                .ToArrayAsyncLinqToDB();

System.InvalidOperationException: 'No coercion operator is defined between types 'System.Guid[]' and 'System.Guid'.'

   at System.Linq.Expressions.Expression.GetUserDefinedCoercionOrThrow(ExpressionType coercionType, Expression expression, Type convertToType)
   at System.Linq.Expressions.Expression.Convert(Expression expression, Type type, MethodInfo method)
   at System.Linq.Expressions.Expression.Convert(Expression expression, Type type)
   at LinqToDB.Mapping.MappingSchema.GenerateSafeConvert(Type fromType, Type type)
   at LinqToDB.Linq.Builder.ExpressionBuilder.PrepareConvertersAndCreateParameter(ValueTypeExpression newExpr, Expression valueExpression, String name, ColumnDescriptor columnDescriptor, BuildParameterType buildParameterType)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildParameter(Expression expr, ColumnDescriptor columnDescriptor, Boolean forceConstant, BuildParameterType buildParameterType)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertToSql(IBuildContext context, Expression expression, Boolean unwrap, ColumnDescriptor columnDescriptor, Boolean isPureExpression)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertToExtensionSql(IBuildContext context, Expression expression, ColumnDescriptor columnDescriptor)
   at LinqToDB.Linq.Builder.ExpressionBuilder.<>c__DisplayClass129_0.<ConvertExtensionToSql>b__0(Expression e, ColumnDescriptor descriptor)
   at LinqToDB.Sql.ExtensionAttribute.BuildExtensionParam(IDataContext dataContext, SelectQuery query, MemberInfo member, Expression[] arguments, ConvertHelper convertHelper)
   at LinqToDB.Sql.ExtensionAttribute.BuildFunctionsChain(IDataContext dataContext, SelectQuery query, Expression expr, ConvertHelper convertHelper)
   at LinqToDB.Sql.ExtensionAttribute.GetExpression(IDataContext dataContext, SelectQuery query, Expression expression, Func`3 converter)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertExtensionToSql(IBuildContext context, ExpressionAttribute attr, MethodCallExpression mc)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertToSql(IBuildContext context, Expression expression, Boolean unwrap, ColumnDescriptor columnDescriptor, Boolean isPureExpression)
   at LinqToDB.Linq.Builder.ExpressionBuilder.ConvertPredicate(IBuildContext context, Expression expression)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSearchCondition(IBuildContext context, Expression expression, List`1 conditions)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildWhere(IBuildContext parent, IBuildContext sequence, LambdaExpression condition, Boolean checkForSubQuery, Boolean enforceHaving)
   at LinqToDB.Linq.Builder.WhereBuilder.BuildMethodCall(ExpressionBuilder builder, MethodCallExpression methodCall, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.MethodCallBuilder.BuildSequence(ExpressionBuilder builder, BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.BuildSequence(BuildInfo buildInfo)
   at LinqToDB.Linq.Builder.ExpressionBuilder.Build[T]()
   at LinqToDB.Linq.Query`1.CreateQuery(IDataContext dataContext, Expression expr)
   at LinqToDB.Linq.Query`1.GetQuery(IDataContext dataContext, Expression& expr)
   at LinqToDB.Linq.ExpressionQuery`1.GetQuery(Expression& expression, Boolean cache)
   at LinqToDB.Linq.ExpressionQuery`1.<GetForEachAsync>d__19.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable.ConfiguredTaskAwaiter.GetResult()
   at LinqToDB.AsyncExtensions.<ToArrayAsync>d__9`1.MoveNext()
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()

[sdanyliv]

@Evengard, try this workaround:

LinqToDBForEFTools.Implementation = new LinqToDBForEFToolsWithFix();

class LinqToDBForEFToolsWithFix : LinqToDBForEFToolsImplDefault
{
    public override void DefineConvertors(
        MappingSchema mappingSchema,
        IModel model,
        IValueConverterSelector? convertorSelector)
    {
    }
}

[sdanyliv]

Actually fix released.

[Evengard]

Erhm the fix does help with Overlaps, but ValueIsEqualToAny still throws the same (no coercion found... See above). The proposed workaround didn't help either.

[Evengard]

Should I open a new issue about ValueIsEqualToAny, or will you reopen this one?

[MaceWindu]

https://github.com/linq2db/linq2db/issues/2979