I have two MS SQL tables:

Component ComponentLanguage

I want to build a dynamic list that performs a Left Join on ComponentID so that records in Component are returned in the result set even if a corresponding row in ComponentLanguage is not found.

The code below works great until I apply the LanguageCd filter to the ComponentLanguage table. Once applied all Component rows that do not have a ComponentLanguage row are dropped from the result set.

For testing I purposely changed the DB LanguageCd value on one ComponentLanguage row.

Without the filter on LanguageCd = 26 rows With filter on LanguageCd = 25 rows

If the Left join was working correctly I should get 26 rows either way except with the filter the ComponentNm and ComponentDes attributes would have a NULL value.

Am I missing something obvious?

Dim fields As New ResultsetFields(8 )
fields.DefineField(ComponentFieldIndex.ComponentID, 0, "ComponentID")
fields.DefineField(ComponentFieldIndex.ComponentCd, 1, "ComponentCd")
fields.DefineField(ComponentFieldIndex.LevelNo, 2, "LevelNo")
fields.DefineField(ComponentFieldIndex.FaultTypeCd, 3, "FaultTypeCd")
fields.DefineField(ComponentFieldIndex.SortOrderNo, 4, "SortOrderNo")
fields.DefineField(ComponentFieldIndex.ParentComponentID, 5, "ParentComponentID")
fields.DefineField(ComponentLanguageFieldIndex.ComponentNm, 6, "ComponentNm")
fields.DefineField(ComponentLanguageFieldIndex.ComponentDes, 7, "ComponentDes")

Dim relations As IRelationCollection = New RelationCollection
relations.Add(ComponentEntity.Relations.ComponentLanguageEntityUsingComponentID, JoinHint.Left)

Dim filter As IPredicateExpression = New PredicateExpression
filter.Add(PredicateFactory.CompareValue(ComponentFieldIndex.ComponentCd, ComparisonOperator.Equal, ComponentCd))
filter.AddWithAnd(PredicateFactory.CompareValue(ComponentFieldIndex.LevelNo, ComparisonOperator.Equal, LevelNo))
filter.AddWithAnd(PredicateFactory.CompareValue(ComponentFieldIndex.ActiveInd, ComparisonOperator.Equal, ActiveInd.Yes))
filter.AddWithAnd(PredicateFactory.CompareValue(ComponentLanguageFieldIndex.LanguageCd, ComparisonOperator.Equal, LanguageCd))

Dim dt As New DataTable
Dim dao As New TypedListDAO
dao.GetMultiAsDataTable(fields, dt, 0, Nothing, filter, relations, True, Nothing, Nothing, 0, 0)

The code below works great until I apply the LanguageCd filter to the ComponentLanguage table. Once applied all Component rows that do not have a ComponentLanguage row are dropped from the result set.

of course, as LanguageCd is NULL for the ones which don't hve a componentlanguage row . So you should add an Not Is Null compare to the predicate somewhere so the predicate only applies to the rows which aren't null (or at least have not null for the PK field)

Otis wrote:

of course, as LanguageCd is NULL for the ones which don't hve a componentlanguage row . So you should add an Not Is Null compare to the predicate somewhere so the predicate only applies to the rows which aren't null (or at least have not null for the PK field)

When writing SQL queries, such as the following, the row from the left joined table are not eliminated when you filter on an attribute in the right most table. The attributes receive null values when not found.

Select a.ComponentId , a.ComponentCd , b.ComponentNm -- And some additional attributes From tb_Component a, tb_ComponentLanguage b Where a.ComponentId *= b.ComponentId And a.ComponentCd = '1' And a.LevelNo = 2 And a.ActiveInd = 'Y' And b.LanguageCd = 'EN'

Results: 9042 1 NULL 9067 1 Front steer axle 9075 1 Rear axles 9086 1 Lift/Tag axles

I expected the same with the dynamic list Left Join.

In reading your response I attempted to guess what the predicate would look like. The best I can guess is the following which isn't correct and doesn't work as desired:

filter.AddWithAnd(PredicateFactory.CompareNull(ComponentLanguageFieldIndex.LanguageCd))

Is this what you had in mind? I don't see how to apply the NOT portion of your suggestion.

Paul wrote:

Otis wrote:

of course, as LanguageCd is NULL for the ones which don't hve a componentlanguage row . So you should add an Not Is Null compare to the predicate somewhere so the predicate only applies to the rows which aren't null (or at least have not null for the PK field)

When writing SQL queries, such as the following, the row from the left joined table are not eliminated when you filter on an attribute in the right most table. The attributes receive null values when not found.

