.NET 8中通过ADO.NET调用接收复合类型数组的PostgreSQL函数

问题说明 #

你已在PostgreSQL中定义复合类型usertimelogtype和接收该类型数组的函数bulk_upsert_logs，但在.NET 8通过ADO.NET调用时，原基于Npgsql.Composite的方案因API废弃失败。以下是可行的解决方法：

前置准备：完善PostgreSQL函数定义 #

首先确保你的函数定义完整（原代码片段不完整），示例如下：

CREATE OR REPLACE FUNCTION bulk_upsert_logs(userlogs usertimelogtype[])
RETURNS VOID AS $$
BEGIN
    -- 示例UPSERT逻辑，需替换为你的实际业务代码
    FOREACH log IN ARRAY userlogs LOOP
        INSERT INTO your_time_log_table (employeeno, facilityid, activitydate, duration)
        VALUES (log.employeeno, log.facilityid, log.activitydate, log.duration)
        ON CONFLICT (employeeno, activitydate) DO UPDATE
        SET facilityid = EXCLUDED.facilityid, duration = EXCLUDED.duration;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

解决方案步骤 #

1. 映射.NET类与PostgreSQL复合类型

通过Npgsql的类型映射功能，将UserTimeCardLog类与PostgreSQL的usertimelogtype关联，有两种方式：

方式一：使用特性标记类

给UserTimeCardLog添加特性，明确对应关系：

using NpgsqlTypes;

[NpgsqlComposite("usertimelogtype")]
public class UserTimeCardLog
{
    [NpgsqlName("employeeno")]
    public string EmployeeNo { get; set; }
    
    [NpgsqlName("facilityid")]
    public int FacilityId { get; set; }
    
    [NpgsqlName("activitydate")]
    public DateOnly ActivityDate { get; set; }
    
    [NpgsqlName("duration")]
    public int Duration { get; set; }
}

方式二：手动注册类型映射

若不想修改类定义，可在代码中手动注册映射：

// 在应用启动或方法内执行一次即可
if (!NpgsqlConnection.GlobalTypeMapper.IsRegistered<UserTimeCardLog>())
{
    NpgsqlConnection.GlobalTypeMapper.MapComposite<UserTimeCardLog>("usertimelogtype", new[]
    {
        new NpgsqlCompositeMember("employeeno", typeof(string)),
        new NpgsqlCompositeMember("facilityid", typeof(int)),
        new NpgsqlCompositeMember("activitydate", typeof(DateOnly)),
        new NpgsqlCompositeMember("duration", typeof(int))
    });
}

2. 实现函数调用代码

修改AddOrUpdateUserTimeCards方法，完成参数传递与函数调用：

public async Task AddOrUpdateUserTimeCards(List<UserTimeCardLog> userTimeCardLogs)
{
    // 全局注册复合类型（若已在启动时注册可省略）
    NpgsqlConnection.GlobalTypeMapper.MapComposite<UserTimeCardLog>();

    using (var connection = new NpgsqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        
        using (var command = new NpgsqlCommand("SELECT bulk_upsert_logs(@userlogs)", connection))
        {
            // 将List转换为复合类型数组参数
            command.Parameters.Add(new NpgsqlParameter("@userlogs", NpgsqlDbType.Array | NpgsqlDbType.Composite)
            {
                Value = userTimeCardLogs.ToArray(),
                CompositeTypeName = "usertimelogtype"
            });
            
            await command.ExecuteNonQueryAsync();
        }
    }
}

注意事项 #

• 确保使用Npgsql 8.x及以上版本（对应.NET 8），旧版本的Npgsql.Composite已被废弃，当前使用MapComposite方法完成类型映射。
• 复合类型的名称、成员名称必须与PostgreSQL定义完全匹配，可通过NpgsqlName特性处理.NET属性名与数据库字段名的差异。
• DateOnly类型会被Npgsql自动映射为PostgreSQL的DATE类型，无需额外转换。

内容的提问来源于stack exchange，提问作者Prabhath Kumar Rayabarapu