Search code examples
c#postgresqldapper

How to use WILDCARD using Dapper

How can I use LIKE wildcard in postgre DB using Dapper? I have the following code:

string query = "SELECT name, index FROM article WHERE prefiks LIKE :prefix ;";
return conn.Query<myModel>(query , new { prefix = searchingValue }).ToArray();

Where searchingValue is a simple string. I tried few combinations which I already found:

return conn.Query<myModel>(query , new { prefix = "%" + searchingValue + "%" }).ToArray();

or

string query = "SELECT name, index FROM article WHERE prefix LIKE '%' || :prefix || '%'"
return conn.Query<myModel>(query , new { prefix = searchingValue }).ToArray();

But nothing works. I suppose that above solutions are fine for MS SQL but doesn't work under postgre;

Solution

  • Following should work:

    string query = "SELECT name, index FROM article WHERE prefiks LIKE :prefix ;";
searchingValue = "%" + searchingValue + "%";
return conn.Query<myModel>(query , new { prefix = searchingValue }).ToArray();

    or simply, combine the concatenation in one line:

    return conn.Query<myModel>(query , new { prefix = "%" + searchingValue + "%" }).ToArray();

    Alternatively, you may use string concatenation in SQL itself:

    string query = "SELECT name, index FROM article WHERE prefiks LIKE CONCAT('%', :prefix, '%') ;";
return conn.Query<myModel>(query , new { prefix = searchingValue }).ToArray();

    Note: I am not postgresql expert; so you may need to tune some syntax; just in case.

    This question discusses about case sensitivity of postgresql; may be helpful.