Entity Framework with Oracle using odp.net not taking parameters in linq query

Question

I am using EntityFramework with Oracle using odp.net. The parameterized sql query does not work.

var orderCode = "XYZ";
var set = ctx.Database.SqlQuery<Order>(
    "Select * from dwh.Orders where OrderCode = '{0}'"
    , orderCode
);

(or)

var set1 = ctx.Database.SqlQuery<Order>(
    "Select * from dwh.Orders where OrderCode = ':param'", 
    new OracleParameter("param", orderCode)
);

Console.WriteLine(set.Count() + ", " + set1.Count()); //Gives 0, 0

However, if I have hard code the value, it works.

var set = ctx.Database.SqlQuery<Order>(
    "Select * from dwh.Orders where OrderCode = 'XYZ'",
    orderCode
);

Does any one know why? I have 150 columns in that view. Is that a problem?

UPDATE: The query with the Oracle parameter works. The problem is that I had single quotes around the :param variable.

That being said, top query with '{0}' does not work. Also, the following linq query does not work.

var set = ctx.Orders.Where(a => a.OrderCode == orderCode); // Gets zero results.

When I hardcode the value, it works and fetches the results correctly.

var set = ctx.Orders.Where(a => a.OrderCode == "XYZ"); // Gets the results correctly.

UPDATE 2: The queries work with dotconnect driver from Devart. Looks like this is an issue with odp.net.

Anyone has similar problems?

Can you explain "does not work" better? Are you getting an exception, an empty result, or the wrong results? — Tom Halladay, Jun 14 '13 at 14:10
I'm having a similar problem. I don't want to reference the ODP driver in my application (that's why I have EF), so this is irksome. For now, I'm probably going to do the anti-pattern of concatenating the SQL. The parameter is entirely internal to the application, so SQL Injection won't be a problem. Still, it leaves a bad taste in my mouth. — VeteranCoder, Aug 11 '13 at 03:22
any final solution with full source code sample working about it ? — Kiquenet, Aug 13 '13 at 06:58

score 1 · Answer 1 · edited May 23 '17 at 11:52

Not sure if you truncated your example, but if you are using multiple parameters, this might be the problem:

Parameterized query in Oracle trouble

Although I can't see anything wrong with your example, I wonder if you're being hit by the old BindByName problem. By default, ODP.NET binds parameters to the query in the order in which they are added to the collection, rather than based on their name as you'd like. Try setting BindByName to true on your OracleCommand object and see if that fixes the problem.

ViRuSTriNiTy · Answer 2 · 2018-02-12T10:40:27.980

Be careful when using strings in connection with Oracle as it has the quirk to pad strings that are defined as CHAR (read CHAR versus VARCHAR2 Semantics for details).

Lets assume you have defined OrderCode as CHAR(4) with a value of XYZ then PL/SQL blank-pads the value to the declared length which results in XYZ_ (whereas _ is the padding character here).

Also assume that the non-constant string orderCode is treated as VARCHAR2 due to this statement in the docs:

If either value in a comparison has datatype VARCHAR2, non-blank-padding semantics are used. That is, when comparing character values of unequal length, PL/SQL makes no adjustments and uses the exact lengths.

Now if you try to do a a.OrderCode == orderCode then the left side is CHARand the right side is VARCHAR2 and therefore non-blank-padding is used which results in XYZ_ = XYZ returning false.

So the solution is to use something like a.OrderCode.TrimEnd() == orderCode to make the comparison work as .TrimEnd() is translated into the PL/SQL RTRIM() function which returns a VARCHAR2.

Entity Framework with Oracle using odp.net not taking parameters in linq query

2 Answers2