我使用odp.net在Oracle中使用EntityFramework.参数化的sql查询不起作用.
var orderCode = "XYZ"; var set = ctx.Database.SqlQuery<Order>( "Select * from dwh.Orders where OrderCode = '{0}'",orderCode );
(要么)
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
但是,如果我有硬编码值,它就可以了.
var set = ctx.Database.SqlQuery<Order>( "Select * from dwh.Orders where OrderCode = 'XYZ'",orderCode );
有人知道为什么吗?我在该视图中有150列.那是问题吗?
更新:
使用Oracle参数的查询有效.问题是我在:param变量周围有单引号.
话虽如此,带有“{0}”的热门查询不起作用.此外,以下linq查询不起作用.
var set = ctx.Orders.Where(a => a.OrderCode == orderCode); // Gets zero results.
当我对值进行硬编码时,它可以正常工作并获取结果.
var set = ctx.Orders.Where(a => a.OrderCode == "XYZ"); // Gets the results correctly.
更新2:
查询使用Devart的dotconnect驱动程序.看起来这是odp.net的一个问题.
有人有类似的问题吗?
不确定是否截断了您的示例,但如果您使用多个参数,则可能是问题所在:
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.