Finding text in a database using Entity Framework

Question

I am writing a user interface that allows someone to search for users by their first and / or last name. For example, if you typed the name “Mike” for the first name and “Joe” for the last name, it will return “Mike Jones,” “Mike Johnson,” and “Mike Jobs.” For this search, I use the following LINQ statement:

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

(Maybe or maybe not the best way to do a case-insensitive example, but it seems to work)

The problem is that the user enters a first or last name, but then leaves the other field blank. If I type “Mike” for the first name and leave the Last Name field blank, I want to return all the Mikes regardless of their last name. The above query does not return any results if both fields are not filled at least with 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 do not get any results if both fields are blank. I checked under the debugger that LastName == "" really true.

UPDATE:

I did a few more debugs, and this is actually an Oracle problem. Generated request:

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

At first glance it seems correct. However, Oracle does not seem to short the phrase ('' = '') correctly. In fact, if I do this:

 select * from TPM_USER where '' = '' 

I get null strings. I do not have enough Oracle expert to know how this request should be written, but in any case this is an error of the Entity Framework dialect.

Answer 1

Just add 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 just the predicate LASTNAME as conditional.

Late addition:

It is better to avoid expressions such as Where(u => u.FIRSTNAME.ToLower()... They cause any indexes in FIRSTNAME ignored, because the value of the field is first converted and then compared (see here for details ). )

There is a good chance that you do not need these lowercase conversions. Check the field mapping database. If it is case insensitive (CI), which probably is, you do not need these conversions.

Answer 2

Are you sure that FirstName and LastName are not null?

Instead, you can write it as ...

 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 someone encounters this problem with Oracle, the workaround is:

 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 translates to:

 '' is null 

In SQL, which Oracle interprets as true.

Answer 4

You can 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

Perhaps you can try checking the length of the search queries to see if it works 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);