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.

I thought Oracle parameter names started with : instead of @? — RogerN
– RogerN, Commented Mar 19, 2013 at 20:15

Moinois · Accepted Answer · 2013-12-05 14:54:50Z

9

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

answered Dec 5, 2013 at 14:54

Moinois

1211 silver badge2 bronze badges

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

1 Comment

Gerrie Pretorius Over a year ago

Works in EF6 as well.

Mohammed Hossain · Accepted Answer · 2013-03-19 20:19:40Z

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%'.

edited Mar 19, 2013 at 20:19

answered Mar 19, 2013 at 20:14

Mohammed Hossain

1,32910 silver badges19 bronze badges

Collectives™ on Stack Overflow

Parameterizing a raw Oracle SQL query in Entity Framework

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related