c# - Dapper 可以将用户定义的复合类型传递给 PostgreSQL 函数吗？

Question

我试图弄清楚如何使用 Dapper 将用户定义的复合类型传递给 PostgreSQL 函数。我知道这对 SQL Server 是可能的，而且我已经有了使用 Dapper+SQL Server 的工作示例，但是，我对如何使用 PostgreSQL 做同样的事情很不了解。

从我读过的一些内容来看，我不确定 Dapper+PostgreSQL 是否可行，但我知道它确实适用于普通的 Npgsql(我也有一个工作示例)。

所以，如何使用 Dapper 调用接受用户定义复合类型的 PostgreSQL 函数？

示例用户定义的复合类型

CREATE TYPE hero AS (
    first_name text,
    last_name text
);

接受用户定义的复合类型的示例 PostgreSQL 函数

CREATE OR REPLACE FUNCTION testfuncthattakesinudt(our_hero hero)
    RETURNS SETOF characters 
    LANGUAGE 'sql'

    STABLE
    ROWS 1000
AS $BODY$

    SELECT  *
    FROM    characters
    WHERE   first_name = COALESCE(our_hero.first_name, '')
    AND     last_name = COALESCE(our_hero.last_name, '');

$BODY$;

理论 C# 示例

[Test]
public void UsingDapper_Query_CallFunctionThatTakesInUserDefinedCompositeType_FunctionUsesUserDefinedCompositeType()
{
    // Arrange
    using (var conn = new NpgsqlConnection(_getConnectionStringToDatabase()))
    {
        var funcName = "testfuncthattakesinudt";
        var expect = CharacterTestData.First();

        // Act
        var result = conn.Query<Character>(funcName,
            new
            {
                our_hero = new
                {
                    first_name = CharacterTestData.First().first_name,
                    last_name = CharacterTestData.First().last_name
                }
            },
            commandType: CommandType.StoredProcedure
        );

        // Assert
        Assert.AreEqual(expect, result);
    }
}

我知道使用普通的 Npgsql，有必要创建类似于以下内容的参数:

var udtCompositeParameter = new NpgsqlParameter
{
    ParameterName = "our_hero",
    Value = new
    {
        first_name = CharacterTestData.First().first_name,
        last_name = CharacterTestData.First().last_name
    },
    DataTypeName = "hero"
};

但是使用Dapper，我还没有找到设置 DataTypeName的方法或类似的东西。我尝试了许多不同的方法来塑造 Dapper 的参数(例如使用类似 DynamicParameter 的东西并指定 dbType: DbType.Object )，但无论如何，我总是得到一些与复合类型相关的类似错误。我也看过 Dapper source ，但从我所看到的，PostgreSQL 特定的测试和那些似乎符合我正在尝试做的测试是针对 SQL Server 量身定制的。

Answer 1

更新

基于此 answer ，我们能够将这个概念应用于这个问题，并提供一个更清晰的结果，不需要使用 TypeHandler s。

工作集成测试

public void Query_CallFunctionThatTakesInUserDefinedType_FunctionUsesUserDefinedType()
{
    // Arrange
    using (var conn = Db.GetConnection())
    {
        var functionName = "test_function_that_takes_in_udt";
        var expect = CharacterTestData.First();

        // Act
        var result = conn.Query<Character>(functionName,
            new 
            {
                our_hero = new HeroParameter(
                    CharacterTestData.First().FirstName,
                    CharacterTestData.First().LastName
                )
            },
            commandType: CommandType.StoredProcedure
        ).FirstOrDefault();

        // Assert
        Assert.AreEqual(expect, result);
    }
}

注意 自原始问题以来，上面的代码中有一些重构(例如名称)，但是，PostgreSQL 函数的主体没有改变。与此答案相关的最显着重构是如何创建连接:

public NpgsqlConnection GetConnection()
{
    var connection = new NpgsqlConnection(GetConnectionStringToDatabase());
    Dapper.DefaultTypeMap.MatchNamesWithUnderscores = true;
    connection.Open();
    connection.ReloadTypes();

    /*
     * 
     * Ideally, should move this to be handled for _all_ connections like:
     *      NpgsqlConnection.GlobalTypeMapper.MapEnum<SomeEnum>("some_enum_type");
     *      NpgsqlConnection.GlobalTypeMapper.MapComposite<SomeType>("some_composite_type");
     *  
     */
    connection.TypeMapper.MapComposite<Hero>("hero");

    return connection;
}

支持英雄参数

public class HeroParameter : ICustomQueryParameter
{
    private readonly Hero _hero;

    public HeroParameter(string firstName, string lastName)
    {
        _hero = new Hero(firstName, lastName);
    }

    public void AddParameter(IDbCommand command, string name)
    {
        var parameter = new NpgsqlParameter
        {
            ParameterName = name,
            Value = _hero,
            DataTypeName = "hero"
        };
        command.Parameters.Add(parameter);
    }
}

