CODEWITHSUNDEEP
c#sqlentity-frameworkprepared-statement
Cody
Cody

Reputation: 8944

Entity Framework SQL Query not Working with SQL Parameters

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

Upvotes: 2

Views: 8002

Answers (5)

Guillermo Espert
Guillermo Espert

Reputation: 1310

I encountered a similar issue, my parameters wasn't replaced. In general, all documentation from Microsoft is related to SQL Server.

In my case, I was executing the query against an Oracle database, so @p0 doesn't work for Oracle. You must use :p0 notation instead.

Upvotes: 0

Matija Grcic
Matija Grcic

Reputation: 13381

You can use two conventions for passing the parameters, named or index based.

Named parameters convention

string sql = "INSERT INTO SOMETABLE(Column1,Column2,Column3) 
              VALUES(@namedParamOne,@nameParamTwo,@namedParamThree)";

//index based parameter naming convention
List<SqlParameter> parameters = new List<SqlParameter>(){
    new SqlParameter("@namedParamOne","One"),
    new SqlParameter("@nameParamTwo","Two"),
    new SqlParameter("@namedParamThree","Three")
}.ToArray();

Index based parameter naming convention

string sql = "INSERT INTO SOMETABLE(Column1,Column2,Column3) 
              VALUES(@P0,@P1,@P2)";

List<object> parameters = new List<object>(){
    new "Value 1",
    new "Value 2",
    new "Value 3"
}.ToArray();

Execute the command or issue a query

int result = db.Database.ExecuteSqlCommand(sql, parameters);

To return entities you would use

    string sql = "SELECT * from YourTable
                           where SomeValue=@p0 and SomeOtherValue=@p1";
    List<object> parameters = new List<object>(){
        new "Value 1",
        new "Value 2"
    }.ToArray();

DbSqlQuery<SomeEntity> data = db.SomeEntity.SqlQuery(sql, parameters);

To return custom types you issue an query on Database, not on your DbSet.

DbRawSqlQuery<YourViewModel> data = db.Database.SqlQuery<YourViewModel>(sql, parameters);

Upvotes: 0

Sam
Sam

Reputation: 789

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

Upvotes: 2

Aaron D
Aaron D

Reputation: 5876

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 + "%")};

Upvotes: 8

mybirthname
mybirthname

Reputation: 18127

You are probably ORDER BY column name, why you are use parameter for this. Probably the problem comes from p0 parameter. What is the value of orderBy is it valid column ?!

Also you can use:

string p = @"

";

in this case you don't need to add so much '+'. Also add skip and take as parameters

Upvotes: 1

Related Questions