I am trying to replicate the SQL code:

_SELECT     
    Agency.Name AS AgencyName,
    Agency.Institution_ID AS AgencyID 
FROM 
    Agency INNER JOIN Consortium ON     Agency.Institution_ID=Consortium.ChildInstitution_ID
WHERE 
    EXISTS (SELECT 1 
        FROM dbo.AgencyWebForm a 
        Where   agency.Institution_ID = a.Institution_ID 
            and a.SchoolYear = '2004-05' 
            And a.Status = 'Complete' )
    And 
    Consortium.ParentInstitution_ID = 2142 
GROUP BY 
    Agency.Name,
    Agency.Institution_ID 
ORDER BY 
    Agency.Name ASC_

I have created the following code to do it:

Dim oFilter As IPredicateExpression = New PredicateExpression Dim oSubFilter As IPredicateExpression = New PredicateExpression Dim oSort As SortExpression = New SortExpression Dim dtAgencyList As New DataTable Dim dao As New OSEDAL.DaoClasses.TypedListDAO Dim oFields As New OSEDAL.HelperClasses.ResultsetFields(2) Dim oRelations As IRelationCollection = New RelationCollection Dim oGroupBy As IGroupByCollection = New GroupByCollection

    '-- Define the fields we need returned, AgencyName & AgencyID
    oFields.DefineField(AgencyFieldIndex.Name, 0, "AgencyName")
    oFields.DefineField(AgencyFieldIndex.InstitutionId, 1, "AgencyID")

    '-- Define the relationship between the three entities (agency, consortium, and agnecy web form)
    oRelations.Add(AgencyEntity.Relations.ConsortiumEntityUsingChildInstitutionId, JoinHint.None)

    '-- Add another filter to return only consortium  members to the parent agency
    oFilter.AddWithAnd(PredicateFactory.CompareValue(ConsortiumFieldIndex.ParentInstitutionId, ComparisonOperator.Equal, sAgencyID))

    '-- Add a sort clause to sort on webformid
    oSort.Add(SortClauseFactory.Create(AgencyFieldIndex.Name, SortOperator.Ascending))

    '-- Add the group by clause to all columns
    oGroupBy.Add(oFields(0))
    oGroupBy.Add(oFields(1))

    '-- Define the sub filter to use in the Where Exists Clause
    '   We filter on InstitutionID, SchoolYear, and Status
    oSubFilter.Add(PredicateFactory.CompareValue(AgencyWebFormFieldIndex.InstitutionId, ComparisonOperator.Equal, AgencyFieldIndex.InstitutionId))
    oSubFilter.AddWithAnd(PredicateFactory.CompareValue(AgencyWebFormFieldIndex.SchoolYear, ComparisonOperator.Equal, sSchoolYear))
    oSubFilter.AddWithAnd(PredicateFactory.CompareValue(AgencyWebFormFieldIndex.Status, ComparisonOperator.Equal, "Complete"))

    ' Customer.CustomerID IN (SELECT CustomerID FROM Orders WHERE Employee=2) 
    oFilter.AddWithAnd(New FieldCompareSetPredicate( _
                                            Nothing, _
                                            EntityFieldFactory.Create(AgencyWebFormFieldIndex.WebFormId), _
                                            SetOperator.Exist, _
                                            oSubFilter))

    '-- Fetch the results into the dtAgencyList
    dao.GetMultiAsDataTable(oFields, dtAgencyList, 0, oSort, oFilter, oRelations, True, oGroupBy, Nothing, 0, 0)

    '-- Return the new data table
    Return dtAgencyList

The problem is that the generated SQL for the Exists clause ends up reading:

And EXISTS (SELECT [dbo].[AgencyWebForm].[WebForm_ID] AS [WebFormId] FROM [dbo].[AgencyWebForm] WHERE ( [dbo].[AgencyWebForm].[Institution_ID] = @InstitutionId2 And [dbo].[AgencyWebForm].[SchoolYear] = @SchoolYear3 And [dbo].[AgencyWebForm].[Status] = @Status4)

The [AgencyWebForm].[Institution_ID] needs to reference the Agency table in from clause of parent query and NOT a parameter value (which get set to 0 in this case).

How do I do this?

Thanks, JH

Never mind, I figured it out. I needed to use a different type of Predicate for what I was trying to do.

Thanks, JH

A FieldCompareExpression predicate (for the people who read this thread and wonder "which one?!" Glad it's solved!