How create query with multiple LIKE-s that does not create memory issue in dapper cache?

Question

I'm using Dapper to work with sql database. I have some search logic in my website project.

My search gets 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();

Actually 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 filter with parameter. Can someone help me with that? Any idea ?

Have you read this related question? http://stackoverflow.com/questions/9481678/how-to-create-arguments-for-a-dapper-query-dynamically?rq=1 — Turophile, May 15 '14 at 14:25

score 8 · Accepted Answer · answered May 15 '14 at 15:02

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).

Thank you very much, this is much better than my solution. – Mike May 15 '14 at 16:13 — Mike, May 15 '14 at 16:13

How create query with multiple LIKE-s that does not create memory issue in dapper cache?

1 Answers1

Related