SQL Dapper 是否支持 like 运算符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6030099/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does Dapper support the like operator?
提问by Jay Stevens
Using Dapper-dot-net...
使用 Dapper-dot-net...
The following yields no results in the data object:
以下内容不会在数据对象中产生任何结果：
var data = conn.Query(@"
select top 25
Term as Label,
Type,
ID
from SearchTerms
WHERE Term like '%@T%'",
new { T = (string)term });
However, when I just use a regular String Format like:
但是，当我只使用常规字符串格式时，例如：
string QueryString = String.Format("select top 25 Term as Label, Type, ID from SearchTerms WHERE Term like '%{0}%'", term);
var data = conn.Query(QueryString);
I get 25 rows back in the collection. Is Dapper not correctly parsing the end of the parameter @T?
我在集合中得到 25 行。Dapper 没有正确解析参数的结尾@T吗？
回答by Sam Saffron
Try:
尝试：
term = "whateverterm";
var encodeForLike = term => term.Replace("[", "[[]").Replace("%", "[%]");
string term = "%" + encodeForLike(term) + "%";
var data = conn.Query(@"
select top 25
Term as Label,
Type,
ID
from SearchTerms
WHERE Term like @term",
new { term });
There is nothing special about like operators, you never want your params inside string literals, they will not work, instead they will be interpreted as a string.
like 操作符没有什么特别之处，你永远不希望你的参数在字符串文字中，它们将不起作用，而是将它们解释为字符串。
note
笔记
The hard-coded example in your second snippet is strongly discouraged, besides being a huge problem with sql injection, it can cause dapper to leak.
强烈建议不要使用第二个代码段中的硬编码示例，除了 sql 注入的一个大问题之外，它还可能导致 dapper 泄漏。
caveat
警告
Any likematch that is leading with a wildcard is not SARGable, which means it is slow and will require an index scan.
任何like以通配符开头的匹配项都不是 SARGable，这意味着它很慢并且需要索引扫描。
回答by Atir Naveed
Best way to use this to add concat function in query as it save in sql injecting as well, but concat function is only support above than sql 2012
使用它在查询中添加 concat 函数的最佳方法，因为它也保存在 sql 注入中，但 concat 函数仅支持高于 sql 2012
string query = "SELECT * from country WHERE Name LIKE CONCAT('%',@name,'%');"
var results = connection.query<country>(query, new {name});
回答by eliaquin
Yes it does. This simple solution has worked for me everytime:
是的，它确实。这个简单的解决方案每次都对我有用：
db.Query<Remitente>("SELECT *
FROM Remitentes
WHERE Nombre LIKE @n", new { n = "%" + nombre + "%" })
.ToList();
回答by Dscoduc
The answer from Sam wasn't working for me so after some testing I came up with using the SQLite CONCAT equivalentwhich seems to work:
Sam 的回答对我不起作用，所以经过一些测试后，我想出了使用SQLite CONCAT 等效项，它似乎有效：
string sql = "SELECT * FROM myTable WHERE Name LIKE '%' || @NAME || '%'";
var data = IEnumerable data = conn.Query(sql, new { NAME = Name });
