具有多个条件的参数化查询问题

Question

我试图创建一个具有多个条件的参数化sql查询。我正在打开实体框架2.2版和MSSQL 2016上的命令，该命令使用模块System.Data.Common.DbCommand

当我改变和就在@tenantName之后到或， SQL命令可以工作。我需要它是和语句以获得正确的数字。

下面的sql命令代码不工作，但它在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));
command.CommandText = "SELECT COUNT(*) FROM dbo.IdpUserEventLog WHERE " +
"(TenantName IS NOT NULL AND 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%')) " +

Answer 1

感谢@mjwills和@derpirscher的评论，我现在通过将代码更改为以下

获得与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}%"));
command.CommandText = "SELECT COUNT(*) FROM dbo.IdpUserEventLog WHERE " +
"(TenantName = @tenantName) AND (" +
"(EventType LIKE @searchTerm) " +
"OR (AppName LIKE @searchTerm) " +
"OR (EventExtra LIKE @searchTerm) " +
"OR (EventDescription LIKE @searchTerm) " +
"OR (EventResultReport LIKE @searchTerm) " +
"OR (EventDescription LIKE @hashedSearchTerm) " +
"OR (EventExtra LIKE @hashedSearchTerm)) " +