Search code examples
vb.netoracleentity-frameworkodp.netodp.net-managed

Case insensitive search using ALTER SESSION on ODP.NET/EF connections?

I've got some EF/LINQ statements that I need to be case-insensitive text searches, but our oracle database is case sensitive. How can I execute the necessary ALTER SESSION statement at the connection/command level so that it will affect the subsequent same-context calls?

Command I think I need to run (OTN Thread)

ALTER SESSION SET NLS_SORT=BINARY_CI

I'm aware of both Database.ExecuteSqlCommand and Database.Connection.CreateCommand as methods, but I can't figure out the 'when'. If I manually try to do this to the context after creation but before the LINQ, I have to manually open & close the connection, and then it seems to be a different transaction as the LINQ, and doesn't seem to be applying.

Solution

  • Technically this is not a solution to your question of how to inject ALTER SESSION SET NLS_SORT=BINARY_CI into a query, but it may help you with your insensitive case search, just use .ToLower() .