F＃在同一查询中使用join＆groupBy查询表达式

问题描述：

我决定学习如何在F＃中使用查询表达式并找到the official Microsoft documentation。警告似乎没有拼写出来，所以我敢肯定我遇到了一个简单的修复问题，但不知道为什么我得到它显示的错误。F＃在同一查询中使用join＆groupBy查询表达式

我的想法是编写一个查询，同时执行加入和分组。例如，使用他们的示例'MyDatabase'，我想我会尝试找到每个学生注册的类的数量。在编写以下查询之后，编译器不会标记警告，但是当我去运行它时会给出一个错误。

我的查询表达式：

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)

错误：

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>[email protected]() in C:\Users\JDKS\Library\query expressions.fsx:line 129 
Stopped due to error

我甚至以为我可以摆脱这个问题通过使用子查询：

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)

，但给了一个更大的错误：

> 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>[email protected]() in C:\Users\JDKS\Library\query expressions.fsx:line 144 
Stopped due to error

我的猜测是，我并不真正了解发动机罩下发生了什么事情，足以解决这个错误或让我自己找到解决方案。有没有可能在这里查询我想要的内容？有没有解决方法？

我想这是因为在查询结束的元组。尝试没有它。 –

我试过没有元组，它没有工作。然后，我先尝试分组，然后加入并成功。但这不是我想要的。你只能'groupBy''student.StudentID'，因为这是加入到'CourseSelection'的列。我正努力想要说明这一点，但我不能相信没有办法将姓名分组并直接计算课程。 – jks612

'for s in student.Name'正在尝试迭代名称中的字符，这就是为什么在第二个查询中出现此错误 –

答

当我试图运行在本地，我得到这个例外

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

我不知道你为什么会得到这个错误的少漂亮的版本。但是，如果您将GroupBy从查询中拉出，事情似乎可以正常工作。

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)

我认为你的countBy应该是'fst'而不是'snd' – jks612

这真的很奇怪，我的错误是如此之大。你运行的是F＃版本14.0.23413.0吗？ – jks612

但是这会在客户端进行分组。巨大的性能损失。 –

答

我相信你的第一个查询不工作的原因是因为groupBy student into group。

我想你实际上想写的是groupBy student.StudentID into group。

刚刚尝试过。它也给出了一个错误 – jks612

答

我发现从similar question答案：

query { 
    for student in db.Student do 

    join course in db.CourseSelection 
     on (student.StudentID = course.StudentID) 

    let count = query { 
     for c in db.CourseSelection do 
     where (c.StudentID = student.StudentID) 
     select c 
     count 
    } 


    select (student.Name, count) 
    distinct 

} 

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

这些查询表达式的基本范式仍然逃避我。

编辑：

我发现，你可以加入隐而不需要使用join操作

query { 
    for student in db.Student do 
    let count = query { 
     for course in student.CourseSelection do 
     count  
    } 

    select (student.Name, count) 
} 
|> Seq.iter (fun (name, count) -> printfn "%s has %i courses" name count)

F＃在同一查询中使用join＆groupBy查询表达式

相关推荐