FieldCompareSet filter referencing the primary table

Posts   
 
    
ww
User
Posts: 83
Joined: 01-Oct-2004
# Posted on: 23-Sep-2019 23:43:23   

I need to build something like this:


select * from table1 t1
where 
t1.field1=2 and 
not exists(select t2.field2 from table1 t2 where t2.field1=1 and t1.field4=t2.field4)

How do I build a FieldCompareSet predicate where the filter uses values from the records being selected (the "t1.field4=t2.field4" part, where t1 and t2 are the same table)?

I know the FieldCompareSet takes an alias but that only works when there's a relation collection, which there isn't in this case.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 25-Sep-2019 01:59:53   

Here is an example:


select * from Orders
Where ShipVia = 3
and  not exists(select O.OrderID from Orders O where O.ShipVia = 1 and CustomerID = O.CustomerID)

var orders = new EntityCollection<OrderEntity>();

var innerFilter = new PredicateExpression(OrderFields.ShipVia.SetObjectAlias("O") == 1);
innerFilter.Add(OrderFields.CustomerId == OrderFields.CustomerId.SetObjectAlias("O"));

var filterBucket = new RelationPredicateBucket(OrderFields.ShipVia == 3);
filterBucket.PredicateExpression.Add(
    new PredicateExpression(
        new FieldCompareSetPredicate(
            null,
            null,
            OrderFields.OrderId.SetObjectAlias("O"),
            null,
            SetOperator.Exist,
            innerFilter,
            true)));

using (var adapter = new DataAccessAdapter())
{               
    adapter.FetchEntityCollection(orders, filterBucket);
}

ww
User
Posts: 83
Joined: 01-Oct-2004
# Posted on: 25-Sep-2019 03:00:23   

Thanks! I never knew the fields had the SetObjectAlias function simple_smile