The first item in a list-expansion cannot be null

Question

I am getting the error for The first item in a list-expansion cannot be null in the below mentioned code statement-

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

Below are the error details- System.NotSupportedException at Dapper.SqlMapper.PackListParameters outerMessage - The first item in a list-expansion cannot be null

Here is the complete code-

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);
} 

I am not able to find out much details about this kind of error. Any help will be appreciated.

Answer 1

This is caused by a null value in the @xyzs parameter. When Dapper attempts to expand that to a sql "in" clause it fails.

Try removing null from xyzs collection when setting your parameters:

@xyzs = xyzs.Skip(i).Take(2000).Where(x => x != null),

If you actually want records where v.xyz is null you will want to add that as another clause as well.