I am having a problem getting a left join to work in my code.. No matter what I do it seems LLBL ignores my settings and creates inner joins instead. Here is what I am trying to do:

Dim filter As New RelationPredicateBucket
filter.PredicateExpression.Add(New FieldLikePredicate(CaseFields.EmployeeLastName, Nothing, "%" & EmployeeLastNameTextBox.Text & "%"))

filter.PredicateExpression.Add(New FieldCompareValuePredicate(CasePolicyFields.IsCurrent, Nothing, ComparisonOperator.Equal, True))
filter.Relations.ObeyWeakRelations = True
'filter.Relations.Add(CaseEntity.Relations.CasePolicyEntityUsingCaseId, JoinHint.Left)
'filter.Relations.Add(CasePolicyEntity.Relations.EmployerEntityUsingEmployerId, JoinHint.Left)

CasesDs.FilterToUse = filter

I've also tried: 'filter.Relations.ObeyWeakRelations = True filter.Relations.Add(CaseEntity.Relations.CasePolicyEntityUsingCaseId, JoinHint.Left) 'filter.Relations.Add(CasePolicyEntity.Relations.EmployerEntityUsingEmployerId, JoinHint.Left)

and: 'filter.Relations.ObeyWeakRelations = True filter.Relations.Add(CaseEntity.Relations.CasePolicyEntityUsingCaseId, JoinHint.Left) filter.Relations.Add(CasePolicyEntity.Relations.EmployerEntityUsingEmployerId, JoinHint.Left)

The relationship should be such that the Case table is the main table where there has to be a case before a policy can be created and where there can be multiple policies to that case and where an employer can be linked to one or more policies. The search is intended to bring back a case where it exists searching off the name of the case or an employer linked to a case. Here is the sql statement as it should be:

SELECT DISTINCT [Online].[dbo].[Case_tbl].[CaseID] AS [CaseId], [Online].[dbo].[Case_tbl].[CaseNumber], [Online].[dbo].[Case_tbl].[EmployeeFirstName], [Online].[dbo].[Case_tbl].[EmployeeLastName], [Online].[dbo].[Case_tbl].[EmployeeIdNumber], [Online].[dbo].[Employer_tbl].[EmployerName], [Online].[dbo].[Case_Policy_tbl].[IsCurrent] FROM (( [Online].[dbo].[Case_tbl]
left JOIN [Online].[dbo].[Case_Policy_tbl]
ON [Online].[dbo].[Case_tbl].[CaseID]=[Online].[dbo].[Case_Policy_tbl].[CaseId]) left JOIN [Online].[dbo].[Employer_tbl]
ON [Online].[dbo].[Employer_tbl].[EmployerID]=[Online].[dbo].[Case_Policy_tbl].[EmployerId]) WHERE ( ( ( ( [Online].[dbo].[_Case_tbl].[EmployeeLastName] LIKE '%d%' AND [Online].[dbo].[Case_Policy_tbl].[IsCurrent] = 1 ))))

But the sql statement I get back is returning INNER JOIN(s). What am I doing wrong?

Hi nuikeoni, As you are using (I guess) a TypedList, you need to use yourTypedList.ObeyWeakRelations property. So try this:

Dim filter As New RelationPredicateBucket

filter.PredicateExpression.Add(New FieldLikePredicate(CaseFields.EmployeeLastName, Nothing, "%" & EmployeeLastNameTextBox.Text & "%"))
filter.PredicateExpression.Add(New FieldCompareValuePredicate(CasePolicyFields.IsCurrent, Nothing, ComparisonOperator.Equal, True))

CasesDs.FilterToUse = filter
CasesDs.TypedList.ObeyWeakRelations = True

Although it's preferred to set the join hints in the typedlist designer in the llblgen pro designer, unless of course you're only needing the left joins in this particular case.