How can I use SQL Parameters in a CONTAINS clause?

Question

I am looking to do something like:

select * from MyValues
where CONTAINS(MyValues.Value, ' @p0 OR @p1 OR @p2 or @p3 ')

I issue the query through EF's SqlQuery() method like:

query = context.Database.SqlQuery<MyResult>(@"select * from MyValues
    where CONTAINS(MyValues.Value, '@p0 OR @p1 OR @p2 OR @p3')",
    new SqlParameter("@p0", "Cat"),
    new SqlParameter("@p1", "Green"),
    new SqlParameter("@p2", "Red"),
    new SqlParameter("@p3", "Dog"));

The command goes through fine, no exceptions, but I do not receive any results. When I manually use the strings in place of the parameters, I get the expected results. I've tried various forms and combinations of quotation marks but to no avail.

Are SQL Parameters allowed within a CONTAINS expression?

Thanks!

score 1 · Accepted Answer · answered Mar 28 '16 at 17:06

Because the CONTAINS function uses a single string, I don't believe that you can use parameters as you have them. You can try building up the full string and passing that in as a single parameter however. As long as the string that you build is then passed in as a parameter I believe that you'll avoid any issues with possible SQL injection.

score 0 · Answer 2 · answered Mar 28 '16 at 20:49

May be you can try this.

query = context.Database.SqlQuery<MyResult>(@"select * from MyValues)

Once you get your list.filter it using below query. First put all your parameters in a string array.

string[] paramValues= [p1,p2,p3,p4] 
var results=query.where(r=> paramValues.Contains(r.Value));

Note:If your resultset is huge.it's not a good idea to return all results to front end and do filter.in most cases you have other conditions to filter.

How can I use SQL Parameters in a CONTAINS clause?

2 Answers2