Dapper: SqlBuilder OR clause

[rafakwolf]

The SqlBuilder seems to be adding "OR" clause the wrong way...

are adding "AND" instead of "OR".

builder.Where("MyField = @Param",paramObj);
builder.OrWhere("MyField2 = @Param2",paramObj2); 

results this statement:
WHERE MyField = @Nome AND MyField2 = @Param2

Thanks

[ghost]

This does seem to be an issue prevalent within the code base. See line 146 of SqlBuilder.cs:

AddClause("where", sql, parameters, " AND ", "WHERE ", "\n", true);

[smax48]

I would disagree that it was really a bug - with the previous code, if you call both Where and OrWhere _multiple_ times, the final result was like this:
(WhereCond1 AND WhereCond2 AND ...) AND (OrWhereCond1 OR OrWhereCond2 OR OrWhereCond3 OR ...)
From my perspective, that makes sense.
With this new update we have a breaking change.

[ghost]

I'm inclined to disagree, the function is literally called 'OrWhere', not 'AndWhere'. Pretty sure you can still use the regular .Where function to achieve what you wanted.

[smax48]

It doesn't really matter if I can achieve the same effect using the regular .Where().
The problem here is that so far we've had consistent behaviour (maybe a bit strange, but it worked).
The proposed change is a BREAKING change, so literally it will break ALL existing code that relies on the current behaviour. Dapper is a very popular library and we cannot just break things.
If you need a different behaviour, it is a lot more better to add a new function (or a parameter to the existing one), than to change what we have atm.

[CrescentFresh]

I guess no one bothered to check the original test case. It is still not "fixed":

var sql = new SqlBuilder();
var tpl = sql.AddTemplate("select * from foo /**where**/");

sql.Where("MyField = @Param");
sql.OrWhere("MyField2 = @Param2");

@rafakwolf expected:

select * from foo WHERE  ( MyField = @Param OR MyField2 = @Param2 )

However both the previous and fixed SqlBuilder produces the following:

select * from foo WHERE MyField = @Param AND  ( MyField2 = @Param2 )

This is because only the first call to one of Where() or OrWhere() is the one that defines the joiner variable. That is, the variable that dictates what string to place between clauses. If Where() is called first the joiner will be "AND" but if OrWhere() is called first the joiner will be "OR".

Consider this: with the "fix" we get the following behavior (imagine these are in separate functions that don't know about each other):

sql.Where("a = @a");

sql.OrWhere("b = @b1");
sql.OrWhere("b = @b2");

result:

select * from foo WHERE a = @a AND (b = @b1 OR b = @b2)

But switch the order of the calls:

sql.OrWhere("b = @b1");
sql.OrWhere("b = @b2");

sql.Where("a = @a");

result:

select * from foo WHERE a = @a OR (b = @b1 OR b = @b2)

And that is what I would call a bug.

As a side comment, with the previous code it was at least consistent in that it always produced the first result irrespective of order.