Serenity: We Can't Write Not Exists Sql Sub Query

[chintankukadiya18]

If i wan to join multiple tables by not exists operator then it is possible in Serenity ?

[TheDelusioned]

I am also trying to figure this out. I am trying to return rows from TableA that do not have an existing entry in TableB.

This gives me the results I want but I don't know how to translate to Serenity.Data.SqlQuery:

SELECT TableA.* FROM dev.TableA
WHERE NOT EXISTS
(SELECT TableB.bId FROM dev.TableB WHERE TableA.aId = TableB.aId)

[TheDelusioned]

I can also get the results I am looking for with:

SELECT TableA.* FROM dev.TableA
WHERE TableA.aId NOT IN
(SELECT TableB.aId FROM dev.TableB)

I tried the following:

protected override void PrepareQuery(SqlQuery query)
{
var aFld = TableARow.Fields;
var bFld = TableBRow.Fields;

SqlQuery subquery = new SqlQuery();
subquery
    .From(bFld)
    .Select(bFld.aId);

query
    .Select("*")
    .Where(new Criteria(aFld.aId).NotIn(subquery));

}

It gets me close with:

SELECT * FROM [dev].[TableA] T0
WHERE (T0.[aId] NOT IN SELECT T0.[aId] FROM [dev].[TableB] T0)

If I could get the "(" after the WHERE statement to move to after the NOT IN statement I think it would work?

I'm sure it's obvious I'm a noob with Serenity. My apologies up front.

[volkanceylan]

query.Where(fld.aId.NotIn(query.SubQuery().From(bFld).Select(bFld.aId)))