Linq 查询未按预期运行 [英] Linq query not behaving as expected

问题描述

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

var 结果 =(来自employeeRepo.GetAll() 中的r其中 r.EmployeeName.Contains(searchString)||r.SAMAccountName.Contains(searchString)orderby r.EmployeeName选择新的 SelectListItem{文本 = r.EmployeeName,值 = 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');选择 *来自#employeeWHERE EmployeeName LIKE '%waidande%';-- 检查选择 *来自#employeeWHERE CAST(EmployeeName AS VARCHAR(100)) <>员工姓名;

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

附言我强烈建议您观看 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屋！