How do I join tables with a condition using LLBLGen?

Question

I have the following Sql Query that returns the type of results that I want:

SELECT b.ID, a.Name, b.Col2, b.COl3
FROM Table1 a
LEFT OUTER JOIN Table2 b on b.Col4 = a.ID AND b.Col5 = 'test'

In essence, I want a number of rows equal to Table1 (a) while having the data from Table2 (b) listed or NULL if the condition, 'test', doesn't exist in Table2.

I'm rather new to LLBLGen and have tried a few things and it isn't working. I can get it to work if the condition exists; however, when a requirements change came in and caused me to rewrite the query to that above, I'm at a loss.

Below is the old LLBLGen C# code that worked for existing products but not for the above query:

LookupTable2Collection table2col = new LookupTable2Collection();

RelationCollection relationships = new RelationCollection();
relationships.Add(LookupTable2Entity.Relations.LookupTable1EntityUsingTable1ID, JoinHint.Left);

IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareValuePredicate(LookupTable2Fields.Col5, ComparisonOperator.Equal, "test"));

table2col.GetMulti(filter, relationships);

Table 1 has 3 records in it. I need the 3 records back even if all items from Table 2 are NULL because the condition doesn't exist. Any ideas?

Answer 1

Try this: -

SELECT b.ID, a.Name, b.Col2, b.Col3
FROM Table1 a
LEFT OUTER JOIN (select ID, Col2, Col3, Col4 from Table2 where Col5 = 'test') b on a.ID = b.Col4

Answer 2

You've to add your filter to the relation join like this:

relationships.Add(LookupTable2Entity.Relations.LookupTable1EntityUsingTable1ID, JoinHint.Left).CustomFilter = new FieldCompareValuePredicate(LookupTable2Fields.Col5, ComparisonOperator.Equal, "test");