F# query for join/group/aggregate?

Question

How can I get F# to do the equivalent of

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

in a query expression? I've come up with

query {
    for a in db.table1 do
    join b in db.table2 on ((a.id, a.date) = (b.id, b.date))
    groupBy a.id into g
    select (g.Key, ???) }

but I can't figure out what to insert into "???". To make things worse, the "score" column can be null, which complicates the math.

Alternatively, is there an easier way to do this? I'm not very familiar with .NET database access. Ideally, I'd just give it a block of SQL, it would parse it, and spit back some typed data. As it is, trying to figure out the not-SQL syntax for straightforward SQL is pretty frustrating.

Tomas Petricek · Answer 1 · 2014-04-17T13:27:42.823

5

The translation to SQL can generally deal better with C#-style LINQ operations than with native F# functions. So it is easier to go with Select and Average extension methods than with standard F# functions like Seq.map and Seq.average.

I tried writing a similar grouping using a sample Northwind database - I did not find nice tables to join, but the following does basic aggregation with CASE and works fine:

open System.Linq

query {
    for p in db.Products do
    groupBy p.CategoryID.Value into g
    select (g.Key, g.Select(fun a -> 
      if a.UnitPrice.Value > 10.0M then 1.0 else 0.0).Average()) }
|> Array.ofSeq

It generates a query that is a bit more complicated, but looks right (and uses CASE on the SQL side). You can see that by setting db.DataContext.Log <- System.Console.Out.

Another thing that should generally work would be to use nested query, but I have not tried that.

edited Apr 17 '14 at 13:27

answered Apr 17 '14 at 13:22

Tomas Petricek

240,744
19
378
553

2

I can confirm that nested queries work as well.`select (g.Key, query { for a in g do averageBy (if a.UnitPrice.Value > 10.0M then 1.0 else 0.0) })` – Tarmil Apr 17 '14 at 13:58
@Tarmil I still can't get this to work on my data. The join seems to complicate things. I don't seem to have Northwind installed anywhere, but can you try the equivalent nonsensical query of `select e.EmployeeID, avg(case when e.LastName = p.ProductName then 1.0 else 0.0 end) from Employees e join Products p on e.EmployeeID = p.ProductID group by e.EmployeeID`? I get illegal member access when I try to unpack `g` into a tuple of `(e, p)`, the `.Average()` and `.Select()` calls need full type specifications, and then fail on some "argument 'value' was wrong type" error. – Johann Hibschman Apr 17 '14 at 14:51
I tried something similar and I think it is a bug: https://visualfsharp.codeplex.com/workitem/44. Are you getting the same kind of error message? – Tomas Petricek Apr 17 '14 at 15:12
Yes, I get the same error message when I try to use a sub-query.When I try the `g.Select(...)` call, I need to explicitly type the call (`fun ((a1, b1): db.ServiceTypes.Table1 * db.ServiceTypes.Table2) -> ...`), and even then I get a `System.ArgumentException: The argument 'value' was the wrong type. Expected 'System.Func`2[System.Nullable`1[System.Int32],System.Nullable`1[System.Double]]'. Actual 'System.Nullable`1[System.Double]'.` error, which I don't understand at all. – Johann Hibschman Apr 17 '14 at 15:29

score 0 · Answer 2 · answered May 30 '17 at 10:59

Some years now, but never too late? Is this of any help? In particular, note the use of FirstOrDefault. Sorry for the Norwegian names, but that's not important. The "x" demonstrates access to the first table.

type Result3 = { Aarsak: int; Beskrivelse: string; Antall: int; Varighet: Nullable<int> }
let query3 = query {
    for stopptid in dc.StoppTider do
    where (stopptid.DatoS = datoS && stopptid.SkiftNr = skiftNr)
    groupBy stopptid.Aarsak into g
    join stoppaarsak in dc.StoppAarsak on (g.FirstOrDefault().Aarsak.ToString() = stoppaarsak.Nr)
    select { Aarsak = g.Key; Beskrivelse = stoppaarsak.Norsk; Antall = g.Count(); Varighet = g.Sum(fun x -> x.Varighet) }
    }

I first ended up here when googling. Since it didn't help, I googled equivalent solutions in C#, got a hit on SO, got that to work for my case in C#, then in F#. This is the link:

LINQ: combining join and group by

F# query for join/group/aggregate?

2 Answers2