c# - 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!.