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