Array of composite type as stored procedure input passed by C# Npgsql

Question

I've read several questions and topics related to the issue but none of them actually helped me to solve my problem and none of them actually were related to C#. And here is the actual problem:

I have a Postgre composite type:

CREATE TYPE law_relation_update_model AS (
   from_celex character varying,
   from_article character varying,
   to_celex character varying,
   to_article character varying,
   link_ids integer[],
   to_doc_par_id integer
);

And I have a stored procedure that is meant to accept an array of the following type:

CREATE OR REPLACE FUNCTION insert_law_relations(_items law_relation_update_model[])
RETURNS VOID
AS
$$
BEGIN

END;
$$
LANGUAGE PLPGSQL;

I've removed the body code since it's not relevant cause the following error is thrown at procedure invoke from C#:

malformed array literal

And an inner message of:

array value must start with “{” or dimension information

C# Model that is about to be passed (an array of it):

public class LawRelationUpdateModel
{
    public string FromCelex { get; set; }

    public string FromArticle { get; set; }

    public string ToCelex { get; set; }

    public string ToArticle { get; set; }

    public IEnumerable<int> LinkIds { get; set; } = new List<int>();

    public int ToDocParId { get; set; }
}

And the method that makes the call using Npgsql connector:

public static void InsertLawRelations(LawRelationUpdateModel[] updateModel)
{
    using (NpgsqlConnection conn = new NpgsqlConnection(connPG))
    {
        conn.Open();
        NpgsqlCommand comm = new NpgsqlCommand("insert_law_relations", conn);
        comm.CommandType = CommandType.StoredProcedure;
        var testParam = new NpgsqlParameter();
        testParam.DbType = DbType.Object;
        testParam.Value = updateModel;
        testParam.ParameterName = "_items";
        comm.Parameters.Add(testParam);

        comm.ExecuteNonQuery();
    }
}

Can it be a bug in the connector that somehow does not convert some single/double quotes or brackets correctly when passing?

Or something related to the fact that I asign DBType as an Object? If I don't I get the following error instead:

Can't cast LawRelationUpdateModel[] into any valid DbType.

Any help regarding the issue or other workarounds will be highly appreciated!

Answer 1

Mapping your CLR Types

It is still possible, and in some cases necessary, to set up an explicit mapping for your CLR types. Doing so provides the following advantages:

You know longer need to specify the DataTypeName property on your parameter. Npgsql will infer the data type from your provided CLR type.

Untyped read methods such as NpgsqlDataReader.GetValue() will return your CLR type, instead of a dynamic object (see below). In general you should be using the typed NpgsqlDataReader.GetFieldValue(), so this shouldn't be important.

You can customize the name mapping on a per-type basis (see below). To set up a global mapping for all your connections, put this code before your first open:

 NpgsqlConnection.GlobalTypeMapper.MapEnum<SomeEnum>("some_enum");
 NpgsqlConnection.GlobalTypeMapper.MapComposite<SomeType>("some_composite");

This sets up a mapping between your CLR types SomeEnum and SomeType to the PostgreSQL types some_enum and some_composite.

If you don't want to set up a mapping for all your connections, you can set it up one connection only:

var conn = new NpgsqlConnection(...);
conn.TypeMapper.MapEnum<SomeEnum>("some_enum");
conn.TypeMapper.MapComposite<SomeType>("some_composite");

http://www.npgsql.org/doc/types/enums_and_composites.html

Answer 2

This has changed in [email protected].

More information in the documenation: https://www.npgsql.org/doc/types/enums_and_composites.html#mapping-your-clr-types.

[email protected]:

create schema dbo;
create type dbo.orgtvp as (
  name varchar(200),
  description text
);
create table dbo.orgs(
    orgid INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(200) NOT NULL,
    description TEXT
);

C# class for your composite type mapping:

public class OrgTvp {
  public string Name { get; set; }
  public string Description { get; set; }
}

C#:

var dataSourceBuilder = new NpgsqlDataSourceBuilder("Host=localhost;Username=databoy;Password=supersecret;Database=mystuff;");
dataSourceBuilder.MapComposite<OrgTvp>("dbo.orgtvp"); // case sensitive
await using var dataSource = dataSourceBuilder.Build();
using var conn = dataSource.CreateConnection();

var orgs = new List<OrgTvp> {
  new OrgTvp { Name = "org from tvp 1", Description = "desc 1" },
  new OrgTvp { Name = "org from tvp 2", Description = "desc 2" },
});
// this is one of dapper's extension methods, but you can also directly call the npgsql functions if you want too.
conn.ExecuteAsync(
  "insert into dbo.orgs (name, description) select name, description from unnest(@orgs);",
  new { orgs }
);

Related: You can pass an array of non-composite types without a mapping, like:

var orgs = await conn.QueryAsync<Org>(
  "select * from dbo.orgs where name = any(@orgNames);",
  new { orgNames = new List<string> { "org from tvp 2" } }
);