Using multiple like clauses with dapper for paging in a query with SQL Server 2008 R2

Question

I use Dapper to do a paging query in a large table. The function I use is this:

public List<WarehouseLine> GetPagedWarehouseLines(int pageNumber, int rowsPerPage, string criteria) //todo
{
    var query = @"SELECT *
                  FROM 
                      (SELECT 
                           *, 
                           ROW_NUMBER() OVER (ORDER BY ProductDescr) AS RowNum 
                       FROM 
                           WarehouseLine) AS whl 
                  WHERE 
                      whl.RowNum BETWEEN ((@PageNumber - 1) * @RowsPerPage) + 1 AND @RowsPerPage * (@PageNumber) 
                      AND (@Criteria) 
                  ORDER BY ProductDescr";

    return _db.Query<WarehouseLine>(query, new { PageNumber = pageNumber, RowsPerPage = rowsPerPage, Criteria = criteria }).ToList();
}

The criteria variable is a string with the following value

(Productdescr like '%%') OR (PartNumber like '%%') OR (SerialNumber like '%%') OR(Manufacturer like '%%') OR (SpecialInstructions like '%%') OR (UDF3 like '%%') OR (producttags like '%%') OR (NotesPerPart like '%%') 

Parenthesis aside, the query runs fine in SQL Server Management Studio. But during debugging, I get the following error:

An expression of non-boolean type specified in a context where a condition is expected, near 'ORDER'.

I use the latest stable version of Dapper.

Answer 1

'Criteria' variable is a part of SQL expression, it cannot be treated as SQL parameter.

var query = @"SELECT *
  FROM (
  SELECT *, 
  ROW_NUMBER() OVER (ORDER BY ProductDescr) AS RowNum 
  FROM WarehouseLine) AS whl 
  WHERE whl.RowNum BETWEEN ((@PageNumber-1)*@RowsPerPage)+1 
  AND @RowsPerPage*(@PageNumber) 
  AND " 
  + Criteria + 
  @"ORDER BY ProductDescr";