Dapper: F# Anonymous records support in query results

[enricosada]

F# 4.6 added anonymous record support

These are similar to C# anonymous classes, but by design, the fields are sorted by name on creation so declaration order doesnt matter ( {| X=5; Y=6|} = {| Y=6; X=5 |} )

In dapper this create an issue when are used as a result type.

// dotnet add package Dapper

open System
open System.Data.SqlClient
open Dapper

// docker pull mcr.microsoft.com/mssql/server:2017-latest-ubuntu
// docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=<YourStrong!Passw0rd>" -p 1433:1433 --name sql1 -d mcr.microsoft.com/mssql/server:2017-latest-ubuntu
let connection = @"Data Source=localhost,1433;Database=master;User=sa;Password=<YourStrong!Passw0rd>;";

use db = new SqlConnection(connection)
db.Open()

// OK, same order
let res = db.QuerySingle<{| Name: string; Tid: int; X:string; Y: int |}>("select Name='fdsf', Tid=20, X='hfds', Y=15") 

// fails but understandable, different order
let res = db.QuerySingle<{| Name: string; Tid: int; X:string; Y: int |}>("select Tid=20, Name='fdsf', X='hfds', Y=15")

// fails and unexpected, same order
let res = db.QuerySingle<{| Tid: int; Name: string; X:string; Y: int |}>("select Tid=20, Name='fdsf', X='hfds', Y=15")  

the error explain it doesnt find the constructor with right shape

Unhandled Exception: System.InvalidOperationException: A parameterless default constructor or one matching signature (System.Int32 Tid, System.String Name, System.String X, System.Int32 Y) is required for <>f__AnonymousType1611086028`4'[[System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.Int32, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.String, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e],[System.Int32, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]] materialization

As note:

// F#
let x = {| Tid = 20; Name = "fdsf"; X = "hfds"; Y = 15 |}

// IL
IL_00ea: ldstr        "fdsf"
IL_00ef: ldc.i4.s     20 // 0x14
IL_00f1: ldstr        "hfds"
IL_00f6: ldc.i4.s     15 // 0x0f
IL_00f8: newobj       instance void class '<>f__AnonymousType1611086028`4\''<string, int32, string, int32>::.ctor(!0/*string*/, !1/*int32*/, !2/*string*/, !3/*int32*/)

and

// C#
var x = new { Tid = 20, Name = "fdsf", X = "hfds", Y = 15 };

// IL
IL_0001: ldc.i4.s     20 // 0x14
IL_0003: ldstr        "fdsf"
IL_0008: ldstr        "hfds"
IL_000d: ldc.i4.s     15 // 0x0f
IL_000f: newobj       instance void class '<>f__AnonymousType0`4'<int32, string, string, int32>::.ctor(!0/*int32*/, !1/*string*/, !2/*string*/, !3/*int32*/)```

ref https://github.com/Microsoft/visualfsharp/issues/6422

[enricosada]

I'll try to address that and send a PR.

I think dapper can fallback to search for a constructor with parameters sorted by name too, if is not found with query column order.

[enricosada]

done in PR #1233 ready to review

[darting]

+1

[isaacabraham]

Just a question on this - we're using Dapper and Anonymous Records with some success in F#4.6/7. The only issue we're finding is that Dapper appears to be looking for a constructor with the parameters in a specific order, rather than simply looking for the correct parameters in any order.

This somewhat limits the utility of F# in general with Dapper since you need to align the order of field declarations on a record with the fields that come back from SQL. Is this by design?

[enricosada]

@isaacabraham it’s by design of F#

F# use .NET anonymous classe of for anonimous types, and these have constructor params by order of definition.

See the linked dotnet/fsharp issue.

Other .NET Lang doesn’t know about it. F# compiler support equality by name of fields (c# doesn’t, strict by order)

[isaacabraham]

Yep, I know. I'm just wondering about whether Dapper could match by name rather than order though.

[enricosada]

@isaacabraham that's what PR https://github.com/StackExchange/Dapper/pull/1233 fixes

[isaacabraham]

Ah, great. I got confused from the title - anonymous records are currently supported. If it's matching by name not order it's more about handling F# records better in general. Currently we are decorating records with [<CLIMutable>] to turn off the ordering restriction.

[enricosada]

@isaacabraham not really.
anonymous records are currently supported, but doesnt work always, depends on the order of the result columns.
see the issue description, there are some examples, but mostly is like

let res = db.QuerySingle<{| Tid: int; Name: string; X:string; Y: int |}>("select Tid=20, Name='fdsf', X='hfds', Y=15")  

where you think it should works (same order of anon record field and query args), but doesnt at runtime