groupby multiple columns in a F# 3.0 query

Images
Related searches

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

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

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

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?

UPDATE:

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.

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.

groupby multiple columns in a F# 3.0 query, 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� 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) } But I get a "Only parameterless constructors and initializers are supported in LINQ to Entities." exception.

I see this in first of your links, I think it is what you want:

query {
    for student in db.Student do
    groupValBy student.Name student.Age into g
    select (g, g.Key, g.Count())
}

Query groupBy multiple columns (SqlDataConnection , Query groupBy multiple columns (SqlDataConnection type provider). Querying a players table grouped by first, then by the last name and counting the number� You call.groupby()and pass the name of the column you want to group on, which is "state". Then, you use ["last_name"]to specify the columns on which you want to perform the actual aggregation. You can pass a lot more than just a single column name to.groupby()as the first argument. You can also specify any of the following:

//in F# 3.0
open Microsoft.FSharp.Linq.RuntimeHelpers
open Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter
open System.Linq

//[<CLIMutable>]
//type MyRecord = { Column1 : int; Column2 : int }
// require constructor in F#
// groupBy is not valid

type T(column1 : int, column2 : int)
    member val Colum1=colum1 with get,set
    membre val Colum2=colum2 with get,set

query {
    for d in context.table do
    groupValBy d (NewAnonymousObjectHelper(T(d.Colum1,d.Colume2))) into g
    select (g.Key)
    }

Code Editor, 2. LINQ includes five sorting operators: OrderBy, OrderByDescending, ThenBy, ThenByDescending and Reverse. 3. LINQ query syntax does not support� column-Name must be a column from the current scope of the query; there can be no columns from a query block outside the current scope. For example, if a GROUP BY clause is in a subquery, it cannot refer to columns in the outer query. SelectItems in the SelectExpression with a GROUP BY clause must contain only aggregates or grouping columns.

When using groupBy you need to select an aggregating function (e.g. count, sum, avg...).

Group rows in a table (Power Query) - Excel, aggregate a column with an aggregate function,. or perform a row operation. Group by one or more columns. Using the Query Editor ribbon : In the� group by students. studentId, name, surname, class, gender, birthdate, point We must write all column names that was listed after the group by clause like the example. We can’t use students.* after group by clause in sql.

First you have to remember that a query is translated into actual SQL at some point. It appears that linq does not support the use of multiple group keys as a Tuple<>. Therefore any transformation into Tuple<> has to be done after the database call has completed.

Second, you should be able to achieve multiple key grouping by performing multiple groupings behind each other on the respective keys:

query {
    for d1 in context.table do
    groupBy d1.col1 into g1
    for d2 in g1 do
    groupBy d2.col2 into g2
    select g2
}

Please have mercy with me if the syntax is not 100% since F# is not my native tongue :) The concept however should work just fine.

DataColumn.Expression Property (System.Data), Gets or sets the expression used to filter rows, calculate the values in a column, or create an aggregate column. In Spark 3.0, the column metadata will always be propagated in the API Column.name and Column.as. In Spark version 2.4 and earlier, the metadata of NamedExpression is set as the explicitMetadata for the new column at the time the API is called, it won’t change even if the underlying NamedExpression changes metadata.

Uses unique values from specified index / columns to form axes of the resulting DataFrame. This function does not support data aggregation, multiple values will result in a MultiIndex in the columns. See the User Guide for more on reshaping. Parameters index str or object or a list of str, optional. Column to use to make new frame’s index.

The By phrase groups rows having common values in specified column(s), much like GROUP BY in SQL. The result of a query including a By phrase is a keyed table whose key column(s) are those in the By phrase. This is well-defined because the grouping along like values ensures uniqueness of the keys.

Comments
  • Is the problem that you're grouping on a tuple, or that you're selecting one? The error seems to refer to the "output" type. Maybe select g.Count() instead and see what happens. I haven't tried 3.0...just guessing.
  • I tried selecting a constant "select (1)" and got the same thing
  • @ildjarn: g.Key is a tuple, which doesn't have a parameterless constructor. select 1 should work though, if my guess is correct.
  • @Daniel : select g.Key does not construct or initialize a new tuple, however. AFAICT, select (g.Key) would be a Tuple<Tuple<col1_t, col2_t>>, which is invoking the constructor of Tuple<'T1>; select g.Key would be a Tuple<col1_t, col2_t>, reusing an existing value instead of initializing a new one. Of course, I'm running with the assumption that the exception is being caused by the select rather than the groupBy, which may be totally incorrect. :-P
  • The parenthesis is just for grouping. It is the commas that make it into a tuple. A tuple with only one element is entered as (1,)
  • groupValBy is described as "Selects a value for each element selected so far and groups the elements by the given key." The key is this case is still just based on student.Name
  • So, why is there that student.Age? I can't believe there is no easy way to group by multiple columns in FSharp. Similar case I see now in join - how to write composite key?
  • Actually this will group only by student.Age. The first arg groupValBy is the value which will be stored, the second is the key to group by. Imagine the word by between the first and second args.
  • The example is really just for illustration, using select (g.Key) isn't useful but should work... "select (g.Count())" gives the same error.
  • Interestingly enough still get "Only parameterless constructors and initializers are supported in LINQ to Entities."
  • Seems to work. However having trouble getting anything beyond select (g.Key). select (g.Count()) gives an error and I'm not sure how to go about doing aggregation on other columns. Still playing with the idea though
  • Please add comments to help the asker understand. Otherwise this is "magic".
  • new {d.col1, d.col2} constructs a C# anonymous type; this is not valid F#.