Parameterizing a raw Oracle SQL query in Entity Framework

Question

I'm trying to parameterize a raw SQL query for an Oracle synonym (non-entity) in EF 4 and I am having some problems. Currently I am doing something like the code below, based on some examples that I saw:

 string term="foo";
 OracleParameter p = new OracleParameter("@param1", term);
 object[] parameters = new object[] { p };
 var model = db.Database.SqlQuery<ProjectTask>("SELECT * FROM (SELECT * FROM web_project_task_vw WHERE project_num like '%@param1%') WHERE rownum<=100", parameters).ToList();

Running this doesn't return any results. If I replace the parameter with something like

"SELECT * FROM web_project_task_vw WHERE project_num like '%"+term+"%'"

it returns the results I expect, but this is obviously a SQL injection risk.

Can anyone point me in the right direction for how parameters are supposed to work in EF 4 for an Oracle DB?

Thanks.

Answer 1

First, like Mohammed wrote, you need to prefix the parameter with ':', but not as you define it, just in the query. Second, you are currently searching not for the value of the parameter but rather strings that contains the string @param1. So surround the value of the parameter with % and you should get a result.

So it should look something like this:

string term="foo";
 OracleParameter p = new OracleParameter("param1", term);
 object[] parameters = new object[] { p };
 var model = db.Database.SqlQuery<ProjectTask>("SELECT * FROM (SELECT * FROM web_project_task_vw WHERE project_num like '%'||:param1||'%') WHERE rownum<=100", parameters).ToList();

Answer 2

Your p might have an incorrect parameter name; the name should be param1, not @param1. Your query is also incorrect; replace '%@param1%' with '%:param1%'.