子查询的参数化异常 ， 必须声明标量变量

[zhujiancc]

var query = context.Select<Users>().As("u")
    .Where(s => s.Status > 0)
    .Where("u.Tel like @Tel", new { Tel = "%188%" })
    .WithTempQuery(s => new { UserId = s.UserId });
//执行正常
var users = query.ToList();
//执行正常
var joinMap = context.Select<Users, UserRoleMap>().As("u", "m")
    .Where(s => s.t1.Status > 0)
    .Where("u.Tel like @Tel", new { Tel = "%188%" })
    .InnerJoin(s => s.t1.UserId == s.t2.UserId)
    .ToList();
//报错  必须声明标量变量 "@Tel"
var anyMap = context.Select<UserRoleMap>()
    .Where(s => query.Any(u => u.UserId == s.UserId))
    .ToList();

简单查询和join 没有问题 ， 涉及到嵌套， 或者子查询【fromquery】 这种 都会报 ”必须声明标量变量“

异常语句生成的SQL

SELECT a.[UserId], a.[RoleId], a.[CompanyId], a.[AddTime] 
FROM [UserRoleMap] a With(NoLock) 
WHERE (exists(SELECT TOP 1 1 
    FROM ( 
        SELECT u.[UserId] 
        FROM [Users] u With(NoLock) 
        WHERE (u.[Status] > 0) AND (u.Tel like @Tel) ) u 
    WHERE (u.[UserId] = a.[UserId])))

数据库版本

sqlserver

安装的Nuget包

3.2.813

.net framework/. net core？ 及具体版本

net 6

[2881099]

WithParameter 解决这个问题，在 Union 文档有介绍

[zhujiancc]

弄混了 ， 原先 把 WithParameters写到 query 了

var dbpars = new List<DbParameter>()
{
    new SqlParameter { ParameterName = "Tel", Value = "%188%" }
};
var query = context.Select<Users>().As("u")
    .WithParameters(dbpars)
    .Where(s => s.Status > 0)
    .Where("u.Tel like @Tel")
    .WithTempQuery(s => new { UserId = s.UserId });
//报错  必须声明标量变量 "@Tel"
var anyMap = context.Select<UserRoleMap>()
    .Where(s => query.Any(u => u.UserId == s.UserId))
    .ToList();

放在 anyMap 这边就可以了 ，谢了

var dbpars = new List<DbParameter>()
{
    new SqlParameter { ParameterName = "Tel", Value = "%188%" }
};
var query = context.Select<Users>().As("u")
    .Where(s => s.Status > 0)
    .Where("u.Tel like @Tel")
    .WithTempQuery(s => new { UserId = s.UserId });
//执行成功
var anyMap = context.Select<UserRoleMap>()
    .Where(s => query.Any(u => u.UserId == s.UserId))
    .WithParameters(dbpars)
    .ToList();

[zhujiancc]

还有一个问题 ， 如果开启了自动参数化 ， UseGenerateCommandParameterWithLambda(true) 下面的代码会报错 , 必须声明标量变量 "@exp_0"。

var dbpars = new List<DbParameter>()
{
    new SqlParameter { ParameterName = "Tel", Value = "%188%" },
    new SqlParameter { ParameterName = "UserIds", Value = new[] { 1, 2, 3 }  }
};
var request = new
{
    NickName = "hello",
    Status = 1,
};
var data = context.Select<Users>().As("u")
    .Where(s => s.Status ==request.Status)
    .Where(s => s.NickName==request.NickName)
    .Where("u.UserId in @UserIds")
    .WithParameters(dbpars)
    .ToList();

[2881099]

WithParameter位置调一下，放前面