Searching for text in a database with Entity Framework

Question

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.

I tried:

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.

UPDATE:

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 '' = ''

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.

Answer 1

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()));

Or only the LASTNAME predicate as conditional one.

Later addition:

An expression like Where(u => u.FIRSTNAME.ToLower()... is better to be avoided. They cause any indexes on FIRSTNAME to be ignored, because the field value is converted first and then compared (see here for more details).

There's a big chance you don't need these lower-case conversions. Check the database collation of the field. If it's case-insensitive (CI), which it probably is, you don't need these conversions.