Linq query not behaving as expected

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

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