UseGenerateCommandParameterWithLambda(true)时，使用Where条件+子查询有一定概率出现异常：“必须声明标量变量 "@exp_0"

[skiller008]

问题：开启参数化查询，使用Where条件+子查询有一定概率出现异常：“必须声明标量变量 "@exp_0"

开发框架：.NET 5.0

FreeSql版本：3.2.662

数据库版本：Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) on Windows 10 Pro 10.0 (Build 19042: )

重现步骤

1.开启参数化查询

UseGenerateCommandParameterWithLambda(true);

2.同时包含Where以及子查询：

var name = "123";
var result = await freeSql.Select<Items>()
    .Where(t => t.Name == name
    && freeSql.Select<Items>().Any(t2 => t2.CenterItemID == t.CenterItemID)).ToListAsync();

生成的SQL语句：

SELECT a.[ItemID], a.[CenterItemID], a.[Name]
FROM [Items] a
WHERE (a.[Name] = @exp_0 AND exists(SELECT TOP 1 1
    FROM [Items] t2
    WHERE (t2.[CenterItemID] = a.[CenterItemID])))

异常信息：

System.Exception: 必须声明标量变量 "@exp_0"。
“)”附近有语法错误。
 ---> Microsoft.Data.SqlClient.SqlException (0x80131904): 必须声明标量变量 "@exp_0"。
“)”附近有语法错误。
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__188_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
   at FreeSql.Internal.CommonProvider.AdoProvider.ExecuteReaderMultipleAsync(Int32 multipleResult, DbConnection connection, DbTransaction transaction, Func`3 fetchHandler, Action`2 schemaHandler, CommandType cmdType, String cmdText, Int32 cmdTimeout, DbParameter[] cmdParms, CancellationToken cancellationToken)
ClientConnectionId:6f63e54f-5194-46f7-92fa-e996c9eddab4
Error Number:137,State:2,Class:15
   --- End of inner exception stack trace ---
   at FreeSql.Internal.CommonProvider.AdoProvider.LoggerException(IObjectPool`1 pool, PrepareCommandResult pc, Exception ex, DateTime dt, StringBuilder logtxt, Boolean isThrowException)
   at FreeSql.Internal.CommonProvider.AdoProvider.ExecuteReaderMultipleAsync(Int32 multipleResult, DbConnection connection, DbTransaction transaction, Func`3 fetchHandler, Action`2 schemaHandler, CommandType cmdType, String cmdText, Int32 cmdTimeout, DbParameter[] cmdParms, CancellationToken cancellationToken)
   at FreeSql.Internal.CommonProvider.Select0Provider`2.ToListAfPrivateAsync(String sql, GetAllFieldExpressionTreeInfo af, ReadAnonymousTypeOtherInfo[] otherData, CancellationToken cancellationToken)
   at FreeSqlTraining.Program.Test() in E:\Projects\Demo\Training\FreeSqlTraining\Program.cs:line 129
   at FreeSqlTraining.Program.<>c__DisplayClass3_0.<<Main>b__0>d.MoveNext() in E:\Projects\Demo\Training\FreeSqlTraining\Program.cs:line 44

异常出现概率不定，但在有一定并发量时，或使用比较复杂语句时出现概率较高

PS:可提供重现demo

[2881099]

类似这样并发测试吗，没有出现上述报错。

static void TestExp(IFreeSql fsql)
{
    var tasks = new List<Task>();

    for (var a = 0; a < 100; a++)
    {
        var task = Task.Run(async () =>
        {
            var name = "123";
            var result = await fsql.Select<Rsbasedoc2>()
                .Where(t => t.Name == name
                    && fsql.Select<Rsbasedoc2>().Any(t2 => t2.Id == t.Id)).ToListAsync();
        });
        tasks.Add(task);
    }
    Task.WaitAll(tasks.ToArray());
}

[2881099]

谢谢反馈描述，找到问题原因了：

1、子查询时，也主查询使用了同一个 List<DbParameter>
2、~Select0Provider() 执行了 dbParams.Clear，并发的时候容易出现

[2881099]

v3.2.664 重要 bug

• 修复 UseGenerateCommandParameterWithLambda 子查询并发 bug；UseGenerateCommandParameterWithLambda(true)时，使用Where条件+子查询有一定概率出现异常：“必须声明标量变量 "@exp_0" #1155 （重要）
• 修复 pgsql Dto 映射使用常量 false 转换失败；
• 修复 IIF 三元表达式树解析 bool HasValue 问题；
• 修复 MySqlConnector BulkCopy 映射顺序问题；
• 优化 XML 注释读取支持 interface；
• support provider、Extensions Exceptions 多语言

[2881099]

WithParameters 参数化共享

开启参数化查询功能后，使用 WithParameters 共享参数化，避免产生相同的参数名称：

var dbpars = new List<DbParameter>();

var id1 = 1;
var id2 = 2;
var sql = fsql.Select<User1>()
    .WithParameters(dbpars)
    .Where(a => a.Id == id1)

    .FromQuery(
        fsql.Select<User1>()
            .WithParameters(dbpars)
            .Where(a => a.Id == id2)
    )
    .InnerJoin((a,b) => a.Id == b.Id)
    .ToSql();

SELECT a."Id", a."GroupId", a."Username" 
FROM (
    SELECT a."Id", a."GroupId", a."Username" 
    FROM "User1" a 
    WHERE (a."Id" = @exp_0) 
) a
INNER JOIN ( 
    SELECT a."Id", a."GroupId", a."Username" 
    FROM "User1" a 
    WHERE (a."Id" = @exp_1) ) b ON b."Id" = a."Id"