I've got the following table structure

Patient 1:n Incident n:1 Doctor

and I want to get a list of Patients with related incidents with two filters, one on an incident field and a second on doctor field. Meaning, "get me all the patients, along with their incidents, where the incidents are marked "ForSync" and the doctor has username = X"

I use the following statements

        Dim patientList As New EntityCollection(Of PatientEntity)
        Dim filter As RelationPredicateBucket
        Dim prefetchPath As IPrefetchPath2

        prefetchPath = New PrefetchPath2(CInt(EntityType.PatientEntity))
        prefetchPath.Add(PatientEntity.PrefetchPathIncident).SubPath.Add(IncidentEntity.PrefetchPathDoctor)

        filter = New RelationPredicateBucket()
        filter.Relations.Add(PatientEntity.Relations.IncidentEntityUsingPatientId)
        filter.Relations.Add(IncidentEntity.Relations.DoctorEntityUsingDoctorId)
        filter.PredicateExpression.Add(IncidentFields.ForSync = True)
        filter.PredicateExpression.AddWithAnd(DoctorFields.UserName = userName)
        
        adapter.FetchEntityCollection(patientList, filter, prefetchPath)

This, generates the following SQL :

Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT DISTINCT [ImpactServer].[dbo].[Patient].[PatientID] AS [PatientId], [ImpactServer].[dbo].[Patient].[RowGuid], [ImpactServer].[dbo].[Patient].[ExtCode], [ImpactServer].[dbo].[Patient].[FirstName], [ImpactServer].[dbo].[Patient].[LastName], [ImpactServer].[dbo].[Patient].[IsMale], [ImpactServer].[dbo].[Patient].[IsFemale], [ImpactServer].[dbo].[Patient].[ForSync] FROM (( [ImpactServer].[dbo].[Patient]  INNER JOIN [ImpactServer].[dbo].[Incident]  ON  [ImpactServer].[dbo].[Patient].[PatientID]=[ImpactServer].[dbo].[Incident].[PatientID]) INNER JOIN [ImpactServer].[dbo].[Doctor]  ON  [ImpactServer].[dbo].[Doctor].[DoctorID]=[ImpactServer].[dbo].[Incident].[DoctorID]) WHERE ( ( [ImpactServer].[dbo].[Incident].[ForSync] = @ForSync1 AND [ImpactServer].[dbo].[Doctor].[UserName] = @UserName2))
    Parameter: @ForSync1 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: True.
    Parameter: @UserName2 : String. Length: 50. Precision: 0. Scale: 0. Direction: Input. Value: "Manos".

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [ImpactServer].[dbo].[Incident].[IncidentID] AS [IncidentId], [ImpactServer].[dbo].[Incident].[RowGuid], [ImpactServer].[dbo].[Incident].[PatientID] AS [PatientId], [ImpactServer].[dbo].[Incident].[DoctorID] AS [DoctorId], [ImpactServer].[dbo].[Incident].[IncidentDate], [ImpactServer].[dbo].[Incident].[IsActive], [ImpactServer].[dbo].[Incident].[Notes], [ImpactServer].[dbo].[Incident].[ForSync] FROM [ImpactServer].[dbo].[Incident]  WHERE ( [ImpactServer].[dbo].[Incident].[PatientID] IN (@PatientId1, @PatientId2))
    Parameter: @PatientId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 17.
    Parameter: @PatientId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 16.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [ImpactServer].[dbo].[Doctor].[DoctorID] AS [DoctorId], [ImpactServer].[dbo].[Doctor].[UserName], [ImpactServer].[dbo].[Doctor].[ExtCode], [ImpactServer].[dbo].[Doctor].[FirstName], [ImpactServer].[dbo].[Doctor].[LastName], [ImpactServer].[dbo].[Doctor].[FullName] FROM [ImpactServer].[dbo].[Doctor]  WHERE ( [ImpactServer].[dbo].[Doctor].[DoctorID] IN (@DoctorId1, @DoctorId2))
    Parameter: @DoctorId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 2.
    Parameter: @DoctorId2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 1.

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.

The problem is that after the fetch, the incident property of each patient entity contains all the related incidents. I've expected to contain only the filtered related incidents based on my two criteria. Am I doing something wrong? Any help on that one?

Shouldn't you omit this relationship as it is already "added" by the defined prefetch path ?

filter.Relations.Add(PatientEntity.Relations.IncidentEntityUsingPatientId)

DvK wrote:

Shouldn't you omit this relationship as it is already "added" by the defined prefetch path ?

I suppose you're right! I reviewed the code and this seems to get me what I want:

    Dim patientList As New EntityCollection(Of PatientEntity)
    Dim prefetchPath As IPrefetchPath2

    prefetchPath = New PrefetchPath2(CInt(EntityType.PatientEntity))
    Dim element As PrefetchPathElement2
    element = prefetchPath.Add(PatientEntity.PrefetchPathIncident)
    element.Filter.Add(IncidentFields.ForSync = True)
    element.FilterRelations.Add(IncidentEntity.Relations.DoctorEntityUsingDoctorId)
    element.Filter.AddWithAnd(DoctorFields.UserName = userName)

    adapter.FetchEntityCollection(patientList, Nothing, prefetchPath)

Actually, this is the final correct implementation:

       Dim patientList As New EntityCollection(Of PatientEntity)
        Dim filter As RelationPredicateBucket
        Dim prefetchPath As IPrefetchPath2

        prefetchPath = New PrefetchPath2(CInt(EntityType.PatientEntity))
        Dim element As PrefetchPathElement2
        element = prefetchPath.Add(PatientEntity.PrefetchPathIncident) '.SubPath.Add(IncidentEntity.PrefetchPathDoctor)
        element.Filter.Add(IncidentFields.ForSync = True)
        element.FilterRelations.Add(IncidentEntity.Relations.DoctorEntityUsingDoctorId)
        element.Filter.AddWithAnd(DoctorFields.UserName = userName)

        filter = New RelationPredicateBucket()
        filter.Relations.Add(PatientEntity.Relations.IncidentEntityUsingPatientId)
        filter.Relations.Add(IncidentEntity.Relations.DoctorEntityUsingDoctorId)
        filter.PredicateExpression.Add(IncidentFields.ForSync = True)
        filter.PredicateExpression.AddWithAnd(DoctorFields.UserName = userName)
        
        adapter.FetchEntityCollection(patientList, filter, prefetchPath)

I had to apply the filters twice.