Select a.ComponentId
    , a.ComponentCd
    , b.ComponentNm  -- And some additional attributes
From tb_Component a,
    tb_ComponentLanguage b
Where a.ComponentId *= b.ComponentId
And a.ComponentCd = '1'
And a.LevelNo = 2
And a.ActiveInd = 'Y'
And b.LanguageCd = 'EN'

Results:
9042    1   NULL
9067    1   Front steer axle
9075    1   Rear axles
9086    1   Lift/Tag axles

I expected the same with the dynamic list Left Join.

That's not ansi left join syntaxis. You have to specify '*' with all fields which are left joined. Could you rewrite it in ansi join syntaxis please?

select  *
from    Customers c left join orders o
    on c.customerid = o.customerid
where   o.employeeid = 6
    and o.orderid is null

gives 0 rows, leaving the last clause out, gives a lot of rows, though no customer with no orders (there are 2 with no orders in northwind) are showing up. Which is logical, as a test for '6' means you don't want nulls.

In reading your response I attempted to guess what the predicate would look like. The best I can guess is the following which isn't correct and doesn't work as desired: filter.AddWithAnd(PredicateFactory.CompareNull(ComponentLanguageFieldIndex.LanguageCd))

Is this what you had in mind? I don't see how to apply the NOT portion of your suggestion.

It has an overload which accepts a boolean, negate, pass true for that parameter

Otis wrote:

That's not ansi left join syntaxis. You have to specify '*' with all fields which are left joined. Could you rewrite it in ansi join syntaxis please?

Sorry I learned to write SQL statements in Oracle and the same syntax works in MS Sql. Everyone gives me trouble about it...guess I need to break down and join in writing ANSI SQL Queries...

Anyway, my desired SQL looks like this:

SELECT  a.ComponentID
    , a.ComponentCd
    , a.LevelNo
    , a.FaultTypeCd
    , b.ComponentNm
    , b.LanguageCd
FROM    tb_Component a LEFT OUTER JOIN
        tb_ComponentLanguage b ON a.ComponentID = b.ComponentID And b.LanguageCd = 'EN'
WHERE a.ComponentCd = '1'
And a.LevelNo = 2
And a.ActiveInd = 'Y'

Which leads to the next question:

How do I add the LanguageCd='EN' clause to the relationship?

Putting it in the filter results in this SQL which isn't what I want.

SELECT  a.ComponentID
    , a.ComponentCd
    , a.LevelNo
    , a.FaultTypeCd
    , b.ComponentNm
    , b.LanguageCd
FROM    tb_Component a LEFT OUTER JOIN
        tb_ComponentLanguage b ON a.ComponentID = b.ComponentID
WHERE a.ComponentCd = '1'
And a.LevelNo = 2
And a.ActiveInd = 'Y'
And b.LanguageCd  = 'EN'

As you said previously, this removes the desired rows because LanguageCd when it isn't = 'EN'

I'll peck away at the relationship and see if I can solve the riddle before your response.

Thanks for your prompt and curteous responses. They are always appreciated!

On a different note, do you or others at Solution Designs offer product demonstrations? One of our Divisions in the Netherlands is interested in using your LLBLGenPro product and would like enjoy a local presentation. Please email me and I'll provide more details.

Ok, I finally found the right help page that described exactly what I was trying to do:

Generated code - Filtering and sorting, SelfServicing Custom filters for EntityRelations

I'm not sure how many time I read right past this section...

It shows how to add a filter to a relationship which is what I needed.

Dim relationFilter As IPredicateExpression = New PredicateExpression
relationFilter.Add(PredicateFactory.CompareValue(ComponentLanguageFieldIndex.LanguageCd, ComparisonOperator.Equal, LanguageCd))
Dim relations As IRelationCollection = New RelationCollection
relations.Add(ComponentEntity.Relations.ComponentLanguageEntityUsingComponentID, JoinHint.Left).CustomFilter = relationFilter

I know you hear it a lot, but thanks again for your great support!

Paul wrote:

On a different note, do you or others at Solution Designs offer product demonstrations? One of our Divisions in the Netherlands is interested in using your LLBLGenPro product and would like enjoy a local presentation. Please email me and I'll provide more details.

Although I'd love to give the Dutch division of your company a presentation of LLBLGen Pro, it's not very profitable for us to do that with our current licensing scheme. (and they're not located very near us as well ). As always, I'm more than willing to provide answers to their questions .

I know you hear it a lot, but thanks again for your great support!

Thanks Paul!