Question about GUID and isIn

[hkirk]

Thanks for the project, seems really nice. Was fidling around yesterday with the isIn and the Guid data type.

So having two tables with a relationship between them

CREATE TABLE Groups (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    group_no SMALLINT,
    semester VARCHAR(5) NOT NULL,
    pattern UUID
);
CREATE TABLE Students (
    id UUID PRIMARY KEY,  
    fullname VARCHAR(100),
    auid VARCHAR(10),
    group_id UUID,
    CONSTRAINT fk_group
      FOREIGN KEY(group_id)
          REFERENCES Groups(id)
);

Trying to select all students that are in a list of groups like

let groupIds: Guid list = groups |> Seq.map _.id |> Seq.toList 

// Get all students for these groups
let! students = 
    select {
        for s in studentsTable do
        where (isIn s.group_id groupIds)
    }
    |> conn.SelectAsync<Models.Student> |> Async.AwaitTask

This compiles and seems like it should work, you have a test case (

Dapper.FSharp/tests/Dapper.FSharp.Tests/PostgreSQL/DeleteTests.fs

Line 139 in a83c9d5

where (isIn p.Id personIds)

) here that looks much like what im trying to get to work.

But when using this with PostgresSQL it throws an execption

api-1         | info: Microsoft.AspNetCore.Hosting.Diagnostics[1]
api-1         |       Request starting HTTP/1.1 GET http://api:5100/api/pattern/available - - -
api-1         | Generated SQL: DELETE FROM "students" WHERE "students"."group_id" <> ALL (@Where_students_group_id1) ---- map [(Where_students_group_id1, System.Object[])]
api-1         | fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
api-1         |       An unhandled exception has occurred while executing the request.
api-1         |       System.AggregateException: One or more errors occurred. (Writing values of 'System.Object[]' is not supported for parameters having no NpgsqlDbType or DataTypeName. Try setting one of these values to the expected database type..)
api-1         |        ---> System.InvalidCastException: Writing values of 'System.Object[]' is not supported for parameters having no NpgsqlDbType or DataTypeName. Try setting one of these values to the expected database type..
api-1         |          at Npgsql.Internal.AdoSerializerHelpers.<GetTypeInfoForWriting>g__ThrowWritingNotSupported|1_0(Type type, PgSerializerOptions options, Nullable`1 pgTypeId, Nullable`1 npgsqlDbType, Exception inner)
api-1         |          at Npgsql.Internal.AdoSerializerHelpers.GetTypeInfoForWriting(Type type, Nullable`1 pgTypeId, PgSerializerOptions options, Nullable`1 npgsqlDbType)
api-1         |          at Npgsql.NpgsqlParameter.ResolveTypeInfo(PgSerializerOptions options, IDbTypeResolver dbTypeResolver)
api-1         |          at Npgsql.NpgsqlParameterCollection.ProcessParameters(ReloadableState reloadableState, Boolean validateValues, CommandType commandType)
api-1         |          at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
api-1         |          at Npgsql.NpgsqlCommand.ExecuteReader(Boolean async, CommandBehavior behavior, CancellationToken cancellationToken)
api-1         |          at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
api-1         |          at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 434

[Dzoukr]

Hi @hkirk, this looks really weird. I suspect this has something to do with a Npgsql / Postgres version, but I need to investigate deeper. Just to be 100%... does the test work on your setup, or is it failing as well?

[Dzoukr]

If you could create a small repro repo / add a test with failure, I can look at it.