f# 3.0 - F# query for join/group/aggregate? -

how can f# equivalent of

select a.id, avg(case when a.score = b.score 1.0 else 0.0 end) table1 join table2 b on a.id = b.id , a.date = b.date group a.id 

in query expression? i've come with

query {     in db.table1     join b in db.table2 on ((a.id, a.date) = (b.id, b.date))     groupby a.id g     select (g.key, ???) } 

but can't figure out insert "???". make things worse, "score" column can null, complicates math.

alternatively, there easier way this? i'm not familiar .net database access. ideally, i'd give block of sql, parse it, , spit typed data. is, trying figure out not-sql syntax straightforward sql pretty frustrating.

the translation sql can deal better c#-style linq operations native f# functions. easier go select , average extension methods standard f# functions seq.map , seq.average.

i tried writing similar grouping using sample northwind database - did not find nice tables join, following basic aggregation case , works fine:

open system.linq  query {     p in db.products     groupby p.categoryid.value g     select (g.key, g.select(fun ->        if a.unitprice.value > 10.0m 1.0 else 0.0).average()) } |> array.ofseq 

it generates query bit more complicated, looks right (and uses case on sql side). can see setting db.datacontext.log <- system.console.out.

another thing should work use nested query, have not tried that.