CODEWITHSUNDEEP
linqlinq-to-sqlf#
Ramy
Ramy

Reputation: 21261

how to outer join in F# using FLinq?

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?

Upvotes: 9

Views: 1705

Answers (3)

Ramy
Ramy

Reputation: 21261

I ended up created separate queries for each outer join and calling that at certain points when looping through the resultset of the outermost query.

Upvotes: 0

ninegrid
ninegrid

Reputation: 1801

Perhaps you should create a view in the database that performed the left outer join, and then LINQ over that view.

Upvotes: 0

Tomas Petricek
Tomas Petricek

Reputation: 243051

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

Upvotes: 5

Related Questions