LINQ to SQL for Oracle and bind variable naming

[sclegg]

Hi all,

In C#, when using LINQ to SQL for Oracle database (v5.25.42.0) with ASP.NET MVC 3.5, the bind variable changed on every execution. On the first execution of the LINQ to SQL query with the where clause, the DBMonitor shows the bind variable of :p__linq__1. On the second execution of the same exact LINQ to SQL query during the same session, the DB Monitor shows the bind variable of :p__linq__2. This name should stay the same as :p__linq__1 not :p__linq__2. Otherwise, the Oracle database treats the two sqls as two separate executions. This is a significant performance issue. Is there a way to control the naming of the bind variable? Thanks in advance!

see this article for details: http://www.oracle-base.com/articles/mis ... iables.php

[AndreyR]

Thank you for the inquiry, we are investigating the issue.
I will let you know about the results of our investigation.

[sclegg]

Thank you for checking into this issue. I believe the parsing issue is on the naming for the bind variable. For a web application, I'm using a connection pool with of course closing the connection when completing a transaction or query. Since this is a web based application, I'm not cannot just close the web based application. The following is a sample of a source code where a button on a web page calls this method in C#:

public List fetchQryLINQ(DateTime param1
, DateTime param2)
{
DataSourceModel1.XEntities db = null;

try
{

string sConnStrg = ConfigurationManager.ConnectionStrings["MyConnString1"].ConnectionString;
db = new DataSourceModel1.XEntities(sConnStrg);

var myList = (from a in db.XTxns
where a.EventTimestamp >= param1
&& a.EventTimestamp = :p__linq__1) AND ("Extent1".EVENT_TIMESTAMP = :p__linq__3) AND ("Extent1".EVENT_TIMESTAMP <= :p__linq__4)

-- -1 row(s) affected.

The dbMonitor shows the connection as closed after each above query transaction.

The problem is the unique bind variable. Oracle cannot reused the first query because the variable is different. Do you see the issue?

[AndreyR]

Thank you for the clarification.
We will let you know as soon as the results are available.

[AndreyR]

These queries are generated by Entity Framework and we don't have technical possibility to influence this process.
I recommend you to create CompiledQuery and call it in case if you need better performance.
More information about compiled queries is available here:
http://msdn.microsoft.com/en-us/library ... query.aspx