Search code examples
linqf#query-expressions

F# Query Expression using join & groupBy in same query

I decided to learn how to use query expressions in F# and found the official Microsoft documentation. The caveats don't seem spelled out so I'm sure I've hit a problem with a simple fix but don't know why I'm getting the error it shows.

My thought was to write a query that did both a join and grouping in the same go. For example, using their sample 'MyDatabase' I thought I would try to find the number of classes that each student is enrolled in. After writing the following query, the compiler flags no warning, but when I go to run it it gives an error.

My query expression:

query {
    for student in db.Student do
    join course in db.CourseSelection
        on (student.StudentID = course.StudentID)

    groupBy student into group

    select (group.Key, group.Count())

}

|> Seq.iter (fun (student, classCount) -> printfn "Student %s has %i classes" student.Name classCount)

The error: 

System.InvalidOperationException: Could not format node 'New' for execution as SQL.
   at System.Data.Linq.SqlClient.SqlFormatter.Visitor.VisitNew(SqlNew sox)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
>    at System.Data.Linq.SqlClient.SqlFormatter.Visitor.VisitAlias(SqlAlias alias)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   at System.Data.Linq.SqlClient.SqlFormatter.Visitor.VisitSelect(SqlSelect ss)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   at System.Data.Linq.SqlClient.SqlFormatter.Visitor.VisitScalarSubSelect(SqlSubSelect ss)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   at System.Data.Linq.SqlClient.SqlFormatter.Visitor.VisitRow(SqlRow row)
   at System.Data.Linq.SqlClient.SqlFormatter.Visitor.VisitSelect(SqlSelect ss)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   at System.Data.Linq.SqlClient.SqlFormatter.Visitor.Format(SqlNode node, Boolean isDebug)
   at System.Data.Linq.SqlClient.SqlFormatter.Format(SqlNode node)
   at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(ResultShape resultShape, Type resultType, SqlNode node, ReadOnlyCollection`1 parentParameters, SqlNodeAnnotations annotations)
   at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations)
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at Microsoft.FSharp.Collections.SeqModule.Iterate[T](FSharpFunc`2 action, IEnumerable`1 source)
   at <StartupCode$FSI_0008>.$FSI_0008.main@() in C:\Users\JDKS\Library\query expressions.fsx:line 129
Stopped due to error

I even thought I could get out of this issue by using a subquery:

query {
    for student in db.Student do
    join course in db.CourseSelection
        on (student.StudentID = course.StudentID)

    let count = query {
        for s in student.Name do
        select course.CourseID
        count
    }

    select (student.Name, count)

}

|> Seq.iter (fun (student, classCount) -> printfn "Student %s has %i classes" student classCount)

but that gave an even larger error:

> System.NotSupportedException: Sequence operators not supported for type 'System.String'.
   at System.Data.Linq.SqlClient.SqlBinder.Visitor.ConvertToFetchedSequence(SqlNode node)
   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitAlias(SqlAlias a)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   at System.Data.Linq.SqlClient.SqlVisitor.VisitSource(SqlSource source)
   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSelect(SqlSelect select)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitAlias(SqlAlias a)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   at System.Data.Linq.SqlClient.SqlVisitor.VisitSource(SqlSource source)
   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSelect(SqlSelect select)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitAlias(SqlAlias a)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   at System.Data.Linq.SqlClient.SqlVisitor.VisitSource(SqlSource source)
   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSelect(SqlSelect select)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   at System.Data.Linq.SqlClient.SqlVisitor.VisitSequence(SqlSelect sel)
   at System.Data.Linq.SqlClient.SqlVisitor.VisitScalarSubSelect(SqlSubSelect ss)
   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSubSelect(SqlSubSelect ss)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitExpression(SqlExpression expr)
   at System.Data.Linq.SqlClient.SqlBinder.Visitor.FetchExpression(SqlExpression expr)
   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitNew(SqlNew sox)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitExpression(SqlExpression expr)
   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSelect(SqlSelect select)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitAlias(SqlAlias a)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   at System.Data.Linq.SqlClient.SqlVisitor.VisitSource(SqlSource source)
   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitSelect(SqlSelect select)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   at System.Data.Linq.SqlClient.SqlBinder.Visitor.VisitIncludeScope(SqlIncludeScope scope)
   at System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
   at System.Data.Linq.SqlClient.SqlBinder.Bind(SqlNode node)
   at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(ResultShape resultShape, Type resultType, SqlNode node, ReadOnlyCollection`1 parentParameters, SqlNodeAnnotations annotations)
   at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations)
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.DataQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
   at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
   at Microsoft.FSharp.Collections.SeqModule.Iterate[T](FSharpFunc`2 action, IEnumerable`1 source)
   at <StartupCode$FSI_0015>.$FSI_0015.main@() in C:\Users\JDKS\Library\query expressions.fsx:line 144
Stopped due to error

My guess is that I don't truly understand what's going on under the hood enough to work out the error or lead myself to a solution. Is it possible to query for what I want here? Is there a workaround?

Solution

  • When I tried running it locally I got this exception

    System.Exception: Grouping over multiple tables is not supported yet

    I am not sure why you would be getting the less pretty version of that error. But if you pull the GroupBy out of the query, things seem to work fine.

    query {
    for student in db.Student do
    join course in db.CourseSelection
        on (student.StudentID = course.StudentID)
    select (student.Name, course.Id)
} 
|> Seq.countBy snd
|> Seq.iter (fun (student, classCount) -> printfn "Student %s has %i classes" student classCount)