Trying to build an sql query using Sqlkata with multiple conditions

668 Views Asked by At

I am trying to achieve the below query by using the SQLKata. Was able to partially achieve with the code below and still struck creating the Or grouping in the expected query below(highlighted in bold).

Expected Query SELECT * FROM A INNER JOIN B ON (A.C1 = B.BC1 AND A.C2 = B.BC2 AND A.C3 = B.BC3) OR (A.C5 = B.BC5 AND A.C6 = C.BC6) WHERE A.BATCH = 1

There is a configuration table where we store our config. My Configuration is as below

field matching_field matching_table new_or_group?
C1 BC1 B No
C2 BC2 B No
C3 BC3 B No
C5 BC5 B Yes
C6 BC6 B No

The primary table is "A" and which then joins with the matching table "B" on the above field conditions

Below is the code I did.

   query.Join(matching_table, q =>
        {
            bool bReset = false;
            foreach (TBLConfiguration internalField in fields)
            {
                if (bReset == false)
                {
                    if (internalField.field == null)
                    {
                        q.WhereNull(internalField.matching_field);
                    }
                    else
                    {
                        q.On(internalField.matching_field, 
                      internalField.field, "=");
                    }
                }


                 if (bReset == true)
                 {
                    if (internalField.field == null)
                    {
                       //This is where i am trying to do the start 
                       "Or" condition for the join (second group)
                       q.OrWhereNull(internalField.matching_field);
                    }
                    
                    bReset = false;
                }

                if (internalField.new_or_group? == "yes")
                {
                    bReset = true;
                }

            }

            return q;
        });
0

There are 0 best solutions below