Left Join on option type

[MicaelMor]

Description

I wish to execute the following sql query:

SELECT "Movements".*, "Employees".*
FROM public."Movements" LEFT JOIN public."Employees" ON "Employees"."ID" = "Movements"."EmployeeId"

However, I can't seem to make this work in a query because the EmployeeId field can be null, and since I am using

useOptTypes = true

, this ofc gets converted into an Option, if I try and do something like:

query {
for m in ctx.Public.Movements do
join e in ctx.Public.Employees on (m.EmployeeId = Some(e.Id))
select (m, e)
}

The results of this query only returns values where EmployeeId is not null.

How can I create a query that returns all values from Movements, and then for Employee it returns either an option type or a potentially null Employee depending if EmployeeId is null or not?

I have also tried using the (!!) operator since it seems from what I was able to find to be the operator for outer joins, however when I try and use it say in this way:

join e in (!!) ctx.Public.Employees on (m.EmployeeId = Some(e.Id))

I get the following error:

The type 'FSharp.Data.Sql.SqlDataProvider<...>.dataContext.publicSchema.public.Employees' does not support the operator '!!'

I'm sure this must have been asked before, but I can't seem to find anything that works, so sorry if this is a duplicate.

Related information

Using Postgresql 12.4 with SQLProvider 1.1.91 and Npgsql 4.0.10

[Thorium]

The left join is done via (!!) operation and needs brackets, i.e.:

query {
 for m in ctx.Public.Movements do
 join e in (!!) (ctx.Public.Employees) on (m.EmployeeId.Value = e.Id)
 select (m, e)
}

If you have problems with a non-nullable field being null due to join and then use it in where-clause, try box(e.LastName) = null.

Edit: There might be places where comparison to "Some(x)" is not working perfectly because Option<'T> a .NET-object (PRs accepted), but databases handle quite well the null-values so using x.Value and maybe a null-check before that e.g. x.IsSome

[MicaelMor]

Thanks for the quick reply, I had tried using the .Value instead but also hadn't manage to get it working, so had assumed converting e.Id to option would be safer, but thanks for letting me know .Value is the way to go.

Anyway while I had missed the () around ctx.Public.Employees when I looked at other examples of using (!!), I still can't get it to work, since I still get the following error when using the (!!) operator:

The type 'FSharp.Data.Sql.SqlDataProvider<...>.dataContext.publicSchema.public.Employees' does not support the operator '!!' https://i.imgur.com/BJZo8kD.png (the names are different because those are the real names of the tables I'm using).

Any ideas why this error might be showing up?

[Thorium]

Have you opened the namespace FSharp.Data.Sql ?

[MicaelMor]

Well that was an embarrassingly obvious mistake on my part lol.

Thank you for the help, that fixed it.