DbExtensions - WHERE with AND and OR

Question

I would like to build an SQL statement using DbExtensions. I need to build a WHERE clause with AND and OR. Something like this

SELECT *
FROM myTable
WHERE (Field1 LIKE '%a%' OR Field1 LIKE '%b%')
    AND (Field2 LIKE '%a%' OR Field2 LIKE '%b%')

I need to do this from within a loop.

var builder = SQL
    .SELECT("*")
    .FROM("myTable")
    .WHERE();

foreach (var field in fields) {
    foreach (var value in field.values) {
        builder.AppendClause("WHERE", " OR ", field.columnName + " LIKE {0}", new object[] { "%" + value.Value + "%" });
    }
}

The above code seems to produce an SQL statement like so

SELECT *
FROM myTable
WHERE Field1 LIKE '%a%' OR Field1 LIKE '%b%'
    OR Field2 LIKE '%a%' OR Field2 LIKE '%b%'

As you can see, if doesnt have any brackets and there is an OR instead of an AND.

It there anyway i can get DbExtensions to do what i want using the methods available, without having to write this all to another stringBuilder?

EDIT I have changed my code as suggested to this

var builder = SQL
   .SELECT("*")
   .FROM("myTable")
   .WHERE();

foreach (var field in fields) {
   builder._OR(field.values, field.columnName + " LIKE {0}", v => new object[1] { "%" + v.ToString() +  "%" });
}

But i also need to append an OR into the current block. So lets say that each field has a property called "CheckBlank" and if true, i need to add " OR FieldX IS NULL" how can this be done? So i would expect to end up with SQL like this if Field1.CheckBlank was true, but Field2 CheckBlank is false

SELECT *
FROM myTable
WHERE (Field1 LIKE '%a%' OR Field1 LIKE '%b%' OR Field1 IS NULL)
    AND (Field2 LIKE '%a%' OR Field2 LIKE '%b%')

Answer 1

Try this:

var builder = SQL
   .SELECT("*")
   .FROM("myTable")
   .WHERE();

foreach (var field in fields) {
   builder._OR(field.values, field.columnName + " LIKE {0}", v => new object[1] { "%" + v.ToString() +  "%" });
}

I don't know if merging the % with the parameter value is the best option. I mostly work with MySQL, where I do Field1 LIKE CONCAT('%', {0}, '%'). Don't append parameter values to the format string, it could expose an injection.

Answer 2

You can use this sample to generate specified query.

Query:

SELECT *
FROM myTable
WHERE (Field1 LIKE '%a%' OR Field1 LIKE '%b%')
    AND (Field2 LIKE '%a%' OR Field2 LIKE '%b%')

Code snippet to do this:

var builder = SQL
    .SELECT("*")
    .FROM("myTable")
    .WHERE();

foreach (var field in fields) {
        builder.WHERE("").Append("(")
        var index = 0;
        foreach (var value in field.values) {
            builder.AppendClause("WHERE", index == 0 ? "": " OR ", field.columnName + " LIKE {0}", new object[] { "%" + value.Value + "%" });
        }
        index++;
        builder.Append(")");
    }