英文:
Include space in EF Core Startswith
问题
当使用EF Core查询时，使用StartsWith时，空格不会被包括在内，例如这个查询（注意Bob后面的空格）：
var query = _context.Persons
.Where(p => p.FullName.StartsWith("Bob "))
.Select(p => p.FullName);
将包括Bobby、Bobfish和Bobwhatever，因为SQL使用的是LEN('Bob ')，这个长度是3，因为LEN不包括尾随空格。
如何包括空格的技巧是什么？
英文:
When doing an EF Core query with StartsWith, then space is not included e.g. this query (notice space after Bob):
var query = _context.Persons
.Where(p => p.FullName.StartsWith("Bob ")
.Select(p => p.FullName);
will include Bobby, Bobfish and Bobwhatever because the SQL uses LEN('Bob ') that is 3 because LEN does not include trailing spaces.
What is the trick to include spaces?
答案1
得分: 3
你可以尝试通过EF.Functions明确使用LIKE：
var query = _context.Persons
.Where(p => EF.Functions.Like(p.FullName, "Bob %"))
.Select(p => p.FullName);
英文:
You can try using LIKE explicitly via EF.Functions:
var query = _context.Persons
.Where(p => EF.Functions.Like(p.FullName, "Bob %"))
.Select(p => p.FullName);
答案2
得分: 1
以下是您要翻译的内容：
一种技巧是使用DbFunctions.Like来实现SQL原生的LIKE筛选：
var query = _context.Persons
.Where(p => EF.Functions.Like(p.FullName, "Bob %"))
.Select(p => p.FullName);
这是基于EF6解决方案，使用SqlFunctions.PatIndex，允许您使用SQL LIKE模式进行匹配，因此我们可以绕过LINQ到SQL实现，该实现忽略了空格：
var query = _context.Persons
.Where(p => SqlFunctions.PatIndex("Bob %", p.FullName) > 0)
.Select(p => p.FullName);
> 0在这种情况下是正确的，因为PatIndex返回基于1的序号，指示找到模式的位置。如果没有匹配项，结果将为0。在以_开始_的模式的情况下，结果将始终是1或0。
如果搜索模式是参数化的，那么我们只需要将%通配符字符附加到末尾：
var query = _context.MyTable
.Where(v => v.Adresse.StartsWith(pre + "%"))
.Select(v => $"{v.Adresse}").Take(10);
或者您可以将其注入到自己的显式变量中：
var searchExp = pre + "%";
var query = _context.MyTable
.Where(v => v.Adresse.StartsWith(searchExp))
.Select(v => $"{v.Adresse}").Take(10);
英文:
One trick is to use DbFunctions.Like to implement SQL native LIKE filtering:
var query = _context.Persons
.Where(p => EF.Functions.Like(p.FullName, "Bob %"))
.Select(p => p.FullName);
This is based on the EF6 solution using SqlFunctions.PatIndex that allows you to use a SQL LIKE pattern to match, so we can bypass the LINQ to SQL implementation that ignores the whitespace:
var query = _context.Persons
.Where(p => SqlFunctions.PatIndex("Bob %", p.FullName) > 0)
.Select(p => p.FullName);
> > 0 is correct in this instance because PatIndex returns a 1-based ordinal for where the pattern was found. If there are no matches, the result will be 0. In the case of a StartsWith pattern, the result will always be 1 or 0.
If the search pattern is parameterised then we just need to append the % wildcard character to the end:
var query = _context.MyTable
.Where(v => v.Adresse.StartsWith(pre + "%"))
.Select(v => $"{v.Adresse}").Take(10);
or you can inject this into it's own explicit variable:
var searchExp = pre + "%";
var query = _context.MyTable
.Where(v => v.Adresse.StartsWith(searchExp))
.Select(v => $"{v.Adresse}").Take(10);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论