F# groupBy - System.Exception : unrecognized method call

Question

I'm trying to query data with F# SqlDataProvider but I got strange error when I'd like to use groupBy function

my init code :

r# "packages/FSharp.Data.2.2.5/lib/net40/FSharp.Data.dll"
r# "packages/SQLProvider.1.0.0/lib/FSharp.Data.SQLProvider.dll"
r# "packages/FSharp.Data.TypeProviders.5.0.0.2/lib/net40/FSharp.Data.TypeProviders.dll"

open FSharp.Data
open FSharp.Data.Sql
open FSharp.Data.TypeProviders
open FSharp.Linq
open System.Text.RegularExpressions
open System
open System.Data

type dbSchema = SqlDataProvider<
                    ConnectionString = "my-connection-string",
                    DatabaseVendor = Common.DatabaseProviderTypes.MSSQLSERVER,
                    IndividualsAmount = 1000,
                    UseOptionTypes = true>
let db = dbSchema.GetDataContext()

my query :

query {
    for county in db.Dbo.Countries do
    groupBy county.CountryCode into g
    select (g.Key, g.Count())
    } |> Seq.iter (fun (key, count) -> printfn "%s %d" key count)

I got this error :

System.Exception: unrecognised method call at Microsoft.FSharp.Linq.RuntimeHelpers.LeafExpressionConverter.EvaluateQuotation(FSharpExpr e) at Microsoft.FSharp.Linq.QueryModule.EvalNonNestedInner(CanEliminate canElim, FSharpExpr queryProducingSequence) at Microsoft.FSharp.Linq.QueryModule.clo@1735-1.Microsoft-FSharp-Linq-ForwardDeclarations-IQueryMethods-Executea,b at .$FSI_0003.main@() in C:\Development\CountriesParser\Script1.fsx:line 36

The line 36 is exact line of groupBy.

As I read in these pages, it should work http://fsprojects.github.io/FSharp.Linq.ComposableQuery/QueryExamples.html https://msdn.microsoft.com/en-us/library/hh225374.aspx

Answer 1

There is a number of different SQL type providers for F# and I think your code uses community-driven one that does not support the groupBy construct at the moment.

1. The type SqlDataProvider comes from SQLProvider package and is documented here.
2. The FSharp.Data.TypeProviders library (also distributed with Visual Studio) is documented here and exposes SqlDataConnection type.
3. You also mentioned ComposableQuery library documented here, which is an extension that you can use on top of (2), but you're not doing that in your code.

The library (1) is nicer in a number of ways, but does not support the full query language yet, so you'll probably need to switch to (2).

To do that, reference just FSharp.Data.TypeProviders (you do not need the other two packages) and then use the SqlDataConnection type, following the MSDN Walkthrough on the topic