Somewhat Complex IN, NOT IN query - how to do in LLBLGen

Posts   
 
    
larkydoo
User
Posts: 45
Joined: 30-Jun-2008
# Posted on: 24-May-2010 15:52:41   

I have a query that goes something like this:

SELECT * FROM A WHERE A.ThisValue=@ThisValue AND A.Bid NOT IN (SELECT Bid from B WHERE B.Cid IN (SELECT CiD FROM C WHERE C.SomeID=@SomeID))

I'm pretty sure I should use FieldCompareSetPredicate to do this but am unsure where to begin. Any help would be much appreciated.

Laurie

larkydoo
User
Posts: 45
Joined: 30-Jun-2008
# Posted on: 24-May-2010 16:03:34   

I should probably re-phrase the query, since there's an m:n relationship in there. It'd be more like this:

SELECT * FROM A WHERE A.ThisValue=@ThisValue AND Aid NOT IN (SELECT Aid FROM A_B WHERE A_B.Bid in (SELECT Bid from B WHERE B.Cid=@Cid))

Don't know if it makes much of a different, but just in case.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 24-May-2010 16:39:15   

Assuming you are using Adapter, the following is how the code should look like (not tested, but should show you the idea):

using(var adaoter = new DataAccessAdapter())
{
    var innerFilter = new PredicateExpression();

    innerFilter.Add(new FieldCompareSetPredicate(
    ABFields.BId, null, BFields.BId, null,
    SetOperator.In, (BFields.CId == XYZ)));

    var filter = new PredicateExpression(AFields.ThisValue == ABC);

    filter.Add(new FieldCompareSetPredicate(
    AFields.AId, null, ABFields.AId, null,
    SetOperator.In, innerFilter), true);

    adapter.FetchEntityCollection(col, filter);
}
larkydoo
User
Posts: 45
Joined: 30-Jun-2008
# Posted on: 25-May-2010 00:10:50   

Cool. I'm using SelfServicing, so it looks pretty similar. Not sure how/why to use null values and they generated an exception so I removed them:

myThisValue = ...;
myCid = ...;

IPredicateExpression filter = new PredicateExpression;
filter.Add(AFields.ThisValue == myThisValue);

IPredicateExpression innerFilter = new PredicateExpression();
innerFilter.Add(new FieldCompareSetPredicate(ABFields.Bid, BFields.Bid, SetOperator.In, BFields.Cid == myCid));
filter.AddWithAnd(new FieldCompareSetPredicate(AFields.Aid, ABFields.Aid, SetOperator.In, innerFilter, true));

myCollection.GetMulti(filter);
Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 25-May-2010 09:50:19   

You got it right.

The nulls are just required for the Adapter overloaded method.

larkydoo
User
Posts: 45
Joined: 30-Jun-2008
# Posted on: 25-May-2010 15:29:43   

Thanks for the help, Walaa!