与实体框架数据库搜索文本 [英] Searching for text in a database with Entity Framework

问题描述

我正在写一个用户界面，允许一个人通过他们的第一和/或姓氏查找用户。例如，如果你在迈克键入的名字和乔为姓，它会返回麦克·琼斯，迈克·约翰逊与迈克·乔布斯。我用下面的LINQ语句此搜索：

I'm writing a UI that allows a someone to lookup users by their first and/or last name. For example, if you typed in "Mike" for the first name and "Jo" for the last name, it would return "Mike Jones", "Mike Johnson" and "Mike Jobs". I use the following LINQ statement for this search:

var users = (from u in context.TPM_USER
             where u.LASTNAME.ToLower().Contains(LastName.ToLower())
             && u.FIRSTNAME.ToLower().Contains(FirstName.ToLower())
             select u);

（有可能是也可能不是一个更好的方式做一个区分大小写的喜欢的条款，但是这似乎工作）

(There may or may not be a better way to do a case-insensitive like clause, but this seems to work)

现在的问题是，如果在第一个或最后一个名称的用户类型，但随后离开其他领域的空白。如果我输入迈克的名字，并留下姓氏领域的空白，我想不管返回其姓氏的所有米凯什。上述查询返回任何结果，除非这两个领域都填补至少东西。

The problem is if the user types in a first or last name, but then leaves the other field empty. If I type in "Mike" for the first name and leave the Last Name field blank, I want to return all Mikes regardless of their last name. The above query returns no results unless both fields are filled in with at least something.

我想：

var users = (from u in context.TPM_USER
             where (LastName == "" || u.LASTNAME.ToLower().Contains(LastName.ToLower()))
             && (FirstName == "" || u.FIRSTNAME.ToLower().Contains(FirstName.ToLower()))
             select u);

不过，我仍然没有结果，除非这两个领域都填写了。我是姓氏==确实是真的。在调试器下验证

However, I still get no results unless both fields are filled out. I've verified under the debugger that LastName == "" is indeed true.

更新：

我做了一些更多的调试，这实际上是一个Oracle的问题。正在生成的查询是：

I did some more debugging and this is actually an Oracle issue. The query being generated is:

--Replaced the field list with * for brevity
SELECT * FROM TPMDBO.TPM_USER "Extent1"
     WHERE (('jones' = '') OR ((INSTR(LOWER("Extent1".LASTNAME), LOWER('jones'))) > 0)) AND (('' = '') OR ((INSTR(LOWER("Extent1".FIRSTNAME), LOWER(''))) > 0))

乍一看似乎是正确的。但是，甲骨文似乎不正确的短路的短语（''=''）。事实上，如果我做的：

Which at first glance appears to be correct. However, Oracle does not seem to correctly short-circuit the phrase ('' = ''). In fact, if I do:

select * from TPM_USER where '' = ''

我得到零行。我没有足够的Oracle专家知道这个查询的应的写，但无论哪种方式，它是一个实体框架的方言问题。

I get zero rows. I'm not enough of an Oracle expert to know how this query should be written, but either way it's an Entity Framework dialect bug.

推荐答案

只是有条件地添加predicates：

Just add the predicates conditionally:

var users = from u in context.TPM_USER select u;
if (!string.IsNullOrWhiteSpace(FirstName))
    users = users.Where(u => u.FIRSTNAME.ToLower().Contains(FirstName.ToLower()));
if (!string.IsNullOrWhiteSpace(LastName))
    users = users.Where(u => u.LASTNAME.ToLower().Contains(LastName.ToLower()));

还是只有姓氏predicate作为条件之一。

Or only the LASTNAME predicate as conditional one.

这篇关于与实体框架数据库搜索文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！