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 · Accepted Answer · 2017-05-19 19:57:40Z

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, 2017 at 19:57

answered May 27, 2016 at 16:25

phil

6382 gold badges5 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jesse de Wit Over a year ago

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)

drizin · Accepted Answer · 2020-08-07 23:24:21Z

3

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

answered Aug 7, 2020 at 23:24

drizin

2,0252 gold badges23 silver badges50 bronze badges

1 Comment

Andrew Cowenhoven Over a year ago

I wasted hours rolling my own before I found this. Highly recommended.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related