Dapper SqlBuilder OrWhere using AND instead of OR

dapper sqlbuilder

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;
}

Accepted Answer

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


Popular Answer

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 });


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why