为EF Core添加自定义SQL转换：解决ExecuteSqlInterpolated的IN子句问题

问题背景 #

使用ExecuteSqlInterpolatedAsync编写复杂查询时，IN子句传入int[]类型会抛出错误：

System.InvalidOperationException : The current provider doesn't have a store type mapping for properties of type 'int[]'

这是因为Pomelo MySQL提供程序未实现数组类型到SQL IN子句的自动转换，直接用ExecuteSqlRawAsync手动拼接SQL会导致复杂查询可读性差、维护困难，且ConfigureConventions中配置的类型映射对原生SQL参数不生效。

解决方案1：自定义DbCommandInterceptor拦截处理 #

通过拦截数据库命令，自动将数组类型参数拆分为多个单个参数，并替换SQL中的占位符，保持ExecuteSqlInterpolated的简洁语法同时实现参数化查询。

实现拦截器 #

public class ArrayParameterInterceptor : DbCommandInterceptor
{
    // 同步非查询命令处理（如INSERT/UPDATE/DELETE）
    public override InterceptionResult<int> NonQueryExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<int> result)
    {
        ProcessArrayParameters(command);
        return base.NonQueryExecuting(command, eventData, result);
    }

    // 异步非查询命令处理
    public override ValueTask<InterceptionResult<int>> NonQueryExecutingAsync(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<int> result,
        CancellationToken cancellationToken = default)
    {
        ProcessArrayParameters(command);
        return base.NonQueryExecutingAsync(command, eventData, result, cancellationToken);
    }

    // 同步查询命令处理（如SELECT）
    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result)
    {
        ProcessArrayParameters(command);
        return base.ReaderExecuting(command, eventData, result);
    }

    // 异步查询命令处理
    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = default)
    {
        ProcessArrayParameters(command);
        return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }

    private void ProcessArrayParameters(DbCommand command)
    {
        // 筛选出数组类型的参数
        var arrayParams = command.Parameters.Cast<DbParameter>()
            .Where(p => p.Value is Array array && array.Length > 0)
            .ToList();

        foreach (var param in arrayParams)
        {
            var array = (Array)param.Value;
            var newParamNames = new List<string>();

            // 为数组每个元素创建单独参数
            for (int i = 0; i < array.Length; i++)
            {
                var newParam = command.CreateParameter();
                newParam.ParameterName = $"@{param.ParameterName}_{i}";
                newParam.Value = array.GetValue(i);
                newParam.DbType = param.DbType;
                command.Parameters.Add(newParam);
                newParamNames.Add(newParam.ParameterName);
            }

            // 替换SQL中的数组参数占位符为多个参数的集合
            command.CommandText = command.CommandText.Replace(
                param.ParameterName,
                $"({string.Join(", ", newParamNames)})");

            // 移除原数组参数
            command.Parameters.Remove(param);
        }
    }
}

注册拦截器 #

在DbContext的OnConfiguring方法中添加拦截器：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseMySql(
            "your_connection_string", 
            ServerVersion.AutoDetect("your_connection_string"))
        .AddInterceptors(new ArrayParameterInterceptor());
}

使用方式 #

直接保留ExecuteSqlInterpolated的写法即可，拦截器会自动处理数组参数：

var userIds = new int[] { 1, 3, 5 };
await context.Database.ExecuteSqlInterpolatedAsync(
    $"DELETE FROM Users WHERE Id IN ({userIds})");

解决方案2：封装自定义DbContext方法 #

如果不想用拦截器，可以在DbContext中封装一个自定义方法，手动处理数组参数的转换，同时保持插值语法的便利性。

实现自定义方法 #

public async Task<int> ExecuteSqlInterpolatedWithArraysAsync(FormattableString query)
{
    var arguments = query.GetArguments();
    var sqlTemplate = query.ToString();
    var parameters = new List<MySqlParameter>();

    for (int i = 0; i < arguments.Length; i++)
    {
        var arg = arguments[i];
        if (arg is int[] intArray)
        {
            // 处理int[]类型参数，生成多个单个参数
            var paramNames = new List<string>();
            for (int j = 0; j < intArray.Length; j++)
            {
                var paramName = $"param_{i}_{j}";
                paramNames.Add($"@{paramName}");
                parameters.Add(new MySqlParameter(paramName, intArray[j]));
            }
            // 替换原占位符
            sqlTemplate = sqlTemplate.Replace($"{{{i}}}", $"({string.Join(", ", paramNames)})");
        }
        else
        {
            // 处理普通参数
            var paramName = $"param_{i}";
            sqlTemplate = sqlTemplate.Replace($"{{{i}}}", $"@{paramName}");
            parameters.Add(new MySqlParameter(paramName, arg ?? DBNull.Value));
        }
    }

    return await Database.ExecuteSqlRawAsync(sqlTemplate, parameters.ToArray());
}

使用方式 #

调用自定义方法替代原生ExecuteSqlInterpolatedAsync：

var userIds = new int[] { 2, 4, 6 };
await context.ExecuteSqlInterpolatedWithArraysAsync(
    $"UPDATE Users SET IsActive = 0 WHERE Id IN ({userIds})");

说明 #

ConfigureConventions中配置的类型映射仅针对实体模型的属性，无法作用于原生SQL的参数传递，因此之前的尝试无效。上述两种方案均通过参数化方式处理数组，避免了SQL注入风险，同时保持了查询的可读性。

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