Entity Frame Work - Oracle : Direct path read problem(Full scan)

When  I don't use bind variance , the response time is acceptable.

query.Where(e => e.CUST_LOT == "AAA");

 

 

But  with a bind variance , obviously it spends too much time, which occurred time out error.

 

string Lotid = "AAA" ;

query.Where(e => e.CUST_LOT == Lotid );

 

 

 

I look into the query with Query tool, the plan is OK.

But When I check the session, there is a direct path read problem(Full path read).

 

 

I found a solution in the article below. use EntityFunctions.AsNonUnicode!

string Lotid = "AAA" ;

query.Where(e => e.CUST_LOT == EntityFunctions.AsNonUnicode(Lotid));

 

https://forums.oracle.com/forums/thread.jspa?messageID=10723648

 

[ the cause of this problem]

Because .NET string is Unicode, by default NVARCHAR2 is used in parameter binding for a string variable.
In WHERE clause, when one side is N type and the other side is not N type, data conversion occurs.
That may cause full table scan instead of using index.

EntityFunctions.AsNonUnicode() tells ODP.NET to treat a string as non Unicode. In this case, VARCHAR2
is used for the parameter binding. Because both sides are not N type, no more data conversion and
no more full table scan.