EF2.2搭配MSSQL2016多条件参数化SQL查询AND语句不生效问题

你代码中LIKE条件的写法错误，将参数名直接包裹在单引号内时，SQL Server会将@searchTerm、@hashedSearchTerm识别为普通字符串，而不是你预先定义的参数变量，导致匹配逻辑失效。当你把第一个AND改成OR之后，查询条件放宽返回了结果，才会让你误以为OR能正常运行，本质上两种写法的参数都没有被正确解析。

有两种常用的修复方式，任选其一即可：

方案1：将通配符拼接至参数值中 #

修改参数赋值逻辑，把百分号通配符直接加到参数值里，SQL语句中不需要再写通配符和额外的单引号：

var command = _context.Database.GetDbConnection().CreateCommand();
command.Transaction = _context.Database.CurrentTransaction.GetDbTransaction();

// 参数赋值部分调整，把通配符拼入参数值
command.Parameters.Add(new SqlParameter("@tenantName", tenantName));
command.Parameters.Add(new SqlParameter("@searchTerm", $"%{searchTerm}%"));
command.Parameters.Add(new SqlParameter("@hashedSearchTerm", $"%{hashedSearchTerm}%"));

// CommandText的LIKE部分去掉单引号和内置的百分号
command.CommandText = "SELECT COUNT(*) FROM dbo.IdpUserEventLog WHERE " +
"(TenantName = @tenantName) AND (" +
"(EventType IS NOT NULL AND EventType LIKE @searchTerm) " +
"OR (AppName IS NOT NULL AND AppName LIKE @searchTerm) " +
"OR (EventExtra IS NOT NULL AND EventExtra LIKE @searchTerm) " +
"OR (EventDescription IS NOT NULL AND EventDescription LIKE @searchTerm) " +
"OR (EventResultReport IS NOT NULL AND EventResultReport LIKE @searchTerm) " +
"OR (EventDescription IS NOT NULL AND EventDescription LIKE @hashedSearchTerm) " +
"OR (EventExtra IS NOT NULL AND EventExtra LIKE @hashedSearchTerm)) ";

补充：TenantName = @tenantName本身就会排除TenantName为NULL的情况，原语句中的TenantName IS NOT NULL判断可以省略，不影响逻辑。

方案2：在SQL中用CONCAT拼接通配符 #

如果不想修改参数赋值逻辑，可以直接在SQL语句中用CONCAT函数拼接通配符和参数：

var command = _context.Database.GetDbConnection().CreateCommand();
command.Transaction = _context.Database.CurrentTransaction.GetDbTransaction();

command.Parameters.Add(new SqlParameter("@tenantName", tenantName));
command.Parameters.Add(new SqlParameter("@searchTerm", searchTerm));
command.Parameters.Add(new SqlParameter("@hashedSearchTerm", hashedSearchTerm));

// 通过CONCAT拼接通配符和参数，保证参数能被正确识别
command.CommandText = "SELECT COUNT(*) FROM dbo.IdpUserEventLog WHERE " +
"(TenantName = @tenantName) AND (" +
"(EventType IS NOT NULL AND EventType LIKE CONCAT('%', @searchTerm, '%')) " +
"OR (AppName IS NOT NULL AND AppName LIKE CONCAT('%', @searchTerm, '%')) " +
"OR (EventExtra IS NOT NULL AND EventExtra LIKE CONCAT('%', @searchTerm, '%')) " +
"OR (EventDescription IS NOT NULL AND EventDescription LIKE CONCAT('%', @searchTerm, '%')) " +
"OR (EventResultReport IS NOT NULL AND EventResultReport LIKE CONCAT('%', @searchTerm, '%')) " +
"OR (EventDescription IS NOT NULL AND EventDescription LIKE CONCAT('%', @hashedSearchTerm, '%')) " +
"OR (EventExtra IS NOT NULL AND EventExtra LIKE CONCAT('%', @hashedSearchTerm, '%')) ";

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