This is the next part of the C# and F# integration journey. This time we will look into Dapper functional wrapper and option vs nullable type mismatch. Together with the previous post, we will close the loop of C# and F# coexistence.
Introduction
If you are interested just in making dapper more usable in F# or conversion between F# option <-> C# Nullable then this is the article for you. However, if you are looking for how-to integrate C# with F# in general you should also read my previous article about composing dependencies in C# and F# hybrid solution.
Nullable to option, option to nullable
If one will try to return option types from C# controller it will end up with JSON like this:
1[
2 {
3 "id": 3,
4 "openedAt": "2020-06-21T12:26:48.3966667",
5 "invoicesCount": 0
6 },
7 {
8 "id": 2,
9 "openedAt": "2020-06-21T12:26:44.81",
10 "closedAt": {
11 "case": "Some", // This is how option type was serialized.
12 "fields": [
13 "2020-06-21T12:26:48.3966667"
14 ]
15 },
16 "invoicesCount": 0
17 }
18]
Not so nice or is it? Keep in mind that None is missing at all in the JSON. Let’s do some option to null conversation to achieve the following:
1[
2 {
3 "id": 3,
4 "openedAt": "2020-06-21T12:26:48.3966667",
5 "invoicesCount": 0
6 },
7 {
8 "id": 2,
9 "openedAt": "2020-06-21T12:26:44.81",
10 "closedAt": "2020-06-21T12:26:48.3966667",
11 "invoicesCount": 0
12 }
13]
The F# util module that can help us with null <-> option transformation is straightforward, let me just paste it:
1module NullableOptionUtil
2
3let ToOption (n: System.Nullable<_>) =
4 if n.HasValue then Some n.Value else None
5
6let StringToOption(n: string) =
7 if System.String.IsNullOrEmpty(n) then None else Some n
8
9let ToNullable (n: option<'a>) =
10 if n.IsSome then new System.Nullable<'a>(n.Value) else new System.Nullable<'a>()
11
The util is extra easy to use. Find the example below:
1namespace Api.Dtos
2{
3 using System;
4 using static NullableOptionUtil;
5
6 public class SaleInvociesReportDto
7 {
8 public int Id { get; set; }
9 public DateTime OpenedAt { get; set; }
10 public DateTime? ClosedAt { get; set; }
11 public int InvoicesCount { get; set; }
12
13 public static SaleInvociesReportDto FromQueryResult(Queries.SalesInvoiceReportsResult saleInvoiceReport) =>
14 new SaleInvociesReportDto
15 {
16 Id = saleInvoiceReport.Id,
17 OpenedAt = saleInvoiceReport.OpenedAt,
18 ClosedAt = ToNullable(saleInvoiceReport.ClosedAt),
19 InvoicesCount = saleInvoiceReport.InvoicesCount
20 };
21 }
22}
23
Easy peasy, but can we do better? Can we do something to stop thinking about null to option conversion? Also, I don’t like writing DTOs when I do CQRS - the query results are already prepared for reading, creating DTO had been always 1:1 copy so I just stopped doing them for the “query part”. So let’s instruct our serializer on how to deal with the option. I did it in F# but if you prefer C#, go with C#. It is heavily inspired by Lev Gorodinski’s post [1] about different converters.
1module OptionConverter
2
3open System
4open Microsoft.FSharp.Reflection
5open Newtonsoft.Json
6
7type OptionConverter() =
8 inherit JsonConverter()
9
10 override converter.CanConvert(typeParam) =
11 typeParam.IsGenericType && typeParam.GetGenericTypeDefinition() = typedefof<option<_>>
12
13 override converter.WriteJson(writer, value, serializer) =
14 let value =
15 match value with
16 | null -> null
17 | _ -> (FSharpValue.GetUnionFields(value, value.GetType()) |> snd).[0]
18 serializer.Serialize(writer, value)
19
20 override converter.ReadJson(reader, typeParam, _, serializer) =
21 let innerType = typeParam.GetGenericArguments().[0]
22 let innerType =
23 match innerType.IsValueType with
24 | true -> (typedefof<Nullable<_>>).MakeGenericType([| innerType |])
25 | _ -> innerType
26 let value = serializer.Deserialize(reader, innerType)
27 let cases = FSharpType.GetUnionCases(typeParam)
28 match value with
29 | null -> FSharpValue.MakeUnion(cases.[0], [||])
30 | _ -> FSharpValue.MakeUnion(cases.[1], [| value |])
Now you can delete the DTO, and return the query result directly without bothering about JSON representation of option type. Don’t forget to add the converter 😅
1 services.AddNewtonsoftJson(
2 options =>
3 {
4 ...
5 options.SerializerSettings.Converters.Add(new OptionConverter.OptionConverter());
6 })
Dapper in F# - small wrapper to make use of it easy
Using dapper in F# is easy but if you are about to use asynchronous IO then you will have to deal with C# Task by writing Async.AwaitTask : Task<'a> → Async<'a>. This function translates a Task value to an Async value. Dapper is itself written in C# so you know the reason.
Example pure dapper example:
1 let insertNewReportWithoutDapper (createSqlConnection: unit -> Async<SqlConnection>) newReport =
2 let (SalesInvoicesReportId reportId) = newReport.Id
3 async {
4 use! sqlConnection = createSqlConnection ()
5 do! sqlConnection.ExecuteAsync("INSERT INTO [Accounting].[SalesReport](Id, OpenedAt) VALUES(@Id, @OpenedAt)",
6 {| Id = reportId; OpenedAt = newReport.OpenedAt |})
7 |> Async.AwaitTask
8 |> Async.Ignore
9 }
With our wrapper which I will show you next it will be possible to write operations with SqlConnection in more F# friendly way which will increase code readibility. The same example with our wrapper:
1 let insertNewReport createSqlConnection newReport =
2 let (SalesInvoicesReportId reportId) = newReport.Id
3 async {
4 use! sqlConnection = createSqlConnection ()
5 do! sqlConnection |> dbParamatrizedExecute
6 "INSERT INTO [Accounting].[SalesReport](Id, OpenedAt) VALUES(@Id, @OpenedAt)"
7 {| Id = reportId; OpenedAt = newReport.OpenedAt |}
8 }
Much cleaner isn’t it? Here’s the wrapper code inspired by Roman ProvaznÃk gist [2] but with a focus on async methods.
1namespace FSharpDapperWrapper
2
3open System.Data
4open System.Data.SqlClient
5open Dapper
6
7module DapperFSharp =
8 let dbQuery<'Result> (query: string) (connection: SqlConnection): Async<'Result seq> =
9 connection.QueryAsync<'Result>(query) |> Async.AwaitTask
10
11 let dbQueryMultiple (query: string) (connection: SqlConnection): Async<SqlMapper.GridReader> =
12 connection.QueryMultipleAsync(query) |> Async.AwaitTask
13
14 let dbParametrizedQueryMultiple (query: string) (param: obj) (connection: SqlConnection): Async<SqlMapper.GridReader> =
15 connection.QueryMultipleAsync(query, param) |> Async.AwaitTask
16
17 let dbParamatrizedQuery<'Result> (query: string) (param: obj) (connection: SqlConnection): Async<'Result seq> =
18 connection.QueryAsync<'Result>(query, param) |> Async.AwaitTask
19
20 let dbParamatrizedExecute (sql: string) (param: obj) (connection: SqlConnection) =
21 connection.ExecuteAsync(sql, param)
22 |> Async.AwaitTask
23 |> Async.Ignore
24
25 let createSqlConnection connectionString =
26 OptionHandler.RegisterTypes() // This option handler translates null to None when reading, and None to null when writing. More in chapter 3.1.
27 async {
28 let connection = new SqlConnection(connectionString)
29 if connection.State <> ConnectionState.Open then do! connection.OpenAsync() |> Async.AwaitTask
30 return connection
31 }
32
33 type Dapper.SqlMapper.GridReader with
34 member reader.Read<'a>() = reader.ReadAsync<'a>() |> Async.AwaitTask
Having a factory method for SQL connection we can make our CompositionRoot described in Part 1 complete:
1Func<FSharpAsync<SqlConnection>> fSqlConnectionFactory =
2 () => DapperFSharp.createSqlConnection(_appSettings.ConnectionString);
This is the “magical” _fSqlConnectionFactory that I left without going into details in the previous post.
Mapping from null to None.
The next step to have Dapper in our functional world is about mapping null to None when reading the data (we don’t want nulls right?) and None to null when writing. The code explains itself:
1namespace FSharpDapperWrapper
2open Dapper
3open System
4
5type OptionHandler<'T> () =
6 inherit SqlMapper.TypeHandler<option<'T>> ()
7
8 override __.SetValue (param, value) =
9 let valueOrNull =
10 match value with
11 | Some x -> box x
12 | None -> null
13 param.Value <- valueOrNull
14
15 override __.Parse value =
16 if Object.ReferenceEquals(value, null) || value = box DBNull.Value
17 then None
18 else Some (value :?> 'T)
19
20module OptionHandler =
21 let RegisterTypes () =
22 SqlMapper.AddTypeHandler (OptionHandler<bool>())
23 SqlMapper.AddTypeHandler (OptionHandler<byte>())
24 SqlMapper.AddTypeHandler (OptionHandler<sbyte>())
25 SqlMapper.AddTypeHandler (OptionHandler<int16>())
26 SqlMapper.AddTypeHandler (OptionHandler<uint16>())
27 SqlMapper.AddTypeHandler (OptionHandler<int32>())
28 SqlMapper.AddTypeHandler (OptionHandler<uint32>())
29 SqlMapper.AddTypeHandler (OptionHandler<int64>())
30 SqlMapper.AddTypeHandler (OptionHandler<uint64>())
31 SqlMapper.AddTypeHandler (OptionHandler<single>())
32 SqlMapper.AddTypeHandler (OptionHandler<float>())
33 SqlMapper.AddTypeHandler (OptionHandler<double>())
34 SqlMapper.AddTypeHandler (OptionHandler<decimal>())
35 SqlMapper.AddTypeHandler (OptionHandler<char>())
36 SqlMapper.AddTypeHandler (OptionHandler<string>())
37 SqlMapper.AddTypeHandler (OptionHandler<Guid>())
38 SqlMapper.AddTypeHandler (OptionHandler<DateTime>())
39 SqlMapper.AddTypeHandler (OptionHandler<DateTimeOffset>())
40 SqlMapper.AddTypeHandler (OptionHandler<TimeSpan>())
41 SqlMapper.AddTypeHandler (OptionHandler<DateTimeOffset>())
42 SqlMapper.AddTypeHandler (OptionHandler<obj>())
Now you can use dapper in F# without worries! Let me show you two examples;
Samples:
1 let storeCustomer createSqlConnection customer =
2 async {
3 use! sqlConnection = createSqlConnection()
4 do! sqlConnection
5 |> dbParamatrizedExecute """
6INSERT INTO [Customers]
7( Id, Name, Surname, Email) VALUES
8(@Id, @Name, @Surname, @Email)
9 """ (DbCustomer.fromDomain customer)
10 }
11
12 let readCustomerWithOrders createSqlConnection customerId =
13 async {
14 use! sqlConnection = createSqlConnection()
15 let (CustomerId reportId) = customerId
16 let! resultSet = sqlConnection
17 |> dbParametrizedQueryMultiple """
18SELECT Id, Name, Surname, Email FROM [Customers] WHERE [Id] = @Id
19SELECT
20Id,
21Name,
22Price,
23OrderedDate
24FROM [Orders]
25WHERE CustomerId = @Id
26 """ {|Id = customerId|}
27 let! dbCustomer = resultSet.Read<DbCustomer>()
28 let! dbOrders = resultSet.Read<DbOrder>()
29 return (dbCustomer |> Seq.head |> DbCustomer.toDomain,
30 dbOrders |> Seq.map(fun order -> DbOrder.toDomain order)
31 |> Seq.toList
32 )
33 }
Poof… that’s it!
Summary
Handling null and option type mismatch is quite easy and intuitive. Dapper required more effort but still, these are just two files - once you have them, they just work. With the previous article about composing dependencies, you have now full power to write C# hosting project with F# projects starting from the application layer, through the domain to different adapters including querying data with dapper. May the F#orce be with you!
References:
Websites:
[1] Lev Gorodinski blog and his code about different type converters
[2] Roman ProvaznÃk gist with Dapper wrapper