I have following tables in SQL Server.

It has One (tblHeader) to Many (tblDetail) Relationship.

TABLE : tblHeader HeaderID (PK) varchar(10) HeaderName varchar(20) ... ... ...etc

TABLE : tblDetail HeaderID (PK - composite key) varchar(10) DetailNo (PK - composite key) int DetailName varchar(20) ... ... ...etc

SELECT * FROM tblHeader
WHERE tblHeader.HeaderID IN 
       (
             SELECT tblHeader.HeaderID FROM tblHeader WHERE 
             ( 
                BuyerCode IN (SELECT CustID FROM tblCustomer WHERE CustomerName LIKE '%XYZ%') OR 
                SellerCode IN (SELECT CustID FROM tblCustomer WHERE CustomerName LIKE '%XYZ%') OR 
                AgentCode IN (SELECT CustID FROM tblCustomer WHERE CustomerName LIKE '%XYZ%') 
             ) 
         ) 

How can I do this in LLBLGen Pro.

Regards

Kaksss

You should use the FieldCompareSetPredicate to implement the IN clause. Please check the LLBLGen Pro manual "Using the generated code -> Adapter/SelfServicing -> Filtering and sorting -> The predicate system"

It should look something like:

predicateExpression.Add(new FieldCompareSetPredicate(
    HeaderFields.HeaderID, null, HeaderFields.HeaderID, null,
    SetOperator.In, innerFilter));

Construct the "innerFilter" in the same way:

innerFilter.Add(new FieldCompareSetPredicate(
    HeaderFields.BuyerCode, null, CustomerFields.CustID, null,
    SetOperator.In, (CustomerFields.CustomerName % "%XYZ%")));

innerFilter.AddWithOr(new FieldCompareSetPredicate(
    HeaderFields.SellerCode, null, CustomerFields.CustID, null,
    SetOperator.In, (CustomerFields.CustomerName % "%XYZ%")));

innerFilter.AddWithOr(new FieldCompareSetPredicate(
    HeaderFields.AgentCode, null, CustomerFields.CustID, null,
    SetOperator.In, (CustomerFields.CustomerName % "%XYZ%")));