EF4 Linq Oracle11g making queries none case-sensit

问题:

We are moving to EF4 & Linq as our db interface to a Oracle 11g db. The database is setup as case sensitive, but we need to search as case insensitive. In oracle using "UPPER" in the query is potentially very expensive. I have looked at the following options with the indicated results:

ChangeEntities.TABLE.Where(x => x.FIELD.Equals(VARIABLE))

generates SQL Where clause:

WHERE TABLE.FIELD = VARIABLE
---------------------
ChangeEntities.TABLE.Where(x => x.FIELD.Equals(VARIABLE.ToUpper()))

generates SQL Where clause:

WHERE TABLE.FIELD = (UPPER(VARIABLE))
---------------------
ChangeEntities.TABLE.Where(x => x.FIELD.ToUpper().Equals(VARIABLE.ToUpper()));

generates SQL Where clause:

WHERE (UPPER(TABLE.FIELD)) = (UPPER(VARIABLE))
---------------------
ChangeEntities.TABLE.Where(x => x.FIELD.Equals(VARIABLE, StringComparison.CurrentCultureIgnoreCase))

generates SQL Where clause:

WHERE TABLE.FIELD = VARIABLE
-----------------------

The results kinda speak for themselves, except that the last example could actually miss records.

Does anyone have any other thoughts on techniques?

Thanks, Sammer

回答1:

You need to either change the column collation in DB metadata or specify it in the query. The only way to specify it in a query in EF is to use ESQL or Query Builder methods. A better idea is to change the collation on the DB column itself.