how to outer join in F# using FLinq?

Question

question pretty much says it all. I have a big flinq query of the following form:

for alias1 in table1 do
    for alias2 in table2 do
        if  alias1.Id = alias2.foreignId

using this form, how can I do a left outer join between these two tables?

Answer 1

I think you can use the groupJoin function available in the Query module. Here is an example using Northwind with Products as the primary table and Categories as the table with foreign key:

open System.Linq

<@ Query.groupJoin 
     db.Products db.Categories 
     (fun p -> p.CategoryID.Value)
     (fun c -> c.CategoryID)
     (fun p cats ->
        // Here we get a sequence of all categories (which may be empty)
        let cat = cats.FirstOrDefault()
        // 'cat' will be either a Category or 'null' value
        p.ProductName, if cat = null then "(none)" else cat.CategoryName) @>
|> query

There are definitely nicer ways of expressing this using the seq { .. } syntax and by implementing join-like behavior using nested for loops. Unfortunatelly, the quotations to LINQ translator will probably not support these. (Personally, I would prefer writing the code using nested for and using if to check for empty collection).

I was just looking at some improvements in the PowerPack library as part of a contracting work for the F# team, so this will hopefully improve in the future... (but no promises!)