F#3.0查询中的多列分组 [英] groupby multiple columns in a F# 3.0 query

问题描述

就多列分组而言，只需尝试F#3.0并碰壁.显而易见的尝试是

Just trying out F# 3.0 and hit a bit of a wall when it comes to grouping by multiple columns. The obvious thing to try was

query {
    for d in context.table do
    groupBy (d.col1,d.col2) into g
    select (g.Key)
}

但是我得到了"LINQ to Entities仅支持无参数的构造函数和初始化程序".

But I get a "Only parameterless constructors and initializers are supported in LINQ to Entities." exception.

我似乎找不到msdn上的示例

I can't seem to find an example on msdn

http://msdn.microsoft. com/en-us/library/hh225374(v = vs.110).aspx

http://msdn.microsoft. com/en-us/library/hh361035(v = vs.110).aspx

我意识到我的问题类似于 " F#中的实体框架和匿名类型"，但它似乎是Powerpack/F#2.x的重点，我希望F#3.0有一个优雅的答案...有什么想法吗?

And I realize my question is similar to " Entity Framework and Anonymous Types in F#" but it seems to be powerpack/F#2.x focused and I'm hoping F# 3.0 has an elegant answer... Any ideas?

更新:

通过阅读Brian的文章，我发现了CLIMutable属性:

I came across the CLIMutable attribute from reading Brian's post at:

http://blogs.msdn.com/b/fsharpteam/archive/2012/07/19/more-about-fsharp-3.0-language-features.aspx

我非常乐观，所以我尝试了

I was pretty optimistic so I tried

[<CLIMutable>]
type MyRecord = { Column1 : int; Column2 : int }

query {
    for d in context.table do
    groupBy {Column1 = col1; Column2 = col2} into g
    select (g.Key)
}

不幸的是，我得到了完全一样的异常.

Unfortunately I get the exact same exception.

推荐答案

以下是在c#中用于分组并转换为f#的多个列的示例(过度的偏执管理使我重命名了所有内容，但我相信我已经保持一致):

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):

(该数据库是由SqlMetal生成的，GetSummedValuesResult是F#记录类型)

(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>

所以要使用的是Microsoft.FSharp.Linq.RuntimeHelpers.AnonymousObject

请注意，您不应将Seq模块用于聚合功能！

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

尽管这样做会起作用，但它会在客户端进行聚合，而不是制定适当的SQL.

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

这篇关于F#3.0查询中的多列分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！