Linq查询的行为不符合预期 [英] Linq query not behaving as expected

问题描述

我有一个非常简单的linq查询，如下所示:

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 
              });

该问题是由于某种奇怪的原因，使我检索到的每个人的记录都变为小写或大写.即

1. 测试用户
2. 测试用户
3. 测试用户

我会找回正确的记录.但是，当我使用小写字母搜索自己的名字时，我没有得到任何结果，但是如果我使用名字的第一个字母作为大写字母，那么我会得到结果.我似乎不知道为什么要这么做.

数据库中的每个姓氏和名字都以大写字母开头.

我正在使用的searchString是:

1. richard-我得到正确的结果
2. waidande-未找到结果

以上两个用户都在数据库中.

我也在使用Entity Framework来查询Sql Server 2012.

解决方案

如果您的文本具有NVARCHAR数据类型，请检查实际上并不相同的相似字母:

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<>小提琴演示

此处:'а'！= 'a'.一个来自Cyrillic 'a'，另一个来自正常.

---

想法来自:

^{幻灯片来自: http://sqlbits.com/Sessions/Event12/Revenge_The_SQL}

P.S.我强烈建议观看 Rob Volk的演讲:Revenge: The SQL! .

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.

解决方案

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

这篇关于Linq查询的行为不符合预期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！