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?

Answer 1

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.