Anyone got an idea how to get this SQL into a PredicateExpression? A link to a similar post would be appreciated, I'm all googled out.

SELECT 
     AccountTypeID, AccountCode
FROM 
     TableA 
WHERE 
     AccountStatus = 'Active' 
AND 
    AccountTypeID =  1
AND 
    SubAccountCode IS NULL
AND 
     AccountCode IN 
(
   SELECT 
       ac.AccountCode 
   FROM TableB  b, TableA a 
   WHERE 
       b.UserID =  1
    AND 
       a.AccountID = b.AccountID 
    AND a.UserActive = 1
)

in your sub-select the field ac.AccountCode is aliased incorrectly. it should either be a.AccountCode or b.AccountCode. I chose b for simplicity's sake...

I adjusted the query some as the FieldCompareSetPredicate doesn't access PredicateExpressions, only Predicates.

SELECT   AccountTypeID, 
                AccountCode
FROM    TableA 
WHERE  AccountStatus = 'Active' 
   AND     AccountTypeID = 1
   AND    SubAccountCode IS NULL
   AND    AccountCode IN (
                        SELECT   ac.AccountCode 
                        FROM        TableB b inner join TableA a on a.AccountId = b.AccountId and b.UserId = 1
                        WHERE      a.UserActive = 1
              )

I'm using the Adapter method. I wrote this from memory, so I'm not 100% sure about the syntax and overloads.

int userId = 1;
int userActive = 1;
int AccountTypeId = 1;
string accountStatus = "active";

IRelationCollection relations = new RelationCollection(TableBEntity.Relations.TableAonAccountId);
relations[0].CustomFilter = new PredicateExpression(TableBFields.UserId == userId);

IPredicate inClause = new FieldCompareSetPredicate(
           TableAFields.AccountCode, null,
           TableBFields.AccountCode, null,
           SetOperator.In,
           relations,
           TableBFields.UserId == userId);

IEntityFields fields = ResultSet(2);
fields[0] = TableAFields.AccountTypeId;
fields[1] = TableAFields.AccountCode;

IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.PredicateExpression.Add(TableAFields.AccountStatus == accountStatus);
bucket.PredicateExpression.Add(TableAFields.AccountTypeId == AccountTypeId);
bucket.PredicateExpression.Add(TableAFields.SubAccountCode == DbNull.Value);
bucket.PredicateExpression.Add(InClause);

DataTable results = new DataTable();
using(IDataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, results, bucket);
}
return results;

Jason,

Thank you very much! This is exactly what was needed. Your memory was good too, as there was only one parameter that had to change places to get it working.

Once again these forums have been a great resource!