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?

Answer 1

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.