Return query results based off of a foreign key relationship

Question

I'm using the SqlDataConnection type provider and I am trying to figure out the appropriate way to query a table based on query results from a related table.

I initially thought I could do something like this:

let myQuery = 
    query{
        for row in db.StuffTable do
        select row
    }

let result = 
    myQuery
    |> Seq.filter (fun x -> x.fkTable.contains( y.IsComplete = false ) // this obviously doesn't work

So I then started thinking something like this, but I am not sure:

let reuslt =
    query{
        for pkRow in pkTable do
        for fkRow in fkTable do
        where (fkRow.IsComplete = false)
        select pkRow
    }

What is the correct way?

Answer 1

You probably want to join first on the FK-PK pair of columns, then filter on the desired attributes:

let result = query {
        for fkRow in db.FkTable do 
        join pkRow in db.PkTable on (fkRow.FkColumn = pkRow.PkColumn)
        where (pkRow.IsComplete = false)
        select fkRow
    }

In the rare case where you want a full join (cartesian product of the two tables), you can do this:

let result = query {
        for fkRow in db.FkTable do 
        join pkRow in db.PkTable on (1 = 1)
        where (pkRow.IsComplete = false)
        select fkRow
    }

, or use the query the OP suggested. In this case however, for the latter query to be useful, you'd need to select something from pkTable as well.