Searching for text in a database with Entity Framework

.net c# entity-framework linq oracle

Question

I'm creating a user interface that enables people to be looked up by their first and/or last name. As an example, "Mike Jones," "Mike Johnson," and "Mike Jobs" would be returned if you input "Mike" for the first name and "Jo" for the surname name. For this search, I use the LINQ statement that follows:

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

(This method of creating a case-insensitive like clause may or may not be preferable.)

The issue arises when the user fills in either the first or last name but leaves the other field blank. I want to return all Mikes, regardless of their last name, if I enter "Mike" for the first name and leave the Last Name box empty. Unless both fields are filled up with at least anything, the above query does not return any results.

I attempted to

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, until all areas are filled out, I still receive no results. I've established using the debuggerLastName == "" certainly holds true.

UPDATE:

More debugging revealed that this is essentially an Oracle problem. The following is the produced query:

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

it seems to be accurate at first look. Oracle, however, does not seem to be accurately understanding the sentence.('' = '') . In actuality, if I:

select * from TPM_USER where '' = ''

I get 0 rows. Although I'm not a knowledgeable enough in Oracle to know how to write this query, it's an Entity Framework dialect problem.

1
3
6/7/2012 5:21:13 PM

Accepted Answer

Just add the conditional predicates:

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

Alternately, just use the LASTNAME predicate as a conditional one.

After the fact:

Using a phrase likeWhere(u => u.FIRSTNAME.ToLower()... is preferable to stay away from. The indexes they create onFIRSTNAME to be disregarded as the field value is first transformed and then compared (see here for more details).

Most likely, you won't need these lower-case changes. Verify the field's database collation. You don't need these changes if it's case-insensitive (CI), which it probably is.

9
5/9/2019 6:10:07 PM

Popular Answer

FirstName and LastName—are you certain they aren't null?

You may try drafting it this way 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);


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow