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.