Hi --

I know that you can write LLBLGen code to implement a correlated sub-query, however I'm not sure how to do it. (I posted a related question a while ago, but that was specific to typed lists.)

Basically, I haven't been able to figure out how to reference table "c" from the subquery using a FieldCompareSetPredicate expression.

Here's the SQL statement for what I'm trying to implement in LLBLGen:

select  distinct c.*
from
    w_container c
    join w_container_type ct on c.container_type_id = ct.container_type_id
WHERE   1=1
    AND ct.winery_id = 1
    AND NOT EXISTS 
(SELECT 1 FROM m_container2wine c2w WHERE c.container_id = c2w.container_id AND transfer_out_datetime IS NULL)

The relation from w_container to m_Container2wine is a weak 1:N relationship on container_id.

Here's the LLBLGen code I've got so far:

IRelationCollection relation = new RelationCollection();

relation.Add(DataEntities.EntityClasses.ContainerEntity.Relations.ContainerTypeEntityUsingContainerTypeId, "ct");

IPredicateExpression selectFilter = new PredicateExpression();
                selectFilter.Add(DataEntities.FactoryClasses.PredicateFactory.CompareValue( DataEntities.ContainerTypeFieldIndex.WineryId, 
 SD.LLBLGen.Pro.ORMSupportClasses.ComparisonOperator.Equal, Winery_Id, "ct"));  

// I left out the sortExpression code to save space...
objContainerCollection.GetMulti(selectFilter, 0, sortExpression, relation);

Could you provide either a pointer to documentation or some sample code for creating the right Predicate for the "AND NOT EXISTS..." SQL clause? I'm using SelfServicing code.

Thanks, -Ryan Casey

Isn't your query the same as:

select  distinct c.*
from    w_container c INNER JOIN w_container_type ct 
        on c.container_type_id = ct.container_type_id
WHERE   ct.winery_id = 1
        AND c.container_id NOT IN
        (
            SELECT container_id FROM m_container2wine 
            where transfer_out_datetime IS NULL
        )

?

In that case, the NOT IN can be formulated using a FieldCompareSetPredicate. (the Not EXISTS also, but that requires more code due to the co-relation between the two queries), and you don't need that). As you can see, you don't need an alias either in this case.

Otis wrote:

Isn't your query the same as:

select  distinct c.*
from    w_container c INNER JOIN w_container_type ct 
        on c.container_type_id = ct.container_type_id
WHERE   ct.winery_id = 1
        AND c.container_id NOT IN
        (
            SELECT container_id FROM m_container2wine 
            where transfer_out_datetime IS NULL
        )

?

Yes it is. But why would I want to do it the easy way when I can do it the hard way.

I've changed it to a NOT IN and it works perfectly. Thanks again!

For future reference, the Predicate expression for the NOT IN is:

selectFilter.Add(new FieldCompareSetPredicate(EntityFieldFactory.Create(ContainerFieldIndex.ContainerId)
    , EntityFieldFactory.Create(Container2WineFieldIndex.ContainerId)
    , SetOperator.In, PredicateFactory.CompareNull(Container2WineFieldIndex.TransferOutDatetime), true));

Glad it's solved!