Add dynamic number of OR conditions after AND

#1

I would like to get sql query like below one where I have dynamic number of values in array X:
X = new[] { “001%”, “002%” }

The query should be generated depending on the number of values in X array - in this sample case it should be:
"A = 100 AND (B like ‘001%’ OR B like ‘002%’ …)"

I do not want to use UnsafeAnd to add dynamic ORs conditions. How to construct such expression?

#2

If you don’t want to build the dynamic custom SQL expression and execute it with the fragment APIs you would need to combine multiple predicates dynamically which is typically a lot harder to do.

But for this specific flat example you can use OrmLite’s Ensure APIs to force the AND predicate whilst using OrmLite’s SqlExpression APIs to build the clauses normally, e.g:

OrmLiteUtils.PrintSql();  // view generated SQL
OrmLiteConfig.StripUpperInLike = true; // disable insensitive LIKE comparisons

public class Table
{
    [AutoIncrement]
    public long Id { get; set; }
    public string B { get; set; }
    public int A { get; set; }
}
db.CreateTable<Table>();

var X = new[] { "001", "002" };

var q = db.From<Table>()
    .Ensure(x => x.A == 100);

foreach (var item in X) 
{
    q = q.Or(x => x.B.StartsWith(item));
}

var results = db.Select(q);

Which outputs the desired:

SELECT "Id", "B", "A" 
FROM "Table"
WHERE ("A" = @0) AND ("B" like @1 OR "B" like @2)
PARAMS: @0=100, @1=001%, @2=002%

You can play with this Example Live on Gistlyn.

1 Like