Dapper SqlBuilder OrWhere using AND instead of OR

Question

I was trying to use the Where and OrWhere methods of SqlBuilder for Dapper, but it is not acting like how I would expect.

The edited portion of this question is basically what I ran into. Since it didn't receive a response, I'll ask it here.

var builder = new SqlBuilder();
var sql = builder.AddTemplate("select * from table /**where**/ ");
builder.Where("a = @a", new { a = 1 })
        .OrWhere("b = @b", new { b = 2 });

I expected select * from table WHERE a = @a OR b = @b

but I got select * from table WHERE a = @a AND b = @b

Is there any way to add an OR to the where clause using the SqlBuilder?

I think it's just a matter of changing the following in the SqlBuilder class to say OR instead of AND, but I wanted to confirm.

public SqlBuilder OrWhere(string sql, dynamic parameters = null)
{
    AddClause("where", sql, parameters, " AND ", prefix: "WHERE ", postfix: "\n", IsInclusive: true);
    return this;
}

Answer 1

You have to change your query into:

var builder = new SqlBuilder();
var sql = builder.AddTemplate("select * from table /**where**/ ");
builder.OrWhere("a = @a", new { a = 1 })
        .OrWhere("b = @b", new { b = 2 });

Answer 2

Nevermind. I looked through the SqlBuilder code and found that if there is a mixture of Where and OrWhere, it will do the following:

• Join all the AND clauses
• Join all the OR clauses separately
• Attach the OR clauses at the end of the AND clauses with an AND

If you don't have more than 1 OrWhere, then you won't see any OR.

I'll modify my query logic to take this into account

Answer 3

In case you want to try another alternative, DapperQueryBuilder may be easier to understand:

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


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

int a = 1;
int b = 2;

query.Where($"a = {a}");
query.Where($"b = {b}");


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

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

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