Entity Framework SQL Query not Working with SQL Parameters

Vote count:

0

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();

asked 34 secs ago

Cody

1,795

Entity Framework SQL Query not Working with SQL Parameters