F# leftOuterJoin Query Expressions Where the Joined Result Is Null

I recently ran into a strange problem with a leftOuterJoin query expression in F#. In SQL (T-SQL) this would roughly be the equivalent query I was trying to create:

SELECT openTrade.*
FROM trade openTrade
LEFT JOIN trade closedTrade
ON closedTrade.isOpen = 0
AND closedTrade.type = “Sold”
AND closedTrade.transactionQuantity IS NOT NULL
AND closedTrade.shareName = openTrade.shareName
AND ABS(closedTrade.transactionQuantity) = trade.transactionQuantity
WHERE trade.isOpen = 1
AND trade.transactionQuantity IS NOT NULL
AND closedTrade IS NULL

Imagine a list of share buy and sell transactions. So here I’m trying to find all open trade transactions, where the buy transaction does not have a matching sell transaction, with the same number of shares.

Here is my final F# query expression for the Trade records:

for openTrade in trades 
  (fun t -> t.IsSome &&
       t.Value.isOpen && 
       t.Value.transaction.quantity.IsSome) do
leftOuterJoin closedTrade in trades 
  (fun t -> t.IsSome &&
            not t.Value.isOpen &&
            t.Value.type’ = TradeType.Sold &&
            t.Value.transaction.quantity.IsSome)
on ((openTrade.share.name, openTrade.transaction.quantity.Value) =   (closedTrade.share.name, (abs closedTrade.transaction.quantity.Value)))
into result
for closedTrade in result do
where (box closedTrade = null)
select openTrade }

As you can see what’s very cool about F#’s query expressions is that they have an actual left join statement, unlike C# Linq expressions.

If you pay careful attention, the next thing you will notice is that I’m not using result.DefaultIfEmpty(). Because C#’s Linq expressions don’t have a leftOuterJoin, you have to use DefaultIfEmpty with a normal join. But F#’s leftOuterJoin takes cares of this for us, contrary to what Microsoft’s incorrect F# documentation states.

Another interesting point you will notice, is that in the join statement I’m joining on two properties. If you want to join on multiple properties in query expressions, you have to create a tuple from the properties’s values, and compare the two tuples with each other.

Up to this point all the leftOuterJoin examples and documentation work as promised. That is until you try to find the records with no (null) matches. So the weird thing you will encounter is that your “where” clause throws a NullReferenceException. How can this be, because you’re applying the expression over a sequence of records?

Okay, so no problem, just check if the closedTrade is null… but, wait … F# complains because obviously record types can’t be null. But … you’re getting a NullReferenceException at runtime, on something that can’t be null, and at compile time it isn’t … what the hell!

I haven’t gotten to the bottom of the reason why this is happening, but as you can see, the trick is to box your record type, and then compare it to null.

And voila! There you have it. A neat little example of how to find all left joins where they’re null, with F# query expressions.