0

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?

Community
  • 1
  • 1
Sumedha Vangury
  • 643
  • 2
  • 17
  • 43
  • Try to use this query: `var searchedAppUsers = await _context.AppUser.Where(u => u.Profile.FirstName.ToLowerCase().Contains(q.ToLowerCase()) || u.Profile.FirstName.ToLowerCase().Contains(q.ToLowerCase())).ToListAsync();`. Maybe you have query and data in different cases. – Maxim Goncharuk Dec 02 '15 at 11:23

2 Answers2

5

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.

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
  • Sorry for the late reply, but yes this works. All I did was ` _context.AppUser .Where(u => u.Profile.FirstName + " " + u.Profile.LastName).Contains(q)` without actually including the `Split` part, it gives the as expected. – Sumedha Vangury Dec 12 '15 at 05:59
0

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.

NickH
  • 1