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

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