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.

Answer 2

Are you sure that FirstName and LastName aren't null?

You might try writing it like this instead...

string LowerFirstName = (FirstName + "").ToLower();
string LowerLastName = (LastName + "").ToLower();

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

Answer 3

FYI, if anyone runs into this issue with Oracle, here's a workaround:

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

This will get converted to:

'' is null

In SQL, which Oracle interprets as true.

Answer 4

You could simply create a conditional statement around your query:

if (String.IsNullOrWhiteSpace(LastName) && !String.IsNullOrWhiteSpace(FirstName))
{
   var users = (from u in context.TPM_USER
   where (u.FIRSTNAME.ToLower().Contains(FirstName.ToLower()))
   select u);
}
else if (String.IsNullOrWhiteSpace(FirstName) && !String.IsNullOrWhiteSpace(LastName))
{
   var users = (from u in context.TPM_USER
   where (u.LASTNAME.ToLower().Contains(LastName.ToLower()))
   select u);
}

Answer 5

May be you can try checking the length of the search terms to see if it is working in Oracle PL/SQL.

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