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 each header and result , but for a header that does not match in row , result is just an empty sequence and when the query results are passed to the user type, I get an error that the constructor associated with the field in row is not defined. This makes sense, because for any header that does not have a match in row , a null sequence is returned. 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 there is a way around this from the leftOuterJoin documentation here . But, when I try to use this example as a template for my request:

 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) } 

.DefaultIfEmpty() errors with

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

Is there a way I can make this join and select each row by filling in the unsurpassed rows in result using None (or some other null SQL null value) so that I can pass the entire request to my record type?

Ideally, the output for an unsurpassed string would be something like (truncated manual results 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 ToOption result on ToOption result just prints Some (seq [null]) .

Answer 1

The documentation is incorrect; C # does not have the direct equivalent of the leftOuterJoin operator, so DefaultIfEmpty used with a regular connection, but you do not need it in F # (the query builder does this translation for you - see QueryBuilder.LeftOuterJoin in the source if you are interested).

If you need the results of a traditional left join, just add an extra for loop without DefaultIfEmpty (but note that you want to select the new associated 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 the MDR_0916 records that were missing, and not special MDR_0916 values ​​with null fields, however, so you can apply the post-processing step if you need the latter.