Using Postgres function with UUID parameter throws exception

[rndor]

Issue

Using a Postgres function like this one FUNCTION test.update_widget(_id UUID, _name TEXT) RETURNS VOID throws exception:

Npgsql.PostgresException: 42883: function test.update_widget(_id => text, _name => text) does not exist
  at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:1012
  at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:1032
  at at System.Threading.Tasks.ValueTask`1.get_Result()
  at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:444
  at Npgsql.NpgsqlDataReader.NextResult() in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:332
  at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:1219
  at at System.Threading.Tasks.ValueTask`1.get_Result()
  at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:1130
  at at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
  at at Insight.Database.DBConnectionExtensions.ExecuteAndAutoClose[T](IDbConnection connection, Func`2 getCommand, Func`3 translate, CommandBehavior commandBehavior)
  at at Insight.Database.DBConnectionExtensions.Insert[TResult](IDbConnection connection, String sql, TResult inserted, Object parameters, CommandType commandType, CommandBehavior commandBehavior, Nullable`1 commandTimeout, IDbTransaction transaction, Object outputParameters)
  at at WebApplication1.IWidgetRepositoryf8c708ea-e69e-492b-8970-b0d1d60fd08a.UpdateWidget(Widget widget)

Steps to reproduce

Database

Create table:

CREATE SCHEMA test;
CREATE TABLE test.widget
(
    id UUID PRIMARY KEY NOT NULL,
    name TEXT
)

--- Insert test row
INSERT INTO test.widget (id, name) VALUES('30f37a32-6ed5-42c2-bcb0-72469de9d66a', 'test widget');

and plpgsql functions:

CREATE OR REPLACE FUNCTION test.update_widget(_id UUID, _name TEXT) RETURNS VOID AS
$$
BEGIN
    UPDATE test.widget SET name = _name WHERE id = _id;
END;
$$ LANGUAGE plpgsql;

C# code

Along with the following C# code:

public class Widget {
    public Guid Id { get; set; }
    public string Name { get; set; }
}

public interface IWidgetRepository {
    [Sql("update_widget", Schema = "test")]
    void UpdateWidget(Widget widget);
}

Run the following (i.e. in Startup()):

ColumnMapping.Parameters.RemovePrefixes("_");

var db = new NpgsqlConnectionStringBuilder(... my connection string ...);

using (var conn = db.Connection()) {
    var testWidget = new Widget { Id = "30f37a32-6ed5-42c2-bcb0-72469de9d66a", Name = "Updated Name" };

    repo.UpdateWidget(testWidget);
} 

Expected behavior

The record should be updated by calling the update_widget function. It appears that Insight tries to look for the wrong function function test.update_widget(_id => text, _name => text) instead of function test.update_widget(_id => uuid, _name => text).

(This issue does not appear to occur if the id column (and corresponding function signature) are changed to an INT type.)

• Dotnet version: netcore2.1
• Database: Postgres 10.5
• Library version: 6.2.4

[jonwagner]

In your code, you're creating a parameter object with a string for the id:

    var testWidget = new Widget { Id = "30f37a32-6ed5-42c2-bcb0-72469de9d66a", Name = "Updated Name" };

Have you tried making it a guid?

    var testWidget = new Widget { Id = Guid.Parse("30f37a32-6ed5-42c2-bcb0-72469de9d66a"), Name = "Updated Name" };

[rndor]

Sorry, I apparently copied the wrong code in my example. That bit should be as you suggested:

var testWidget = new Widget { Id = Guid.Parse("30f37a32-6ed5-42c2-bcb0-72469de9d66a"), Name = "Updated Name" };

And this, unfortunately, raises the exception as mentioned above.

[jonwagner]

This was due to a typo in the parameter type detection on netstandard1.5/2.0. Now that npgsql 4.0 is out, we can use their built-in parameter detector.

This is fixed in v6.2.5 to be released next week.

This does not affect the .NET Framework versions of Insight, so using one of those versions is a workaround in the meantime.

[jonwagner]

6.2.5 is now available