Linq query not behaving as expected

Question

I have a very simple linq query which is as following:

var result = (from r in employeeRepo.GetAll()
              where r.EmployeeName.Contains(searchString) 
                    || r.SAMAccountName.Contains(searchString)
              orderby r.EmployeeName
              select new SelectListItem 
              { 
                  Text = r.EmployeeName, 
                  Value = r.EmployeeName 
              });

The issue is for some strange reason it fetches me the record of every person who I search for whether in lower case or upper case. i.e.

1. test user
2. Test User
3. TEST USER

I will get back the correct records. However when I search for my own name using lower case I don't get any results back but if I use the first letter of my name as upper case then I get the results. I can't seem to figure out why its doing that.

Every first and last name in the database start with upper case.

The searchString which I'm using are:

1. richard - I get correct results
2. waidande - no results found

Both of the above users are in the database.

I'm also using Entity Framework to query Sql Server 2012.

Answer 1

If your text has NVARCHAR datatype check for similiar letters that in reality are not the same:

CREATE TABLE #employee (ID INT IDENTITY(1,1), EmployeeName NVARCHAR(100));

INSERT INTO #employee(EmployeeName) VALUES (N'waidаnde');

SELECT *
FROM #employee
WHERE EmployeeName LIKE '%waidande%';

-- checking
SELECT *
FROM #employee
WHERE CAST(EmployeeName AS VARCHAR(100)) <> EmployeeName;

db<>fiddle demo

Here: 'а' != 'a'. One is from Cyrillic 'a' and the second is normal.

---

Idea taken from:

^{Slide from: http://sqlbits.com/Sessions/Event12/Revenge_The_SQL}

P.S. I highly recommend to watch Rob Volk's talk: Revenge: The SQL!.

Answer 2

To troubleshoot the issue, determine whether the problem is on the EF side, or on DB side. A common mistake is extra whitespace, so make sure it's not the case before proceeding.

First check what query is being generated by EF, you can use one of the following methods to do this

1. ObjectQuery.ToTraceString() method
2. EF logging of intercepted db calls
3. Sql server profiler

If you are using EF correctly and your query is translated to SQL as expected and contains the predicates in the where section, but you still are not getting any meaningful results, here are some ideas to try out on the DB side:

1. Check collation ( be aware it can be set on server, database and individual column level) - beware of case sensitivity and code page that is being used
2. Verify that your search string contains symbols that can be interpreted in the db code page - for example if code page is 252 - Windows Latin 1 ANSI and you are sending input with symbols from UTF-16 that are outside ANSI - you won't get any results, even though the symbols look the same
3. Highly improbable, but as last resort check if one of your queries has not been cached, as described here

Answer 3

SQL Server 2012 (SQL Server) is installed by default with case insensitive collation. If you need to retrieve records from the database using case sensitivity (because you have "several" records) you need to change the collation (take care because if you change DBMS collation you change also master database collation so also tables and field names become case sensitive).
If you don't need to avoid to retrieve all the records from the DBMS you can just filter records after you retrieve them, i.e.

var result = (from r in employeeRepo.GetAll()
          where r.EmployeeName.Contains(searchString) 
                || r.SAMAccountName.Contains(searchString)
          orderby r.EmployeeName
          select new SelectListItem 
          { 
              Text = r.EmployeeName, 
              Value = r.EmployeeName 
          })
          .ToList()  // Materialize records and apply case sensitive filter
          .Where(r.EmployeeName.Contains(searchString) 
                || r.SAMAccountName.Contains(searchString));