Add dynamic number of OR conditions after AND

ServiceStack.OrmLite
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