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

score 9 · Accepted Answer · answered Jul 22 '15 at 18:53

9

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

answered Jul 22 '15 at 18:53

Fama

346
2
11

So how does your solution look? I stumbled over this issue, too and after looking through the source-code of SqlBuilder i wonder if nesting ORs and ANDs works somehow in sqlbuilder: (... OR ...) AND (... OR ...) vs. (... AND ...) OR (... AND ...) – aurora Jan 12 '16 at 00:09
1

From what I remember, it seems to work this way. (... AND ... AND ... AND ( ... OR ... OR ... OR)). It will put all the ANDs together and then put the ORs at the end in their own group – Fama Jan 13 '16 at 03:29
I have two OrWheres and two Wheres. I don't see any ANDs in the generated query, only ORs. – Mitulát báti Oct 28 '21 at 22:10

score 2 · Answer 2 · answered Nov 08 '17 at 09:27

2

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

answered Nov 08 '17 at 09:27

Popa Andrei

2,299
21
25

score 1 · Answer 3 · answered Aug 07 '20 at 23:30

1

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

answered Aug 07 '20 at 23:30

drizin

1,737
1
18
44

1

I'm looking forward to giving DapperQueryBuilder a go some time. It looks great, well done. Is it easy to see what the generated SQL is whilst debugging? – Zac Oct 20 '20 at 23:46
2

@Zac yes, if you hover the mouse over "query" you should see the underlying query and the attached parameters. – drizin Oct 21 '20 at 00:27

Dapper SqlBuilder OrWhere using AND instead of OR

3 Answers3