Entity Framework SQL Query not Working with SQL Parameters

Question

I have a raw SQL Query that I am trying to use with SqlParameters. When I create the query unsafely (using string concatenation), it works fine...I get the results. In this case, I get 10.

When I try using SQL Parameters I get zero records. I've tried creating parameters with and without the @, I've tried adding them in individually in the call to Query, and I've tried Object Parameters instead of Sql Parameters. I've even tried using only one....and no matter what I do I can't seem to get results using SQL Parameters, only string concatenation.

How does an entity framework SQL Query work with SQL Parameters, and why is the query I am using not working?

Example Code:

string query = "WITH OrderedItems AS " +
               "(" +
               "SELECT *, " +
               "ROW_NUMBER() OVER (ORDER BY @p0) AS 'RowNumber' " +
               "FROM ITEMS" +
               " WHERE item_name LIKE '%@p1%'" +
               ")" +
               "SELECT * FROM OrderedItems" +
               " WHERE RowNumber BETWEEN " + (skip + 1) + " AND " + (skip + take);

// I have tried using @p0 and @p1 as the parameter names here
var parameters = new object[] {new SqlParameter("p0", orderBy), new SqlParameter("p1", model.item_name)};

var context = new DbEntities();
// I have tried only using one and passing it too
List<item> result = context.items.SqlQuery(query, parameters).ToList();

Answer 1

I think the problem is the way you are trying to use the LIKE clause with p1:

The parameterization will take care of quoting the values. You shouldn't provide quotes around the parameter.

Try modifying it to something like:

string query = "WITH OrderedItems AS " +
               "(" +
               "SELECT *, " +
               "ROW_NUMBER() OVER (ORDER BY @p0) AS 'RowNumber' " +
               "FROM ITEMS" +
               " WHERE item_name LIKE @p1" +
               ")" +
               "SELECT * FROM OrderedItems" +
               " WHERE RowNumber BETWEEN " + (skip + 1) + " AND " + (skip + take);

var parameters = new object[] {new SqlParameter("p0", orderBy), new SqlParameter("p1", "%" + model.item_name + "%")};

Answer 2

Remove the quotes and the % from '%@p1%'. Then add the % to the string you are passing as parameter. ... new SqlParameter("p1", "%" + model.item_name + "%") example :

select * from mytable where column1 like @p1

filter1 = "%test%";
var parameters = new object[] {new SqlParameter("p1", filter1)}

and your code will be :

string query = "WITH OrderedItems AS " +
               "(" +
               "SELECT *, " +
               "ROW_NUMBER() OVER (ORDER BY @p0) AS 'RowNumber' " +
               "FROM ITEMS" +
               " WHERE item_name LIKE @p1" +
               ")" +
               "SELECT * FROM OrderedItems" +
               " WHERE RowNumber BETWEEN " + (skip + 1) + " AND " + (skip + take);

var parameters = new object[] {new SqlParameter("p0", orderBy), new SqlParameter("p1", "%" + model.item_name + "%")};
var context = new DbEntities();
List<item> result = context.items.SqlQuery(query, parameters).ToList();