groupby multiple columns in a F# 3.0 query

The following is an example of multiple columns being used for grouping in c# and converted to f# (overly paranoid management has made me rename everything, but I believe I have been consistent):

(TheDatabase was generated by SqlMetal, GetSummedValuesResult is a F# record type)

c#

public static class Reports
{
    public static IReadOnlyList<GetSummedValuesResult> GetSummedValues(TheDatabase db, DateTime startDate, DateTime? endDate)
    {
        var query =
            from sv in db.SomeValues

            where (sv.ADate >= startDate && sv.ADate <= (endDate ?? startDate))

            group sv by new { sv.ADate, sv.Owner.Name } into grouping

            select new GetSummedValuesResult(
                grouping.Key.ADate,
                grouping.Key.Name,
                grouping.Sum(g => g.Value)
            );

        return query.ToList();
    }
}

f#

type Reports() =
    static member GetSummedValues (db:TheDatabase) startDate (endDate:Nullable<DateTime>) =
        let endDate = if endDate.HasValue then endDate.Value else startDate

        let q = query {
            for sv in db.SomeValues do
            where (sv.ADate >= startDate && sv.ADate <= endDate)

            let key = AnonymousObject<_,_>(sv.ADate, sv.Owner.Name)
            groupValBy sv key into grouping

            select {
                ADate        = grouping.Key.Item1;
                AName        = grouping.Key.Item2;
                SummedValues = grouping.Sum (fun (g:TheDatabaseSchema.SomeValues) -> g.Value)
            }
        }

        List(q) :> IReadOnlyList<GetSummedValuesResult>

So the thing to use is Microsoft.FSharp.Linq.RuntimeHelpers.AnonymousObject

Note that you should not use the Seq module for aggregation functions!!

SummedValues  = grouping |> Seq.sumBy (fun g -> g.SomeValues)

Although this WILL WORK, it does the aggregation on the client side, rather than formulating appropriate SQL.

Leave a Comment

Hata!: SQLSTATE[HY000] [1045] Access denied for user 'divattrend_liink'@'localhost' (using password: YES)