CODEWITHSUNDEEP
linqentity-frameworkodp.net
Jonna
Jonna

Reputation: 1645

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

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?

Upvotes: 9

Views: 3127

Answers (2)

ViRuSTriNiTy
ViRuSTriNiTy

Reputation: 5155

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.

Upvotes: 0

Tom Halladay
Tom Halladay

Reputation: 5761

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.

Upvotes: 1

Related Questions