Does Dapper support the like operator?
SqlDapperSql Problem Overview
Using 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
?
Sql Solutions
Solution 1 - Sql
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.
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.
caveat
Any like
match that is leading with a wildcard is not SARGable, which means it is slow and will require an index scan.
Solution 2 - Sql
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
string query = "SELECT * from country WHERE Name LIKE CONCAT('%',@name,'%');"
var results = connection.query<country>(query, new {name});
Solution 3 - Sql
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();
Solution 4 - Sql
The answer from Sam wasn't working for me so after some testing I came up with using the SQLite CONCAT equivalent which seems to work:
string sql = "SELECT * FROM myTable WHERE Name LIKE '%' || @NAME || '%'";
var data = IEnumerable data = conn.Query(sql, new { NAME = Name });
Solution 5 - Sql
Just to digress on Sam's answer, here is how I created two helper methods to make searches a bit easier using the LIKE operator.
First, creating a method for generating a parameterized query, this method uses dynamic: , but creating a strongly typed generic method should be more desired in many cases where you want static typing instead of dynamic.
public static dynamic ParameterizedQuery(this IDbConnection connection, string sql, Dictionary<string, object> parametersDictionary)
{
if (string.IsNullOrEmpty(sql))
{
return null;
}
string missingParameters = string.Empty;
foreach (var item in parametersDictionary)
{
if (!sql.Contains(item.Key))
{
missingParameters += $"Missing parameter: {item.Key}";
}
}
if (!string.IsNullOrEmpty(missingParameters))
{
throw new ArgumentException($"Parameterized query failed. {missingParameters}");
}
var parameters = new DynamicParameters(parametersDictionary);
return connection.Query(sql, parameters);
}
Then adding a method to create a Like search term that will work with Dapper.
public static string Like(string searchTerm)
{
if (string.IsNullOrEmpty(searchTerm))
{
return null;
}
Func<string, string> encodeForLike = searchTerm => searchTerm.Replace("[", "[[]").Replace("%", "[%]");
return $"%{encodeForLike(searchTerm)}%";
}
Example usage:
var sql = $"select * from products where ProductName like @ProdName";
var herringsInNorthwindDb = connection.ParameterizedQuery(sql, new Dictionary<string, object> { { "@ProdName", Like("sild") } });
foreach (var herring in herringsInNorthwindDb)
{
Console.WriteLine($"{herring.ProductName}");
}
And we get our sample data from Northwind DB:
I like this approach, since we get helper extension methods to do repetitive work.
Solution 6 - Sql
My solution simple to this problem :
parameter.Add("@nomeCliente", dfNomeCliPesquisa.Text.ToUpper());
query = "SELECT * FROM cadastrocliente WHERE upper(nome) LIKE " + "'%" + dfNomeCliPesquisa.Text.ToUpper() + "%'";