Hello

I have two tables, namely GroupMemberships and Users. I would like to return a list of users where there the UserId cannot be found in the GroupMemberships list. I basically need the following query generated through LLBLGen.

SELECT
      tblUsers.ipkUsersID,
      tblUsers.sFirstName,
      tblUsers.sSurname
FROM
      tblUsers
WHERE
      tblUsers.ipkUsersID NOT IN
      (
      SELECT
            tblGroupMemberships.ifkUsersID
      FROM
            tblGroupMemberships
      WHERE
            tblGroupMemberships.ifkGroupsID = @ifkGroupsID
      )

I have the Users and GroupMemberships entities already defined. I am using the adapter templates.

Regards, Simon

Simon, you should use a FieldCompareSetPredicate. Ref: LLBLGen Pro Help - Generated code - Adapter - Filtering and Sorting - The predicate system - FieldCompareSetPredicate

Your code could be like this:

// collection to fill
EntityCollection<UsersEntity> Users = new EntityCollection<UsersEntity>(new UsersEntityFactory());

// filter to retrieve collection
IRelationPredicateBucket filter = new RelationPredicateBucket();

// -- SETUP THE SUBQUERY --
// tblGroupMemberships.ifkGroupsID = @ifkGroupsID
IPredicateExpression subQueryFilter = new PredicateExpression(GroupMembershipsFields.ifkGroupsID == someValue);

/// WHERE NOT ipkUsersID IN ( SELECT tblGroupMemberships.ifkUsersID 
/// FROM tblGroupMemberships WHERE tblGroupMemberships.ifkGroupsID = @ifkGroupsID )
/// Note: The last bool parameter indicate whether to negate or not. In this case result in  NOT IN
filter.PredicateExpression.Add(
    new FieldCompareSetPredicate(
        UsersFields.ipkUsersID, null,
        GroupMembershipsFields.ifkUsersID, null,
        SetOperator.In,
       subQueryFilter, true));

// data retrieval
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(Users, filter);
}

David

Hello David

I found that the EntityCollection was hanging when assigning it back to the Grid in the form. So I create a Users TypedList and then use your code and created a Non list (Negate = true) and a List (Negate = false) to return users which aren't found and users which are found. Thanks for your help!

Regards, Simon