I couldn't come up with a good subject line for this. What I'm looking for is as follows:

Entity A has a 1:1 relationship with Entity B Entity A has a 0:n relationship with Entity C

I want to return a collection of Entity A items where there are exactly 0 related Entity C items. How can I do this?

My usual routine when I want to return values where relationships do exist is:

ACollection MyAs = new ACollection();
IPredicateExpression filter = new PredicateExpression();
filter.Add(AFields.Lala=somevalue);
RelationCollection relationsToUse = new RelationCollection();
relationsToUse.Add(BEntity.Relations.AEntityUsingSomeID);
relationsToUse.Add(CEntity.Relations.AEntityUsingSomeID);
myAs.GetMulti(filter, 0, null, relationsToUse);

How can I alter this to return only those cases where CEntity.RelationsAEntityUsingSOmeID is null?

Thanks.

Laurie

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

It's the WHERE A.SomeID NOT IN (SELECT SomeID FROM C) that's I'm looking to replicate in LLBLGen.

BTW, I'm using 2.6, selfservicing.

Thanks again!

Laurie

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

For some reason, I had to use JoinHint.Right instead of Left, but it worked just fine. Thanks for the tip. (BTW, I used the second solution.)

larkydoo wrote:

For some reason, I had to use JoinHint.Right instead of Left, but it worked just fine.

Yes, that was my mistake. The JoinHint.Right worked because you added C-A relation. If you add A->C, it must be JoinHint.Left.

larkydoo wrote:

Thanks for the tip. (BTW, I used the second solution.)

Good