f# sqlite sqlprovider minBy maxBy using float

Question

I have a sqlite table with a mix of integer and float columns. I'm trying to get the max and min values of each column. For integer columns the following code works but I get a cast error when using the same code on float columns:

let numCats = query{for row in db do minBy row.NumCats}

For float columns I'm using the following code but it's slow.

let CatHight = query{for row in db do select row.CatHeight} |> Seq.toArray |> Array.max

I have 8 integer columns and 9 float columns and the behavior has been consistent across all columns so that's why I think it's an issue with the column type. But I'm new to F# and don't know anything so I'm hoping you can help me.

Thank you for taking the time to help, it's much appreciated.

SQLProvider version: 1.0.41

System.Data.SQLite.Core version: 1.0.104

The error is: System.InvalidCastException occurred in FSharp.Core.dll

Added Information

I created a new table with one column of type float. I inserted the values 2.2 and 4.2. Using SQLProvider and System.Data.SQLite.Core I connected queried the database using minBy or maxBy and I get the cast exception. If the column type is integer it works correctly.

More Added Information

Exception detail:

System.Exception was unhandled Message: An unhandled exception of type 'System.Exception' occurred in >FSharp.Core.dll Additional information: Unsupported execution expression value(FSharp.Data.Sql.Runtime.QueryImplementation+SqlQueryable1[FSharp.>Data.Sql.Common.SqlEntity]).Min(row => >Convert(Convert(row.GetColumn("X"))))`

Code that fails:

open FSharp.Data.Sql

[<Literal>]
let ConnectionString =
"Data Source=c:\MyDB.db;" +
"Version=3;foreign keys=true"

type Sql = SqlDataProvider<Common.DatabaseProviderTypes.SQLITE,
ConnectionString,
//ResolutionPath = resolutionPath,
CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL>

let ctx = Sql.GetDataContext()
let Db = ctx.Main.Test

let x = query{for row in Db do minBy row.X}
printfn "x: %A" x

Update 2/1/17

Another user was able to reproduce the issue so I filed an Issue with SQLProvider. I'm now looking at workarounds and while the following code works and is fast, I know there's a better way to do it - I just can't find the correct way. If somebody answers with better code I'll accept that answer. Thanks again for all the help.

let x = query {for row in db do
                sortBy row.Column
                take 1
                select row.Column } |> Seq.toArray |> Array.min

Answer 1

This is my workaround that @s952163 and good people in the SO f# chat room helped me with. Thanks again to everyone who helped.

let x = query {for row in db do
                sortBy row.Column
                take 1
                select row.Column } |> Seq.head