不使用 TypeHandlers事实证明，在我们的情况下是非常有利的。

这是由于 PostgreSQL 在从函数返回时可能使用 UDT/复合类型的挑剔性质。例如，如果 UDT 是要返回的 2 个或更多列之一，则 PostgreSQL 将返回带有 (val1, val2) 形状的 UDT 列的结果集。 .但是，如果返回的只是 UDT，PostgreSQL 会将 UDT 的各个属性扩展到各个列，很像普通的 SELECT从一张 table 。

例如，考虑以下函数:

CREATE OR REPLACE FUNCTION example_function()
    RETURNS hero
    LANGUAGE SQL
AS
$$
SELECT ('Peter', 'Parker')::hero as heroes
$$

在这种情况下，为了 TypeHandler要工作，我们需要结果采用以下格式:

|-----------------|
|      heroes     |
|-----------------|
| (Peter, Parker) |
|-----------------|

这是因为在像 conn.Query<Hero>(...) 这样的电话之后, Dapper 会将第一列(并且只有第一列)传递给 TypeHandler期望它进行必要的转换。

但是，上述函数的输出 example_function , 实际上会以以下扩展格式返回结果:

|------------|-----------|
| first_name | last_name |
|------------|-----------|
|   Peter    |  Parker   |
|------------|-----------|

这意味着 Type的 value传递给 TypeHandler.Parse()方法是 string在这个例子中。

但是，对于在返回 2 个或更多列时将 UDT 作为列之一返回的函数，则 TypeHandler按预期工作，因为单列的值传递给 Parse方法。

考虑这个更新的函数:

CREATE OR REPLACE FUNCTION example_function()
    RETURNS TABLE (year integer, hero hero)
    LANGUAGE SQL
AS
$$
SELECT 1962 AS year, ('Peter', 'Parker')::hero AS hero
$$

它以以下格式返回输出:

|------|-----------------|
| year |      hero       |
|------|-----------------|
| 1962 | (Peter, Parker) |
|------|-----------------|

这是下面的原始解决方案不足的地方。在那个例子中，我(还)没有使用 Parse方法。但是，一旦我需要实现该函数以支持返回的 UDT， TypeHandler根据上面演示的 PostgreSQL 返回 UDT 的方式，它不会工作。

原答案

对于可能偶然发现这个问题的任何其他人，这对我有用，尽管我对此并不十分满意，所以我愿意为这个问题提供更好的解决方案。

工作集成测试

[Test]
public void Query_CallFunctionThatTakesInUserDefinedType_FunctionUsesUserDefinedType()
{
    // Arrange
    using (var conn = new NpgsqlConnection(Db.GetConnectionStringToDatabase()))
    {
        var funcName = "testfuncthattakesinudt";
        var expect = CharacterTestData.First();

        SqlMapper.AddTypeHandler(new HeroTypeHandler());
        conn.Open();
        conn.ReloadTypes();
        conn.TypeMapper.MapComposite<Hero>("hero");

        // Act
        var result = conn.Query<Character>(funcName,
            new
            {
                our_hero = new Hero
                {
                    first_name = CharacterTestData.First().first_name,
                    last_name = CharacterTestData.First().last_name
                }
            },
            commandType: CommandType.StoredProcedure
        ).FirstOrDefault();

        // Assert
        Assert.AreEqual(expect, result);
    }
}

支持 HeroTypeHandler

internal class HeroTypeHandler : SqlMapper.TypeHandler<Hero>
{
    public override Hero Parse(object value)
    {
        throw new NotImplementedException();
    }

    public override void SetValue(IDbDataParameter parameter, Hero value)
    {
        parameter.Value = value;
    }
}

修复似乎分为两部分:

有必要添加一个 HeroTypeHandler并通过 SqlMapper.AddTypeHandler 映射它.

我需要映射我的类型 Hero到我的 PostgreSQL 复合类型 hero通过 conn.TypeMapper.MapComposite .然而，我的直觉(到目前为止)是这只是我在与我自己的集成测试作斗争，因为在实际应用程序中，在应用程序开始时全局注册所有复合类型可能(？)是理想的。 (或者可能不是由于性能原因，如果有很多？)

我不喜欢这个解决方案的是我的 HeroTypeHandler并没有真正提供任何真正的值(value)(没有双关语意)。通过简单地分配 value至 parameter.Value事情奏效了，我的猜测是，这是 Dapper 会为电话所做的事情，但显然不是。 (?) 如果我有很多复合类型，我宁愿不需要为很多复合类型这样做。

请注意，由于我只关心将此类型发送到 PostgreSQL 函数，因此我认为没有必要实现 Parse方法，因此 NotImplementedException .青年会

此外，由于我发布原始问题后进行了一些重构，因此还有一些其他细微差别。但是，它们与上面详述的整体修复无关。