Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
279 views
in Technique[技术] by (71.8m points)

c# - Linq query not behaving as expected

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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:

enter image description here

Slide from: http://sqlbits.com/Sessions/Event12/Revenge_The_SQL

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...