CLIMutable seems to be necessary for some records

[radekm]

According to README.md and already closed issue #67 adding [<CLIMutable>] to F# records isn't necessary.

But when I run the following code without [<CLIMutable>]

open Dapper
open Dapper.FSharp.SQLite
open Microsoft.Data.Sqlite

//[<CLIMutable>]
type Person = { Name : string
                Salary : decimal
                Alive : bool }

let createScript = """
    CREATE TABLE IF NOT EXISTS "Person" (
        "Name" TEXT NOT NULL,
        "Salary" REAL NOT NULL,
        "Alive" INT NOT NULL,
        PRIMARY KEY("Name")
    );
    """

[<EntryPoint>]
let main args =
    OptionTypes.register ()
    let personTable = table<Person>
    use dbConnection = new SqliteConnection("Data Source=Foo.sqlite")
    dbConnection.ExecuteAsync(createScript).GetAwaiter().GetResult() |> ignore
    printfn "Table created"

    select {
        for p in personTable do
        selectAll
    }
    |> dbConnection.SelectAsync<Person>
    |> fun t -> t.GetAwaiter().GetResult()
    |> List.ofSeq
    |> printfn "Results %A"

    0

I get the following exception

Unhandled exception. System.InvalidOperationException: A parameterless default constructor or one matching signature (System.String Name, System.Double Salary, System.Int64 Alive) is required for Program+Person materialization
   at Dapper.SqlMapper.GenerateDeserializerFromMap(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing, ILGenerator il) in /_/Dapper/SqlMapper.cs:line 3297
   at Dapper.SqlMapper.GetTypeDeserializerImpl(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in /_/Dapper/SqlMapper.cs:line 3131
   at Dapper.SqlMapper.TypeDeserializerCache.GetReader(IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in /_/Dapper/SqlMapper.TypeDeserializerCache.cs:line 151
   at Dapper.SqlMapper.TypeDeserializerCache.GetReader(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in /_/Dapper/SqlMapper.TypeDeserializerCache.cs:line 50
   at Dapper.SqlMapper.GetTypeDeserializer(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in /_/Dapper/SqlMapper.cs:line 3085
   at Dapper.SqlMapper.GetDeserializer(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in /_/Dapper/SqlMapper.cs:line 1835
   at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 426

Adding [<CLIMutable>] solves the problem. Also changing the record Person to

type Person = { Name : string
                Salary : float
                Alive : int64 }

solves the problem but that is not what I want because it defeats the point of adding type handlers. I'm using following libraries:

    <ItemGroup>
      <PackageReference Include="Dapper.FSharp" Version="4.6.1" />
      <PackageReference Include="Microsoft.Data.Sqlite" Version="7.0.10" />
    </ItemGroup>

So my question is whether there's a mistake in my code or [<CLIMutable>] needs to be used with certain records?

Maybe it has something to do with SQLite which doesn't have dedicated type for bool or for decimal?

[Dzoukr]

Hello, it seems that System.Int64 for Alive is the issue. I need to check the SQLite docs, but isn't there a more suitable type for boolean value than INT?

[radekm]

Documentation https://www.sqlite.org/datatype3.html recommends INT:

2.1. Boolean Datatype

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

SQLite recognizes the keywords "TRUE" and "FALSE", as of version 3.23.0 (2018-04-02) but those keywords are really just alternative spellings for the integer literals 1 and 0 respectively.

[radekm]

Hmm, seems that using BOOLEAN in schema definition works. Now REAL can't be converted to decimal only to float...

So following works without CLIMutable (still it would be nice to have Salary : decimal):

type Person = { Name : string
                Salary : string
                Alive : bool }

let createScript = """
    CREATE TABLE IF NOT EXISTS "Person" (
        "Name" TEXT NOT NULL,
        "Salary" TEXT NOT NULL,
        "Alive" BOOLEAN NOT NULL,
        PRIMARY KEY("Name")
    );
    """

[dungarDot]

Just wanted to jump in and say this also happens on MSSQL. In my case i'm using bool on the source table as well. I can provide more details if it helps, but they're very wide tables so it's hard to narrow down what's really causing the issue. Adding CLIMutable fixed it though.