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.

Could you write the expected SQL ? – amd May 29 '18 at 20:05 — amd, May 29 '18 at 20:05

amd · Accepted Answer · 2018-06-12T07:37:42.120

9

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);

edited Jun 12 '18 at 07:37

answered May 30 '18 at 06:33

amd

20,637
6
49
67

I have some type of rules and I want to create the bunch of helper methods those create me where clause parts and then join them together. – Navid_pdp11 May 30 '18 at 06:59
and also Is some cases properties of my rules are empty or null and must be ignored. this means I must create this Where parts independently and join them in the final state. – Navid_pdp11 May 30 '18 at 07:08
I need a way to create bracket enclosed condition dynamically – Navid_pdp11 May 30 '18 at 07:17
why you can't do this inside the loop ? – amd May 30 '18 at 07:27
loop is not my problem ... if and conditions break the fluent method. – Navid_pdp11 May 30 '18 at 07:30
I find a better way with WhereIf which enable me to check my rule data. – Navid_pdp11 May 30 '18 at 08:40

How to join multiple WHERE clauses together in SqlKata?

1 Answers1

Other ways