Creating Nested OR Conditions

ServiceStack.OrmLite SQL Server
1

I’d like to be able to build a query using OrmLite with the following format:

SELECT *
FROM TABLE
WHERE (FirstCol = 2) 
AND (SecondCol LIKE '%some_string%' OR SecondCol LIKE '%some_other_string%')

Is it possible to build the query with a SqlExpression so that I can have a clause that is joined with an AND but internally contains ORs?

Update

I see that someone has asked a similar question a few years ago.

2

That looks like this query:

var q = db.From<Table>()
    .Where(x => x.FirstCol == 2)
    .And(x => x.SecondCol.Contains("some_string") || x.SecondCol.Contains("some_other_string"));
3

I know this an old thread, but I just found it. Is there another way to make the “sub where” than using || in this case?

It’s not so easy to add or conditions dynamically, e.g. in a loop:

// at this point I've got a query, with a few ANDs already

foreach (var name in onlyNames) {
    string xname = name + "%";
    q = q.Or("ProcessDefinition.Name LIKE {0}", xname);
}

To be clear, I want my OR’s inside the outer Where, so the total query becomes

where x AND y AND (ProcessDefinition.Name LIKE 'hr%' OR ProcessDefinition.Name LIKE 'recruiting%')

The parenthesis enclosing the ORs are the issue of course, like the OP also had problem with.

4

Add the combined conditions in a single condition, e.g:

q.And("(ProcessDefinition.Name LIKE {0} OR ProcessDefinition.Name LIKE {1})",
     "hr%", "recruiting%");
```
5

That’s what the previous answer said, and I understand. Perfect for most cases I guess.

My question was if there was a different way, since building the SQL for the condition manually was what I wanted to avoid. Don’t look forward to a large StringBuilder statement. However I’m pretty sure the answer is that there isn’t a a different way.

I’m thinking something in the lines of:

var w = new WhereCondition<table>(t=>something).Or(t=>somethingelse)

// now add that fully parenthesis-ed condition to the query
q = q.Where(w)

In this case the “w” would have parenthesis around it.

6

An alternative is to construct C# expressions trees, although that takes a fair bit more effort than constructing an string SQL expression.

OrmLite also includes a copy of Joe Albahari’s PredicateBuilder that can help with this.

7

You’re right, the expression tree has a steep learning curve.
I think I’ve found a middle road though. Adding it here to help others.

  1. Construct a separate SqlExpression
  2. Use the .ToMergedParamsSelectStatement(); to get the SQL string
  3. Take the WHERE part of that one, and add it to the main SqlExpression as SQL
var qa = db.From<ProcessInstance>();  // qa = the "inner one"

// add whatever conditions with AND and OR to "qa"

foreach (var name in ...) {
  qa = qa.Or("ProcessDefinition.Name LIKE {0}", name);
}

// extract inner statement as ready-built SQL w. all params inside
var innerStmt = qa.ToMergedParamsSelectStatement();
var idxWhere = innerStmt.IndexOf("WHERE ") + "WHERE ".Length;
var innerWhere = "(" + innerStmt.Substring(idxWhere) + ")";
q = q.And(innerWhere);  // add it to the main statement
1 Like
8

Yeah that’s a pretty nice alternative solution, good example of making use of different features.