我决定学习如何在F#中使用查询表达式,并找到了Microsoft官方文档。注意事项似乎并未阐明,因此我确定我已经通过简单的修复方法解决了问题,但不知道为什么我得到了所显示的错误。
我的想法是编写一个查询,同时执行连接和分组。例如,我以他们的示例“ 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>.$FSI_0008.main@() 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>.$FSI_0015.main@() in C:\Users\JDKS\Library\query expressions.fsx:line 144
Stopped due to error
我的猜测是,我并没有真正理解幕后发生的事情,不足以解决错误或导致自己找到解决方案。是否可以在这里查询我想要的?有解决方法吗?
当我尝试在本地运行时,出现此异常
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)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句