EF4 Linq Oracle11g进行查询无区分大小写 [英] EF4 Linq Oracle11g making queries none case-sensitive

问题描述

我们正在转向EF4& Linq作为我们的数据库接口到Oracle 11g数据库。数据库设置为区分大小写，但我们需要搜索不区分大小写。在oracle中使用UPPER的查询可能非常昂贵。我已经看到以下选项与指示的结果：

  ChangeEntities.TABLE.Where（x => x.FIELD。等于（VARIABLE））
 
生成SQL Where子句：
 
 WHERE TABLE.FIELD = VARIABLE 
 -------------- ------- 
 ChangeEntities.TABLE.Where（x => x.FIELD.Equals（VARIABLE.ToUpper（）））
 
生成SQL Where子句：
 
 WHERE TABLE.FIELD =（UPPER（VARIABLE））
 --------------------- 
 ChangeEntities.TABLE.Where （x => x.FIELD.ToUpper（）。等于（VARIABLE.ToUpper（）））; 
 
生成SQL Where子句：
 
 WHERE（UPPER（TABLE.FIELD））=（UPPER（VARIABLE））
 --------- ------------ 
 ChangeEntities.TABLE.Where（x => x.FIELD.Equals（VARIABLE，StringComparison.CurrentCultureIgnoreCase））
 
生成SQL Where条款：
 
 WHERE TABLE.FIELD = VARIABLE 
 ----------------------- 
  

结果可以说是自己，除了最后一个例子可能会错过记录。

$ b $有没有人对技术有任何其他想法？

谢谢Sammer

解决方案

你使用什么数据提供者？尝试阅读本文（ http://www.orafaq.com/node/91 ） - 也许这可以帮助你。

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

解决方案

What data provider do you use? Try to read this article (http://www.orafaq.com/node/91) - maybe it can help you.

这篇关于EF4 Linq Oracle11g进行查询无区分大小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！