Frans & Company...
Problem:
Experiencing bug when working with Access. Access will throw an error "Join expression not supported" when doing:
FROM table1
INNER JOIN table2
ON table2.x = table1.x
LEFT JOIN table3
ON table3.x = table2.x
AND table3.x = table1.x
Access will only let one table be used in the JOIN syntax. Really, this is an Access limitation. However, it is a problem. I was writing code like this to pass to a TypedListDAO:
Dim myRelation As EntityRelation = TierEntity.Relations.FixedCostEntityUsingTierId
'Special Left Join Filters
Dim myFixedCostFilter As New PredicateExpression
myFixedCostFilter.Add(PlanFields.BenefitId = FixedCostFields.BenefitId)
myFixedCostFilter.Add(PlanFields.NetworkId = FixedCostFields.NetworkId)
'Filter left join by an additional field on another table
myRelation.CustomFilter = myFixedCostFilter
'Special Join on Fixed Costs
myRelations.Add(myRelation, "", "", JoinHint.Left)
Solution:
I did, however, come up with a solid solution... and I thought it would be helpful others. Although, I would prefer if the above code actually worked.
The key is to add your Left Join Filters to the WHERE clause... and make sure that your filters always match.
SQL we will generate:
WHERE (fixedcost.id IS NULL) OR (fixedcost.id IS NOT NULL AND (__myLeftJoinPredicates___) )
Creating WHERE Clause - Very Clean
'Special Left Join Filters
Dim myFixedCostFilter As New PredicateExpression
myFixedCostFilter.Add(PlanFields.BenefitId = FixedCostFields.BenefitId)
myFixedCostFilter.Add(PlanFields.NetworkId = FixedCostFields.NetworkId)
Dim myWhereClause as PredicateExpression = Util.getLeftJoinWHEREClause(New FixedCostEntity, myFixedCostFilter)
Function to Build Proper Predicate
Public Shared Function getLeftJoinWHEREClause(Of TEntity As {IEntity, EntityBase})(ByRef myLeftJoinEntity As TEntity, ByRef myLeftJoinFilters As PredicateExpression) As PredicateExpression
'Cannot do a LEFT JOIN ON x AND y when x and y are in different tables.
'The ON clause can only accept logic related to the join.
'RYAN'S SOLUTION: make the WHERE clause match both ways
'WHERE (fixedcost.id IS NULL) OR (fixedcost.id IS NOT NULL AND (__myPredicates___) )
Dim myFilter As New PredicateExpression
'Need a Primary Key field to determine if the record is blank or not
Dim myKey As EntityField = DirectCast(myLeftJoinEntity.PrimaryKeyFields(0), EntityField)
'IS NULL
myFilter.Add(myKey = System.DBNull.Value)
'NOT NULL
Dim myExistsFilter As New PredicateExpression
myExistsFilter.Add(myKey <> System.DBNull.Value)
myExistsFilter.AddWithAnd(myLeftJoinFilters)
'Combine IS NULL OR NOT NULL
myFilter.AddWithOr(myExistsFilter)
Return myFilter
End Function
Hope this helps someone. I do, however, wish LLBLGen would do this automatically for EntityRelation.CustomFilter when working with Access.
Thank you,
Ryan D. Hatch
