How to create a query with multiple LIKEs that does not create memory issue in Dapper's cache

Question

I'm using Dapper to work with a SQL database. I have some search logic in my website project. My search gets a list of string parameters:

//filter is list of strings

var sql = new StringBuilder();
sql.Append("SELECT LibraryDocumentId FROM LibraryDocumentKeywords WHERE ");

sql.Append(string.Join("OR ", filter.Select(f => string.Format("LOWER(Keyword) LIKE '%{0}%'", f)).ToList()));

var isList = conn.Query<int>(sql.ToString()).ToList();

I don't want to use this approach of generating dynamic SQL query, because Dapper will cache every single query. I would prefer to pass the filter with parameters. Can someone help me with that?

Answer 1

What you have at the moment is also a huge SQL injection risk. You might want to use DynamicParameters here, i.e. (completely untested, you may need to tweak slightly):

var sql = new StringBuilder(
    "SELECT LibraryDocumentId FROM LibraryDocumentKeywords");
int i = 0;
var args = new DynamicParameters();
foreach(var f in filter) {
    sql.Append(i == 0 ? " WHERE " : " OR ")
        .Append("LOWER(Keyword) LIKE @p").Append(i);
    args.Add("p" + i, "%" + f + "%");
    i++;
}
var data = conn.Query<int>(sql.ToString(), args);

This should cache fairly cleanly (one cache item per number of filters, regardless of their contents).