I wanted to write a filter bucket for this SQL query

Select sp.strPeriod, e.* from HREmployee e Left JOIN HRSalaryPayment sp ON (e.strEmployeeId = sp.strEmployeeId And sp.strPeriod = 'January-2006' And sp.blnIsPosted = 1)

      Dim prefetch As IPrefetchPath2 = HREmployee.CreatePrefetchPath

      prefetch.Add(HREmployee.PrefetchPathHRSalaryPaymentCollection)

      Dim relationFilter As IPredicateExpression = New PredicateExpression
      With relationFilter

         .Add( _
            PredicateFactory.CompareValue(HRSalaryPaymentFieldIndex.StrPeriod, _
                                          ComparisonOperator.Equal, _
                                          Common.DateToSalaryPeriod(Today.Date)))

         .AddWithAnd( _
            PredicateFactory.CompareValue(HRSalaryPaymentFieldIndex.BlnIsPosted, _
                                          ComparisonOperator.Equal, _
                                          True))

      End With

      Dim filter As IRelationPredicateBucket = New RelationPredicateBucket

      filter.Relations.Add( _
         HREmployee.Relations.HRSalaryPaymentEntityUsingStrEmployeeId, JoinHint.Left).CustomFilter = relationFilter

      Dim employees As IEntityCollection2 = HREmployee.FetchList(filter, prefetch, adapter)

When I inspect the objects in the (employees) collection's objects, the (HRSalaryPaymentCollection) property conatins (HRSalaryPayment) objects that do not obey the join's ON clause customFilter (all HRSalaryPayment records are prefetched). I understand that a JOIN's custom filter would work just like the way I wrote the SQL statement. Is there something wrong I am doing here?

I think what you might need is a TypedList, TypedView rather than EntityCollection with a prefetched path related entities.

What's the query generated? At first glance it looks ok what you're doing.

Otis wrote:

What's the query generated? At first glance it looks ok what you're doing.

I have two tables (HREmployee ---> (1-to-n) HRSalaryPayment). I want to retreive: * all records from HREmployee * related records from HRSalaryPayment that satisfy the condition (strPeriod='January-2006' AND blnIsPosted=True)

To do that in LLBL code: 1- build a prefetchPath for HRSalaryPayment 2- build a filterBucket that uses the HREmployee.Relations.HRSalaryPaymentEntityUsingStrEmployeeId relation with a JoinHint.Left 3- I build a predicateExpression for the required condition (strPeriod='January-2006' AND blnIsPosted=True) and add this predicateExpression as a customFilter for the relation in step (2) 4- I fetch an EntityCollection of HREmployee using the filter steps 2 and 3) and prefetch (step 1)

In the fetched entityCollection, each HREmployee object has a property (HRSalaryPaymentCollection) that should be filled with HRSalaryPayment objects that satisfy the relation and filterBucket of steps (2 and 3). The populated (HRSalaryPaymentCollection) properties DO reperesnt the weak relation with HREmployee (all HREmployee but only related HRSalaryPayment); but the (HRSalaryPaymentCollection) properties DO NOT satisfy the relation's custom filter because ALL related HRSalaryPayment records are retreived.

Ok, but what's the SQL generated? Does the left join appear in the query or is it an inner join, and is the filter added to the join or not?

Otis wrote:

Ok, but what's the SQL generated? Does the left join appear in the query or is it an inner join, and is the filter added to the join or not?

have to wait tell tomorrow morning to access my PC

ok.. here are the queries I caught from SQL-Profiler.

exec sp_executesql N'SELECT DISTINCT [Senopsis].[dbo].[HREmployee].[cntId] AS [CntId], [Senopsis].[dbo].[HREmployee].[strEmployeeId] AS [StrEmployeeId], 

 ... removed rest of column definitions to abbreviate

FROM ( [Senopsis].[dbo].[HREmployee]  
    LEFT JOIN [Senopsis].[dbo].[HRSalaryPayment]  
        ON  [Senopsis].[dbo].[HREmployee].[strEmployeeId]=[Senopsis].[dbo].[HRSalaryPayment].[strEmployeeId] 
            AND ( [Senopsis].[dbo].[HRSalaryPayment].[strPeriod] = @StrPeriod1 AND [Senopsis].[dbo].[HRSalaryPayment].[blnIsPosted] = @BlnIsPosted2))', 
N'@StrPeriod1 nvarchar(40),@BlnIsPosted2 bit', @StrPeriod1 = N'January-2006', @BlnIsPosted2 = 1


exec sp_executesql N'SELECT [Senopsis].[dbo].[HRSalaryPayment].[cntId] AS [CntId], [Senopsis].[dbo].[HRSalaryPayment].[strPeriod] AS [StrPeriod], [Senopsis].[dbo].[HRSalaryPayment].[strEmployeeId] AS [StrEmployeeId], 

  ... removed rest of column definitions to abbreviate

