Parameterizing a raw Oracle SQL query in Entity Framework

c#, entity-framework-4, oracle

Solution

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

Problem

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.

Original source