CODEWITHSUNDEEP
c#sqllambdaentity-framework-4
Sumedha Vangury
Sumedha Vangury

Reputation: 693

How to use Like operator in entity framework lamba expression

I am writing an sql query for search where the users searches according to the FirstName and LastName. The query works fine when searched for FirstName or LastName, but when I search for FirstName and LastName(ie space between FirstName and LastName), it gives out blank result. The query string looks like this:
http://localhost:4562/api/User/Search?q=sumedha%20v&filters=&offset=3&limit=9

The sql query is : List<AppUser> searchedAppUsers = await _context.AppUser.Where(u => u.Profile.FirstName.StartsWith(q) || u.Profile.LastName.StartsWith(q)).ToListAsync();

I tried using Contains() instead of StartsWith() but it still gives a blank result. I also tried using SqlMethods.Like() but couldn't see it in the intellisense. I tried using SqlFunctions.PatIndex(), but same problem. I tried to follow this post, but dint get how to do it.
Is there any other way? or am I going wrong somewhere?

Upvotes: 0

Views: 6466

Answers (2)

Gert Arnold
Gert Arnold

Reputation: 109271

Well, if your search term is "sumedha v" it's obvious that there's no FirstName or LastName that contains this string. You're apparently looking for something like this:

_context.AppUser
        .Where(u => (u.Profile.FirstName + " " + u.Profile.LastName).Contains(q))

But this may perform poorly, because now the query engine can't use any index on the name fields. It shouldn't be a problem if there aren't many (thousands of) users.

Maybe you meant to do a search on FirstName and LastName using the first and second part of the search string respectively? Like this: 

var parts = q.Split(q, ' ');
var q1 = parts[0];
var q2 = parts[1];
var result = _context.AppUser
                     .Where(u => u.Profile.FirstName.Contains(q1)
                         && u.Profile.LastName.Contains(q2));

(without null checks)

This would perform better, because the names fields are queried directly.

Upvotes: 5

NickH
NickH

Reputation: 1

Firstly your query only checks FirstName:

List searchedAppUsers = await _context.AppUser.Where(u => u.Profile.FirstName.StartsWith(q) || u.Profile.FirstName.StartsWith(q)).ToListAsync();

Secondly as I understand it you are searching for something like "John Doe" but neither FirstName ("John") nor LastName ("Doe") starts with "John Doe" (or is LIKE 'John Doe%').

Think again about what your SQL would be.

Upvotes: 0

Related Questions