Does Dapper support the like operator?

Question

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?

score 82 · Accepted Answer · edited Sep 13 '16 at 09:36

82

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.

edited Sep 13 '16 at 09:36

TDaver

7,164
5
47
94

answered May 17 '11 at 12:09

Sam Saffron

128,308
78
326
506

Sam - trying to get your answer to work in my project, but throwing errors. why do you set the "term" before declaring it as a string? I only need the % on the right side of the term in the query. I tried doing: string formattedTerm = searchTerm + "[[]%[]]"; but it is not returning results. – jpshook Jul 29 '11 at 12:12
Sam - might be worth answering him. This has confused me as well – RobVious Apr 09 '15 at 04:51
.Replace("[", "[[]").Replace("]","[]]") - this is wrong. Make sure you escape your like clause though: http://stackoverflow.com/questions/439495/how-can-i-escape-square-brackets-in-a-like-clause – razon Apr 21 '15 at 04:51
1

Note: this is approach is vulnerable to SQL injection attacks, check Atir's answer for a better approach – Leo Sep 26 '18 at 07:04
2

For anyone (like) me, who didn't knew about "**SARGable**", [here is an explanation](https://en.wikipedia.org/wiki/Sargable). – Uwe Keim Aug 02 '21 at 12:00

score 62 · Answer 2 · edited Feb 12 '17 at 08:29

62

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

edited Feb 12 '17 at 08:29

Aimee Borda

842
2
11
22

answered Feb 12 '17 at 02:58

Eliaquín

729
6
7

5

This is the answer i liked best...simple and straight forward – oliverdejohnson Feb 12 '19 at 15:42
5

This is my favorite for sure - I use string interpolation as well so it becomes ... new { n = $"%{nombre}%" } – Chad Carter Apr 10 '21 at 01:31
1

perfect! Made ext method out of it – ihor.eth May 08 '21 at 22:01
2

should be the accepted answer – 3xCh1_23 Mar 02 '22 at 19:16
Why this one isn't accepted answer? – Khaled Md Tuhidul Hossain Oct 12 '22 at 09:07

score 59 · Answer 3 · answered Mar 16 '16 at 08:14

59

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

answered Mar 16 '16 at 08:14

Atir Naveed

629
5
3

8

You can also use '+' instead of CONCAT eg: "SELECT * from country WHERE Name LIKE '%' + @name + '%';" – jhhwilliams Apr 09 '18 at 10:04

score 9 · Answer 4 · edited May 23 '17 at 11:54

9

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

edited May 23 '17 at 11:54

Community

1
1

answered Jul 25 '16 at 02:44

Dscoduc

7,714
10
42
48

This also works in IBM Db2. Thanks. – Amjad Abujamous Jul 22 '23 at 18:37

Tore Aurstad · Answer 5 · 2021-06-28T23:19:17.160

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.

score 0 · Answer 6 · edited Feb 05 '22 at 21:28

0

My solution simple to this problem :

parameter.Add("@nomeCliente", dfNomeCliPesquisa.Text.ToUpper());

query = "SELECT * FROM cadastrocliente WHERE upper(nome) LIKE " + "'%" + dfNomeCliPesquisa.Text.ToUpper() + "%'";

edited Feb 05 '22 at 21:28

Zoe

27,060
21
118
148

answered Feb 05 '22 at 21:09

Thiago Macena

1

Does Dapper support the like operator?

6 Answers6

Linked