I'm working on a legacy system (15+ years old) which is still in use but has a very large code base. We have recently discovered that a client using Oracle is having an issue with case sensitivity in searching text. I'm unable to make large changes to the database or code but I still have to fulfil my brief. For some reason, the DB creator put double quotes around column and table names in their scripts which forces Oracle to become case sensitive. (Oracle DB quote column names) meaning that any query on these tables and columns is case sensitive. I'm required to make the search case insensitive but I'm having difficulty in getting Linq-To-Entities to do this.
So far, I've tried to create a culture and then check the IndexOf while ignoring case:
orPredicate = orPredicate.Or(q => culture.CompareInfo.IndexOf(q.Data, tempValue, CompareOptions.IgnoreCase) >= 0);
The system responds with:
"LINQ to Entities does not recognize the method 'Int32 IndexOf(System.String, System.String, System.Globalization.CompareOptions)' method, and this method cannot be translated into a store expression."
I have also tried ToLower():
orPredicate = orPredicate.Or(q => q.Data.ToLower().Contains(tempValue.ToLower()));
The result of this is:
OracleException: ORA-00904: "TOLOWER": invalid identifier
I tried several extension methods that were suggested in some other questions on Stack Overflow but they are all variations of the IndexOf method.
The most frustrating part is that if you put the exact text in, it works. We use the system with SQL Server as well with no problems regarding case sensitivity so it's our solitary Oracle-based client who is having this issue. I can't make wholesale changes to either the database or the search function developed so I need to find a solution that will make Oracle compare without being concerned about case. Converting and comparing (setting to upper or lower) is fine as long as they match correctly but I'm unsure how to get this working in this case.
