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

I have tried that...it doesn't make a difference - I still don't get any results - I said that in the post. — Cody
– Cody, Commented Aug 19, 2014 at 18:33

Aaron D · Accepted Answer · 2014-08-19 18:52:16Z

8

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

answered Aug 19, 2014 at 18:52

Aaron D

5,8971 gold badge39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cody Over a year ago

You're my hero. That was it - the quotes I was adding around the parameter. This too is a much cleaner way of doing it.

StriplingWarrior · Accepted Answer · 2014-08-19 18:59:03Z

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

edited Aug 19, 2014 at 18:59

StriplingWarrior

158k29 gold badges261 silver badges326 bronze badges

answered Aug 19, 2014 at 18:55

Sam

7891 gold badge4 silver badges10 bronze badges

3 Comments

StriplingWarrior Over a year ago

+1. Alternatively, use " WHERE item_name LIKE '%' + @p1 + '%'"

Sam Over a year ago

Oups....i was busy writing the answer and couldn't noticed that Aaron D answered it first and using the same method

Sam Over a year ago

yes definitely StriplingWarrior but i prefer keeping it in the c# code which will give you the option to use the wildcard or not dynamically

mybirthname · Accepted Answer · 2014-08-19 18:45:55Z

1

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

answered Aug 19, 2014 at 18:45

mybirthname

18.2k3 gold badges35 silver badges59 bronze badges

2 Comments

Cody Over a year ago

Even if I add the column name directly to the query and only use the one parameter, I still get no results. Good tip on using the @

mybirthname Over a year ago

In this case the problem is in the p1 parameter. I think you can try the "Aaron D" solution. He wrote an answer.

Matija Grcic · Accepted Answer · 2014-08-19 19:00:17Z

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

Guillermo Espert · Accepted Answer · 2024-02-22 15:22:02Z

0

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.

answered Feb 22, 2024 at 15:22

Guillermo Espert

1,3401 gold badge9 silver badges13 bronze badges

Collectives™ on Stack Overflow

Entity Framework SQL Query not Working with SQL Parameters

5 Answers 5

1 Comment

3 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

3 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related