FROM [Senopsis].[dbo].[HRSalaryPayment]  
WHERE ( [Senopsis].[dbo].[HRSalaryPayment].[strEmployeeId] 
    IN (@StrEmployeeId1, @StrEmployeeId2, @StrEmployeeId3))', 
        N'@StrEmployeeId1 nvarchar(40),@StrEmployeeId2 nvarchar(40),@StrEmployeeId3 nvarchar(40)', 
            @StrEmployeeId1 = N'EMP-1001', @StrEmployeeId2 = N'EMP-1002', @StrEmployeeId3 = N'EMP-1003'

As you note the first query does build the join correctly, but the second query retreives records from HRSalaryPayment for the HREmployee records found in the first query. This is NOT what I want. What I want is to FETCH HREmployee and with it prefetch HRSalaryPayment records that obey the LEFT JOIN custom filter condition. Maybe there is a different way to build this using a RelationPredicateBucket object???

The goal remains, Fetch ALL HREmployee records and PREFETCH related HRSalaryPayment records that obey the condition (strPeriod = 'January-2006' And blnIsPosted = 1))

omar wrote:

Otis wrote:

What's the query generated? At first glance it looks ok what you're doing.

I have two tables (HREmployee ---> (1-to-n) HRSalaryPayment). I want to retreive: * all records from HREmployee * related records from HRSalaryPayment that satisfy the condition (strPeriod='January-2006' AND blnIsPosted=True)

To do that in LLBL code: 1- build a prefetchPath for HRSalaryPayment

No. If you want all records from HREmployee and with those fetch all salarypayment records, you should start with hremployee and create a path to fetch salarypayments. (this is a left join-esk fetch anyway).

The prefetch path then first fetches teh HREmployee entities, and after that for each HREmployee found, it fetches the available HRSalaryPayments. If no salary payment entity is available, none is loaded (which is effectively a left join).

Otis wrote:

No. If you want all records from HREmployee and with those fetch all salarypayment records, you should start with hremployee and create a path to fetch salarypayments. (this is a left join-esk fetch anyway).

The prefetch path then first fetches teh HREmployee entities, and after that for each HREmployee found, it fetches the available HRSalaryPayments. If no salary payment entity is available, none is loaded (which is effectively a left join).

How to amend this code to do what you are suggesting ??

      Dim prefetch As IPrefetchPath2 = HREmployee.CreatePrefetchPath
      prefetch.Add(HREmployee.PrefetchPathHRSalaryPaymentCollection)

      Dim relationFilter As IPredicateExpression = New PredicateExpression
      With relationFilter
         .Add( _
            PredicateFactory.CompareValue(HRSalaryPaymentFieldIndex.StrPeriod, _
                                          ComparisonOperator.Equal, _
                                          Common.DateToSalaryPeriod(Today.Date)))
         .AddWithAnd( _
            PredicateFactory.CompareValue(HRSalaryPaymentFieldIndex.BlnIsPosted, _
                                          ComparisonOperator.Equal, _
                                          True))
      End With

      Dim filter As IRelationPredicateBucket = New RelationPredicateBucket
      filter.Relations.Add( _
         HREmployee.Relations.HRSalaryPaymentEntityUsingStrEmployeeId, JoinHint.Left).CustomFilter = relationFilter

      Dim employees As IEntityCollection2 = HREmployee.FetchList(filter, prefetch, adapter)

Edit: Ok.. if I uderstood ur instructions, I amended the code as follows:

      Dim prefetch As IPrefetchPath2 = HREmployee.CreatePrefetchPath
      prefetch.Add(HREmployee.PrefetchPathHRSalaryPaymentCollection, -1, relationFilter)

      Dim employees As IEntityCollection2 = HREmployee.FetchList(filter, prefetch, adapter)

which generated the following query for HRSalaryPayment

exec sp_executesql N'SELECT [Senopsis].[dbo].[HRSalaryPayment].[cntId] AS [CntId], [Senopsis].[dbo].[HRSalaryPayment].[strPeriod] AS [StrPeriod], [Senopsis].[dbo].[HRSalaryPayment].[strEmployeeId] AS [StrEmployeeId], 

...

FROM [Senopsis].[dbo].[HRSalaryPayment]  
WHERE ( [Senopsis].[dbo].[HRSalaryPayment].[strEmployeeId] IN (@StrEmployeeId1, @StrEmployeeId2, @StrEmployeeId3) 
AND ( ( [Senopsis].[dbo].[HRSalaryPayment].[strPeriod] = @StrPeriod4 AND [Senopsis].[dbo].[HRSalaryPayment].[blnIsPosted] = @BlnIsPosted5)))', 
N'@StrEmployeeId1 nvarchar(40),@StrEmployeeId2 nvarchar(40),@StrEmployeeId3 nvarchar(40),@StrPeriod4 nvarchar(40),@BlnIsPosted5 bit', 
@StrEmployeeId1 = N'EMP-1001', @StrEmployeeId2 = N'EMP-1002', @StrEmployeeId3 = N'EMP-1003', @StrPeriod4 = N'January-2006', @BlnIsPosted5 = 1

This is the result I was looking for... NOTE: just to be sure, for the maxEntities parameter in prefetch.Add, I use (-1) to indicate I want all entities...