C#中如何在SQL查询中引用内存列表/字典实现数据匹配

1. 优化集合创建：将集合初始化移至循环外 #

首先解决重复创建集合的问题——只查询一次Oracle表T1的deptRef数据并缓存，避免在循环内重复执行查询和集合初始化：

// 仅执行一次，获取T1的deptRef集合
List<string> deptRefList = new List<string>();
using (OracleConnection oracleConn = new OracleConnection(yourOracleConnStr))
{
    oracleConn.Open();
    string oracleSql = "SELECT deptRef FROM T1";
    using (OracleCommand cmd = new OracleCommand(oracleSql, oracleConn))
    {
        using (OracleDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                deptRefList.Add(reader.GetString(0));
            }
        }
    }
}

// 后续循环处理T2查询时，直接复用已初始化的deptRefList
foreach (var item in yourLoopDataset)
{
    QueryT2FilteredByDeptRef(deptRefList);
}

2. 在SQL Server查询中引用内存集合 #

方法一：表值参数（推荐，高性能+防注入） #

SQL Server支持表值参数，可直接将内存集合传入SQL语句，完全避免SQL注入风险，适合大数据量场景：

1. 先在SQL Server中创建自定义表类型：

CREATE TYPE dbo.DeptRefTableType AS TABLE (deptRef VARCHAR(50)) -- 字段类型与T1、T2的deptRef匹配

2. C#代码中使用表值参数：

void QueryT2FilteredByDeptRef(List<string> deptRefList)
{
    if (deptRefList.Count == 0) return;

    using (SqlConnection sqlConn = new SqlConnection(yourSqlServerConnStr))
    {
        sqlConn.Open();
        string sql = @"SELECT * FROM T2 
                       WHERE deptRef IN (SELECT deptRef FROM @DeptRefs)";
        
        using (SqlCommand cmd = new SqlCommand(sql, sqlConn))
        {
            // 将List转为DataTable，适配表值参数
            DataTable dt = new DataTable();
            dt.Columns.Add("deptRef", typeof(string));
            foreach (var refVal in deptRefList)
            {
                dt.Rows.Add(refVal);
            }

            // 添加表值参数
            SqlParameter param = cmd.Parameters.Add("@DeptRefs", SqlDbType.Structured);
            param.TypeName = "dbo.DeptRefTableType"; // 对应SQL中创建的表类型
            param.Value = dt;

            // 执行查询并处理结果
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    // 处理T2数据逻辑
                }
            }
        }
    }
}

方法二：参数化IN子句（适合小集合） #

如果deptRefList元素数量较少（如不超过1000），可动态生成参数占位符，既避免SQL注入，又无需创建SQL Server表类型：

void QueryT2FilteredByDeptRef(List<string> deptRefList)
{
    if (deptRefList.Count == 0) return;

    using (SqlConnection sqlConn = new SqlConnection(yourSqlServerConnStr))
    {
        sqlConn.Open();
        // 生成参数占位符（如@p0, @p1...）
        string placeholders = string.Join(", ", deptRefList.Select((_, idx) => $"@p{idx}"));
        string sql = $"SELECT * FROM T2 WHERE deptRef IN ({placeholders})";
        
        using (SqlCommand cmd = new SqlCommand(sql, sqlConn))
        {
            // 逐个添加参数
            for (int i = 0; i < deptRefList.Count; i++)
            {
                cmd.Parameters.AddWithValue($"@p{i}", deptRefList[i]);
            }

            // 执行查询并处理结果
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    // 处理T2数据逻辑
                }
            }
        }
    }
}

3. 额外优化建议 #

• 若T1的数据不会频繁变动，可将deptRefList存入内存缓存（如MemoryCache），避免每次程序启动都查询Oracle；
• 为Oracle表T1的deptRef字段添加索引，提升查询效率；
• 当集合元素超过1000时，优先使用表值参数，避免触发SQL Server的IN子句长度限制。

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