如何使用Dapper与Oracle将数对集合传入IN子句作为参数？

针对需要将(COLUMN_A, COLUMN_B)数对集合作为参数传入Oracle查询的场景，以下是两种可行的实现方式：

方法一：使用Oracle自定义对象与嵌套表（推荐大量数对场景） #

Oracle支持通过自定义UDT（用户定义类型）传递集合参数，步骤如下：

1. 在Oracle数据库创建自定义类型 #

先在数据库中定义存储数对的对象类型，以及包含该对象的嵌套表类型：

CREATE TYPE PAIR_TYPE AS OBJECT (
    A NUMBER,
    B NUMBER
);
/

CREATE TYPE PAIR_TABLE_TYPE AS TABLE OF PAIR_TYPE;
/

2. C#中定义对应实体类 #

public class Pair
{
    public int A { get; set; }
    public int B { get; set; }
}

3. 转换C#集合为Oracle UDT参数并执行查询 #

需要将C#的List<Pair>转换为Oracle识别的嵌套表参数，再通过Dapper执行查询：

var targetPairs = new List<Pair>
{
    new Pair { A = 1, B = 2 },
    new Pair { A = 3, B = 4 },
    new Pair { A = 5, B = 6 }
};

// 构造Oracle自定义类型参数
var pairTableParam = new OracleParameter("parameters", OracleDbType.Object, ParameterDirection.Input);
pairTableParam.UdtTypeName = "PAIR_TABLE_TYPE";
pairTableParam.Value = ConvertToOraclePairTable(targetPairs, connection);

// 执行Dapper查询
var result = connection.Query<string>(@"
    SELECT COLUMN_C FROM SOME_TABLE
    WHERE (COLUMN_A, COLUMN_B) IN (SELECT A, B FROM TABLE(:parameters))
", new { parameters = pairTableParam });

补充：C#集合转Oracle UDT的工具方法 #

private static OracleUdt ConvertToOraclePairTable(List<Pair> pairs, OracleConnection connection)
{
    // 创建嵌套表UDT实例
    var tableUdt = OracleUdt.CreateUdt(connection, "PAIR_TABLE_TYPE");
    var array = (OracleArrayType)tableUdt.Value;

    // 逐个将C# Pair转换为对象UDT
    for (int i = 0; i < pairs.Count; i++)
    {
        var pairUdt = OracleUdt.CreateUdt(connection, "PAIR_TYPE");
        pairUdt.SetValue("A", pairs[i].A);
        pairUdt.SetValue("B", pairs[i].B);
        array.SetValue(i, pairUdt);
    }

    tableUdt.Value = array;
    return tableUdt;
}

方法二：动态生成参数占位符（适合少量数对场景） #

如果不需要创建数据库类型，可以通过动态生成参数名和IN子句的方式实现，代码更简洁：

var targetPairs = new List<Pair>
{
    new Pair { A = 1, B = 2 },
    new Pair { A = 3, B = 4 },
    new Pair { A = 5, B = 6 }
};

var parameters = new DynamicParameters();
var inClauseSegments = new List<string>();

// 为每个数对生成独立参数
for (int i = 0; i < targetPairs.Count; i++)
{
    var aParam = $"pair_{i}_a";
    var bParam = $"pair_{i}_b";
    inClauseSegments.Add($"(:{aParam}, :{bParam})");
    parameters.Add(aParam, targetPairs[i].A, OracleDbType.Int32);
    parameters.Add(bParam, targetPairs[i].B, OracleDbType.Int32);
}

// 拼接完整SQL并执行
var sql = $"SELECT COLUMN_C FROM SOME_TABLE WHERE (COLUMN_A, COLUMN_B) IN ({string.Join(", ", inClauseSegments)})";
var result = connection.Query<string>(sql, parameters);

两种方法对比 #

• 自定义类型法：无参数数量限制，符合Oracle最佳实践，但需要提前在数据库创建类型，代码相对复杂。
• 动态参数法：无需数据库变更，代码简洁，但数对数量过多时（如超过数百个）可能触发Oracle的参数数量或SQL长度限制。

内容的提问来源于stack exchange，提问作者Petr Hruzek