How to pass composite types to PL/pgsql function?

Question

I am trying (unsuccessfully) to pass a PostgreSql composite type to a PL/pgsql function. The error message and sample code are listed below. I have also tried several different variations on the code (such as this without success - each version generates a different error message). I am new to SQL and expect I am making a simple mistake. I would appreciate if someone can review the sample code below and explain what the mistake is that I have made.

Error message

    System.InvalidCastException: When specifying NpgsqlDbType.Enum, SpecificType must be specified as well
       at Npgsql.TypeHandlerRegistry.get_Item(NpgsqlDbType npgsqlDbType, Type specificType)
       at Npgsql.NpgsqlParameter.ResolveHandler(TypeHandlerRegistry registry)
       at Npgsql.NpgsqlParameter.Bind(TypeHandlerRegistry registry)
       at Npgsql.NpgsqlCommand.ValidateParameters()
       at Npgsql.NpgsqlCommand.d__71.MoveNext()
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
       at Npgsql.NpgsqlCommand.d__87.MoveNext()
    --- End of stack trace from previous location where exception was thrown ---
       at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
       at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
       at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
       at Npgsql.NpgsqlCommand.ExecuteScalar()
       at TestDatabase.TestCompositeType.Test() in F:\Visual Studio 2017\Projects\TestDatabase\TestDatabase\TestCompositeType.cs:line 42
       at TestDatabase.Program.Main(String[] args) in F:\Visual Studio 2017\Projects\TestDatabase\TestDatabase\Program.cs:line 17

System information

    Windows 10, 64 bit
    PostgreSQL 10.3, compiled by Visual C++ build 1800, 64-bit
    Npgsql, Version=3.2.6.0

PostgreSql code

    create schema if not exists MySchema;

    create type MySchema.MyType as(
            X real,
            Y real
        );

    create table if not exists MySchema.MyTable(
        ItemID int primary key generated by default as identity,
        MyType MySchema.MyType
    );

    create or replace function MySchema.SetMyType( 
        ItemID2 int,
        MyType2 MySchema.MyType
    ) 
    returns int
    as $$
    declare
        resultID int;
    begin
        resultID := ItemID2;

        if( exists( select 1 from MySchema.MyTable as mt where mt.ItemID = ItemID2 ) ) then
            insert into MySchema.MyTable( MyType ) 
            values ( MyType2 )
            returning mt.ItemID into resultID;
        else
            update MySchema.MyTable as mt 
            set MyType = MyType2
            where mt.ItemID = ItemID2;
        end if;

        return resultID;
    end;
    $$ language plpgsql;

C# code

    public void Test()
    {
        NpgsqlConnection.MapCompositeGlobally( "MySchema.MyType" );

        var connection = new NpgsqlConnection( "Host=localhost;Username=postgres;Password=123456;database=testdb" );
        if( null == connection )
            throw new NullReferenceException( "connection" );
        try
        {
            connection.Open();

            var cmd = new NpgsqlCommand( "MySchema.SetMyType", connection );
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            var par = new NpgsqlParameter( "ItemID2", NpgsqlDbType.Integer );
            par.Value = 1;
            cmd.Parameters.Add( par );

            par = new NpgsqlParameter( "MyType2", NpgsqlDbType.Composite );
            MyType myType = new MyType();
            myType.X = 1;
            myType.Y = 2;
            par.Value = myType;
            cmd.Parameters.Add( par );

            int id = Convert.ToInt32( cmd.ExecuteScalar() );
        }
        finally
        {
            connection.Close();
        }
    }   

Answer 1

The suggestions made by 'sticky bit' corrected the problem. I have included the updated sample code below for anyone else who might deal with the same issue. All strings are converted to lower case using 'ToLower()' but this is only necessary for mapping the data base type in 'NpgsqlConnection.MapCompositeGlobally'.

    namespace TestDatabase
    {
        public class MyType
        {
            public float X;
            public float Y;
        };

        public class TestCompositeType
        {
            public void Test()
            {
                NpgsqlConnection.MapCompositeGlobally<TestDatabase.MyType>( "MySchema.MyType".ToLower() );

                var connection = new NpgsqlConnection( "Host=localhost;Username=postgres;Password=123456;database=testdb".ToLower() );
                if( null == connection )
                    throw new NullReferenceException( "connection" );
                try
                {
                    connection.Open();

                    var cmd = new NpgsqlCommand( "MySchema.SetMyType".ToLower(), connection );
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;

                    var par = new NpgsqlParameter( "ItemID2".ToLower(), NpgsqlDbType.Integer );
                    par.Value = 1;
                    cmd.Parameters.Add( par );

                    par = new NpgsqlParameter( "MyType2".ToLower(), NpgsqlDbType.Composite );
                    MyType myType = new MyType();
                    myType.X = 1;
                    myType.Y = 2;
                    par.Value = myType;
                    par.SpecificType = typeof( MyType );
                    cmd.Parameters.Add( par );

                    int id = Convert.ToInt32( cmd.ExecuteScalar() );
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }

Answer 2

Before calling connection.Open() you have to map your C# type to the database type by calling NpgsqlConnection.MapCompositeGlobally<>() with your C# type in <> (I'm wondering if your code, which isn't having the <MyType> part, even compiled? I get an error if I try that.)

...
NpgsqlConnection.MapCompositeGlobally<MyType>( "MySchema.MyType" );

var connection = new NpgsqlConnection( "Host=localhost;Username=postgres;Password=123456;database=testdb" );
...

(MyType must have a public constructor not taking any arguments for NpgsqlConnection.MapCompositeGlobally<>() to work with it. But you might already know that.)

Additionally you must set the Property SpecificType of the NpgsqlParameter to your C# type. That's what you seem to have actually missed. (It doesn't matter where you do that (as long as it's before the call of ExecuteScalar() and after the creation of the parameter of course), for example let's put it after you've set the parameter's Value.)

...
myType.X = 1;
myType.Y = 2;
par.Value = myType;
par.SpecificType = typeof(MyType);
cmd.Parameters.Add( par );
...

It's all explained in "Accessing PostgreSQL Enums and Composites".

Apparently it will become easier in version 4.0 of Npgsql. I tested it myself with Npgsql version 3.2.7 BTW.

---

Edit:

Another solution is not to specify the NpgsqlDbType but pass the MyType object in the constructor.

...
MyType myType = new MyType();
myType.X = 1;
myType.Y = 2;
par = new NpgsqlParameter( "MyType2", myType );
cmd.Parameters.Add( par );
...

Then the right Postgres type is revolved by the C# type to Prostgres type mapping set with MapCompositeGlobally<>() earlier. Explicitly setting SpecificType is not required then.