How to join multiple WHERE clauses together in SqlKata?

Question

I am using SqlKata to creating dynamic SQL queries. I have a list of conditions, stored in my database, which are generated according to my business rules. this is my code sample:

var list = new List<Query>();
foreach(var rule in rules){
    var q = new Query()
       .Where(x=> x.Where("Price", "<", rule.Price).OrWhere("GoodsType", "=", rule.Type));
    list.Add(q);
}

Now I want to join this list item together but none of Where() extensions overload accepts Query type parameters. Is there a way to join the where clauses together?

this is A VERY LITTLE PART OF expected query which I need to generate.

select * from ship_schedule where Path = @path and scheduleDate= @Date
AND (FD.IssueType ='O' OR fd.Path!='ILMTOP' OR  (fd.Path='ILMTOP' AND F.carrier !='MAL'))
AND (FD.IssueType ='O' OR fd.Path!='TOPILM' OR  (fd.Path='ILMTOP' AND F.carrier !='MAL'))

I need to create the second line of the query to the end.

Answer 1

The Where method is additive and calling it multiple times will add multiple conditions to the query, so you don't need to build the list of conditions by yourself.

var query = new Query("ship_schedule").Where("Path", path);

foreach(var rule in rules) {
  // loop over rules and append them to the query

  if(col == null) {

    query.WhereNull(col);

  } else {

    query.Where(q => 
      q.Where("Price", "<", rule.Price)
        .OrWhere("GoodsType", "=", rule.Type)
    )

  }
}

Other ways

using the When method

query.When(condition, q => q.Where(...));

using the WhereIf method

query.WhereIf(condition, "Id", "=", 10);