How do I build a dynamic sql query with Dapper.SqlBuilder and OrWhere

Question

I am attempting to build a dynamic Sql query for multiple search terms. I understand in general how to use the builder, but am not sure what to do in the loop since I actually need the @term to be different each time (I think). Not just in the query, but in the anonymous type as well to match.

I could use a string.Format in the query string, but not sure how to match it in the anonymous type?

public async Task<List<Thing>> Search(params string[] searchTerms)
{
    var builder = new SqlBuilder();
    var template = builder.AddTemplate("SELECT * /**select**/ from ThingTags /**where**/ ");

    for (int i = 0; i < searchTerms.Length; i++)
    {
        builder.OrWhere("value LIKE @term", new { term = "%" + searchTerms[i] + "%" });
    }
...
}

in the current form the query that gets created for terms "abc" "def" "ghi" is

CommandType: Text, CommandText: SELECT *  from ThingTags WHERE  ( value LIKE @term OR value LIKE @term OR value LIKE @term ) 

Parameters:
Name: term, Value: %ghi%

phil · Answer 1 · 2017-05-19T19:57:40.073

8

Well here is one way to do the query building. I didn't realize that the parameters could be a Dictionary initially.

public async Task<List<Thing>> Search(params string[] searchTerms)
{
var builder = new SqlBuilder();
var template = builder.AddTemplate("SELECT * /**select**/ from ThingTags /**where**/ ");

    for (int i = 0; i < searchTerms.Length; i++)
    {
        var args = new Dictionary<string, object>();
        var termId = string.Format("term{0}", i.ToString());
        args.Add(termId, "%" + searchTerms[i] + "%");
        builder.OrWhere("value LIKE @" + termId, args);
    }
...
}

edited May 19 '17 at 19:57

answered May 27 '16 at 16:25

phil

618
2
5
17

Thanks for answering your question. Just a side comment; the string.Format is inefficient. Use either `term + i.ToString()` or `string.Format("term{0}", i)` – Jesse de Wit May 18 '17 at 06:06

score 3 · Answer 2 · answered Aug 07 '20 at 23:24

You can easily create that dynamic condition using DapperQueryBuilder:

var query = cn.QueryBuilder($@"
    SELECT * 
    FROM ThingTags 
   /**where**/");

// by default multiple filters are combined with AND
query.FiltersType = Filters.FiltersType.OR; 

foreach (var searchTerm in searchTerms)
    query.Where($"value like {searchTerm}");

var results = query.Query<YourPOCO>();

The output is fully parametrized SQL (WHERE value like @p0 OR value like @p1 OR...). You don't have to manually manage the dictionary of parameters.

Disclaimer: I'm one of the authors of this library

I wasted hours rolling my own before I found this. Highly recommended. — Andrew Cowenhoven, Jun 10 '22 at 12:25

How do I build a dynamic sql query with Dapper.SqlBuilder and OrWhere

2 Answers2

Linked