larkydoo wrote:
Just in case my message is too cryptic, here is how I would accomplish this feat in plain ol' T-SQL:
SELECT * FROM A INNER JOIN B ON A.SomeID=B.SomeID
WHERE A.SomeID NOT IN (SELECT SomeID FROM C)
AND A.Lala=SomeValue
To do that, use a FieldCompareSetPredicate:
filter.Add(new FieldCompareSetPredicate(
AFields.SomeID, CFields.SomeId,
SetOperator.In, null, true));
Note that the last parameter indicates that the IN operator should be negated, so it becomes NOT IN.
You also could do this:
...
filter.Add(AFields.Lala=somevalue);
filter.Add(CFields.SomeFkToAId == DBNull.Value);
RelationCollection relationsToUse = new RelationCollection();
relationsToUse.Add(BEntity.Relations.AEntityUsingSomeID);
relationsToUse.Add(CEntity.Relations.AEntityUsingSomeID, JoinHint.Left);
myAs.GetMulti(filter, 0, null, relationsToUse);
That has the same effect on the results. The approximate SQL is:
SELECT DISTINCT A.* from A
LEFT JOIN C ON A.SomeID = C.SomeID
WHERE C.SomeID IS NULL