Dapper查询报错list-expansion首项不能为null异常求助

云上老周

2026-8-28

报错场景

执行如下Dapper数据库查询代码时，触发异常：

count += await connection.ExecuteScalarAsync<int>(sqlCount, parameters);

异常基础信息：

异常类型：System.NotSupportedException
抛出位置：Dapper.SqlMapper.PackListParameters
异常消息：The first item in a list-expansion cannot be null

对应业务实现代码如下：

var sqlCount = @"
SELECT COUNT(DISTINCT b.Id)
FROM DNs as b
INNER JOIN VProducts AS v
    ON v.Id = b.VId
INNER JOIN Data AS d
    ON b.VId = d.VId
        AND b.Id IN (d.ME)
WHERE v.Id = @Id
    AND v.xyz IN @xyzs
    AND (@Number IS NULL OR b.Number = @Number)
    AND (@Type IS NULL OR b.Type = @Type)
    AND (@StartDate IS NULL OR b.DeliveryDate >= @StartDate)
    AND (@EndDate IS NULL OR b.DeliveryDate <= @EndDate)
    AND (DeliveryDate IS NOT NULL OR @Number IS NOT NULL);";
int count = 0;
int result = 0;
using var connection = _dbHelper.GetOpenConnection();
for (int i = 0; i <= xyzs.Count / 2000; i++)
{
    var parameters = new
    {
        @Id = id,
                @xyz = xyzs.Skip(i).Take(2000),
                @Number = Number,
                @Type = Type,
                @StartDate = startDate?.ToUniversalTime(),
                @EndDate = endDate?.ToUniversalTime()
        };
    count += await connection.ExecuteScalarAsync<int>(sqlCount, parameters);
}

根因分析

该异常是Dapper处理IN子句列表参数时的参数校验报错，触发原因有两处：

参数名拼写不匹配：SQL语句中IN子句对应的参数名为@xyzs（复数），但匿名参数对象中传递的列表属性名为@xyz（单数），Dapper解析列表参数时找不到对应的值，拿到null后直接触发首项不能为null的校验异常，这是本次报错的直接原因。
列表分片逻辑存在缺陷：即使修正参数名，现有代码的分片逻辑也存在问题：
- Skip方法入参直接传循环变量i，没有乘以每批条数2000，无法实现每2000条分片查询的预期，会导致大量重复查询
- 没有判断分片后的列表是否为空，当循环到超出集合总长度的轮次时，会传递空列表给Dapper，同样会触发列表展开相关的异常
- 没有对xyzs集合本身做null/空值判断，集合为空时也会触发同类报错

修复方案

按以下步骤修改代码即可解决问题：

统一参数名，保证匿名对象中的列表参数名和SQL中IN子句的参数名完全一致
修正分片逻辑，Skip方法入参改为i * 2000，保证每批跳过已处理的数据
增加边界校验：进入循环前判断xyzs是否为空集合，每批分片后判断当前批次列表长度，为空时直接终止循环，不要给Dapper传递null或空的列表参数

修复后的完整代码：

var sqlCount = @"
SELECT COUNT(DISTINCT b.Id)
FROM DNs as b
INNER JOIN VProducts AS v
    ON v.Id = b.VId
INNER JOIN Data AS d
    ON b.VId = d.VId
        AND b.Id IN (d.ME)
WHERE v.Id = @Id
    AND v.xyz IN @xyzs
    AND (@Number IS NULL OR b.Number = @Number)
    AND (@Type IS NULL OR b.Type = @Type)
    AND (@StartDate IS NULL OR b.DeliveryDate >= @StartDate)
    AND (@EndDate IS NULL OR b.DeliveryDate <= @EndDate)
    AND (DeliveryDate IS NOT NULL OR @Number IS NOT NULL);";
int count = 0;
// 提前处理空集合边界
if (xyzs == null || xyzs.Count == 0)
{
    return 0;
}
using var connection = _dbHelper.GetOpenConnection();
for (int i = 0; i <= xyzs.Count / 2000; i++)
{
    // 修正分片步长
    var currentBatch = xyzs.Skip(i * 2000).Take(2000).ToList();
    // 当前批次无数据直接终止循环
    if (currentBatch.Count == 0) break;
    var parameters = new
    {
        Id = id,
        // 参数名和SQL保持一致
        xyzs = currentBatch,
        Number = Number,
        Type = Type,
        StartDate = startDate?.ToUniversalTime(),
        EndDate = endDate?.ToUniversalTime()
    };
    count += await connection.ExecuteScalarAsync<int>(sqlCount, parameters);
}

补充说明：Dapper的列表展开机制要求IN子句对应的参数必须是非空集合，且集合首元素不能为null。如果业务场景确实需要匹配空集合的逻辑，不要直接传空列表，要提前拼接恒假条件（如1=0）返回，避免触发参数解析异常。

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