leftOuterJoin and `.DefaultIfEmpty()` queries in F#

Question

I have a relatively simple F# query expression with a join:

let mdrQuery = 
    query {
        for header in db.CustomerDetails do
        leftOuterJoin row in db.MDR_0916
            on (header.PID = row.PID) into result
        select (result, header)
        }

This returns every header and result but for a header that has not match in row, result is just an empty sequence and, when the query results are passed to a custom Type, I get an error that a constructor associated with a field in row is not defined. This makes sense as for any header that has no match in row, a null sequence is returned. An example:

mdrQuery |> Seq.head;;
val it :
  seq<dbSchema.ServiceTypes.MDR_0916> * dbSchema.ServiceTypes.CustomerDetails
= (seq [null], CustomerDetails {ACCOUNTMANAGER = null;
                            ACCOUNTSTATUS = "XC";
                            ADDRESSLINE1 = null;
                            ADDRESSLINE2 = null;
                            ADDRESSLINE3 = null;
                            ADDRESSLINE4 = "123 PIG ROAD"...

I suspect that there's a way around this because of the leftOuterJoin documentation here. But, when I try using that example as a template for my query:

let mdrQuery = 
    query {
        for header in db.CustomerDetails do
        leftOuterJoin row in db.MDR_0916
            on (header.PID = row.PID) into result
        for row in result.DefaultIfEmpty() do
        select (result, header)
        }

the .DefaultIfEmpty() piece errors out with

error FS0039: The field, constructor or member 'DefaultIfEmpty' is not defined

Is there a way that I can make this join happen and select every row, filling the unmatched rows in result with None (or some other null SQL null value) so that the entirety of the query can be passed to my record type?

Ideally, the output for an unmatched row would be something like (truncated results created by hand below)

mdrQuery |> Seq.head;;
val it :
  seq<dbSchema.ServiceTypes.MDR_0916> * dbSchema.ServiceTypes.CustomerDetails
= (MDR_0916 {AIMExp = null;
         AP = null;
         APComp = null;
         APEng = null;
         APFine = null;
         APForl = null;...}, 
CustomerDetails {ACCOUNTMANAGER = null;
                            ACCOUNTSTATUS = "XC";
                            ADDRESSLINE1 = null;
                            ADDRESSLINE2 = null;
                            ADDRESSLINE3 = null;
                            ADDRESSLINE4 = "123 PIG ROAD"...

Edit: This question/answer is similar to mine but including ToOption result simply outputs a Some (seq [null]).

In your interactive output, `result` is not an empty sequence, but rather a sequence of one element, and that element is `null`. — Fyodor Soikin, Oct 06 '16 at 14:23
`DefaultIfEmpty` is an extension method, so you need to `open System.Linq`. — kvb, Oct 06 '16 at 15:39
Opening `System.Linq` clears the error but doesn't affect anything else. — Steven, Oct 06 '16 at 18:40

score 1 · Accepted Answer · answered Oct 07 '16 at 16:43

The documentation is wrong; in C# there is no direct equivalent of a leftOuterJoin operator, so DefaultIfEmpty is used with a normal join, but in F# you don't need this (the query builder performs this translation for you - see QueryBuilder.LeftOuterJoin in the source if you're curious).

If you want the results of a traditional left join, then just add the additional for loop without DefaultIfEmpty (but note - you want to select the newly bound row value, not the result sequence):

let mdrQuery = 
    query {
        for header in db.CustomerDetails do
        leftOuterJoin row in db.MDR_0916
            on (header.PID = row.PID) into result
        for row in result do
        select (row, header)
    }

Note that this will give you null values for MDR_0916 entries that were missing, not special MDR_0916 values with null field values, though, so you might want to apply a post-processing step if you need the latter.

Great answer and thanks for the suggestion of applying a post processing step to create the `MDR_0916` values with `null` field values. I ended up selecting only those fields from `MDR...` that I needed and rewrote how I passed it to a custom type. — Steven, Oct 10 '16 at 19:14

leftOuterJoin and `.DefaultIfEmpty()` queries in F#

1 Answers1