F# query expression: How to do a left join and return on those records where the joined table is null?

Question

I am using SQLProvider in a project and I would like to run a query with a left join and return all records that are missing from the joined table.

I suspect the answer to this question will involve one or both of the packages FSharp.Data.TypeProviders and FSharpComposableQuery, although, to be honest, I can't tell where one ends and the other begins.

The common example of a left join in the above links is given as

query {
    for student in db.Student do
    leftOuterJoin selection in db.CourseSelection
        on (student.StudentID = selection.StudentID) into result
    for selection in result.DefaultIfEmpty() do
    select (student, selection)
}

And from what I can tell, this is equivalent to the sql:

select *
from Student s
    left outer join CourseSelection cs on s.StudentID = cs.StudentID

But what I am looking for is the F# equivalent of the sql:

select *
from Student s
    left outer join CourseSelection cs on s.StudentID = cs.StudentID
where s.StudentID is null

I realize that I can just return all records and then filter them in F#, but I want the filtering to happen on the database side where things are indexed and because, in my case especially, the number of non null records is huge, and I am only interested in the null ones.

Answer 1

I think this should do the trick:

query {
    for student in db.Student do
    leftOuterJoin selection in db.CourseSelection
        on (student.StudentID = selection.StudentID) into result
    where (not (result.Any()))
    select student
}

or a nested query:

query {
    for student in db.Student do
    where (query {
        for selection in db.CourseSelection do
        all (student.StudentID <> selection.StudentID)
    })
    select student
}

Edit: since you're using FSharp.Data.TypeProviders, if you have a foreign key between these two tables then you should also have a property that gives the associated CourseSelections, something like this:

query {
    for student in db.Student do
    where (not (student.CourseSelections.Any()))
    select student
}