Using Dapper with F#

If you were to make a post on the F# subreddit, or log in to the offical Slack channel for the language, and ask which technology or framework you should use for interacting with a SQL database in F#, you would be instantly inundated with recommendations to use the offical SQL Type Provider. Newbies and grizzled veterans alike love proselytizing on behalf of type providers, and for good reason; type providers are certainly a unique and powerful part of the F# language, and you won't find much like it in any other programming language.

Sometimes, though, you can't use the SQL type provider for one reason or another. That's typically been the case for me, where the type provider just plain doesn't work in a lot of cases (and to be honest I'm still not sure if type providers are supported in dotnet core).

If you're writing a CRUD application in F#, and you're not able to use a type provider, what are the alternatives? Personally I'm not a huge fan of using ORMs like EntityFramework -- I find they often confuse and obfuscate the actual SQL that's being sent behind the scenes, and can easily lead to performance issues if you're not familiar with the framework. That's why I almost always reach for Dapper, StackExchange's simple object mapper for .NET applications.

Dapper is a hugely popular package, with over ten thousand stars on GitHub at the time of this writing. To put it simply, Dapper lets you write raw SQL, and maps the result to a class or type. It also handles things like parameter sanitization, inserting lists of objects, mapping a result to different types, and so on. Of course there are tons of pros and cons to using Dapper and using a big ORM like EntityFramework, which I won't get into. For the rest of this post, I'm going to assume that you've weighed those pros and cons and decided that Dapper is the right fit for your usecase.

In this post, I'll show you how you can quickly get off the ground and using Dapper in your F# application. We're going to build a small class library which contains database calls for getting, listing, counting, creating, updating and deleting an object. Because F# is another .NET language just like C#, you'll even be able to use this class library in your C# applications and programs! If you're a C# developer, or if you're working in a predominantly C# shop, this is an excellent way to get started with F#, by putting together a comparitively small tool and using it from C#.

Let's begin!

Creating a class library project and installing Dapper

First thing's first, we need to create a new class library project. I'm going to use .NET Standard, so I'll create the project with the dotnet command line tool. Remember to specify the F# language when creating the project, or else you'll get a boring old C# project by default!

dotnet new classlib --name DapperExample --language f#

Once the project has been created, we need to install the Dapper package. I'll try to stick as close to "regular" .NET practices as possible in this article, which means we'll use plain old Nuget for managing packages. Many F# developers use something called Paket for this task, but it's not a requirement.

From your command line, in the same folder that you created the new class library project, add Dapper using the dotnet CLI:

dotnet add package dapper

Once that's done installing we can start modeling the object we're going to be storing in our database.

Modeling the data

For this example, let's say we're going to be storing employee records in our SQL database. Each record is going to have a FirstName, LastName and HiredDate. To take advantage of F# and show off some of the considerations you'll have to keep in mind (when compared to C#), let's pretend we also want to store whether an employee can work saturdays, sundays, or both. We'll use a union type to represent each employee's availability:

type WeekendAvailability =
    | None
    | Saturdays
    | Sundays
    | SaturdaysAndSundays

Pretty simple! Thanks to F#'s union types, we can quickly model weekend availability in just 5 lines of code. Now admittedly this is a pretty simple union type, and you could model it just as easily in C# with a simple enum. Where F# really differs in domain modeling is its ability to store values with union types, which itself has some special considerations when mapping the data to a SQL table.

For the sake of completeness, let's also say that we need to store each employee's health insurance plan and rate, since this is America where we arbitrarily decided to tie health care to employers! We'll use another union type to represent each plan level and the rate the employee pays for it:

type HealthCareRate = decimal
type HealthCarePlan =
    | Bronze of HealthCareRate
    | Silver of HealthCareRate
    | Gold of HealthCareRate
    | Platinum of HealthCareRate

While C# could easily model the employee's weekend availability with an enum, it's not possible to be as succint as F# once the union type includes data (the plan's rate in this case). In C#, you'd probably create an enum for the health care plan level (bronze, silver, etc), and then a class with a Level property and a Rate property.

With the two union types defined, let's put together the rest of the employee model. This is what we're going to be storing in the SQL database, where one row is one employee:

[<CLIMutable>]
type Employee =
    {
        Id: int
        FirstName: string
        LastName: string
        StartDate: DateTime
        Availability: WeekendAvailability
        HealthCare: HealthCarePlan
    }

Notice that the record type has been marked with the CLIMutable attribute. Since all F# record types are immutable by default, and because they don't have empty constructors when used from C#, they must be marked with this attribute to work properly with Dapper. Dapper doesn't know anything about the generic types you pass to it (which we'll be doing in this example), so it constructs a new instance of the type and tries to "blindly" assign properties to it.

If it can't construct an empty instance, and if the properties are immutable, then Dapper won't be able to work with your types. The CLIMutable attribute fixes that while still maintaining immutability in your own F# code.

Creating the SQL database table

With the employee data and union types modeled, we can quickly set up the SQL table to hold employee records. The only special consideration we need to take into account is how we want to store the F# union types that contain extra data, since that doesn't easily map to a single column.

Generally when it comes to storing union types with data, you have two choices: serialize the whole thing to JSON, or break out the component parts of the union type into different columns. Personally I think serializing a value to JSON just to fit it in a SQL column is messy and makes the data harder to query -- despite Microsoft's efforts at making querying JSON in SQL a more pleasant experience. As a rule, I like to break my union types into component parts and store them in separate columns.

In the case of our HealthCarePlan union type, there are two component parts: the type of the plan (Bronze, Silver, Gold, Platinum) and the value of the plan. Those are our two separate columns, and we can easily set up our database functions to split the union type into those columns when inserting or updating, and join the back together when querying.

The other union type, WeekendAvailability, contains no extra data, so in essence it's just a glorified enum and can easily fit in one SQL column.

Putting it all together, here's the SQL script for creating a table that's ready to handle our employee records:

CREATE TABLE Employees_Sample (
    Id int IDENTITY(1,1) PRIMARY KEY,
    FirstName nvarchar(max) NOT NULL,
    LastName nvarchar(max) NOT NULL,
    StartDate datetime2 NOT NULL,
    Availability nvarchar(100) NOT NULL,
    HealthCareType nvarchar(100) NOT NULL,
    HealthCareRate decimal(18,2) NOT NULL
);

While we don't have to worry about it in this particular example, things do start to get more complicated when your union type cases have different types of data. Consider this contrived union type, which tracks the employee's current employment status (we won't be using this in the tutorial, this is just an example):

type StartDate = System.DateTime
type EndDate = System.DateTime

type EmploymentStatus =
    | ActivelyEmployed of StartDate
    | PreviouslyEmployed of (StartDate, EndDate)

If you were to map this to a SQL table, you'd need three separate columns to store the proper data: one column for the type of employment status (ActivelyEmployed or PreviouslyEmployed), one non-nullable column for the StartDate, and one nullable column for the EndDate.

Safety first: parameterizing your SQL queries

One of the most useful things about Dapper is that you can easily pass it an object with arbitrary properties on it, and it will convert the values to SQL parameters. Parameterizing your SQL values not only protects you from SQL injection attacks, but Dapper is also converting the value types on that anonymous object to proper SQL types. Dapper knows that an int in C# is an int in SQL, but it also knows that an int64 in C# is a bigint in SQL.

If you were working with Dapper in C#, setting up a parameterized query would look something like this:

var sql = "INSERT INTO MY_TABLE (Name, DateOfBirth) VALUES (@name, @dob)";
var result = await sqlConnection.ExecuteAsync(sql, new
{
    name = "Jane Doe",
    dob = new System.DateTime(...)
});

That's C#, though. Because F# does not support anonymous object, this isn't quite so easy for us. But it's pretty close! Instead of using an anonymous object, we have to use either concrete types (like a record or class), or we can pass in a dictionary (which would work just as well in C#).

The only "gotcha" is that, thanks to F#'s type inference, it will assume your dictionary is of type Dictionary<string, string> once you add "name" to the dictionary. That means you can't add the dob DateTime value to the same dictionary, because DateTime from dob and String from name are not the same type and can't go in the same dictionary.

The solution is simple, we just need to box each value in the dictionary to the object type. This solves all the qualms, and Dapper can still easily figure out which types are actually being used.

// This dictionary throws an error because the two values are not the same type
let badDict = dict [
    "name", "Jane Doe"
    "dob", System.DateTime(...)
]

// This dictionary will work because the two values are both of type Object
let goodData = dict [
    "name", box "Jane Doe"
    "dob", box (System.DateTime(...))
]

Typing box is by no means difficult, but it will get pretty repetitive as the Database module grows. Let's write a new operator that will do the boxing for us:

let inline (=>) a b = a, box b

We can use the new operator to quickly create string * obj key/value pairs in a dictionary:

let data: Dictionary<string, obj> = dict [
    "name" => "Jane Doe"
    "dob" => (System.DateTime(...))
]

The Database module

Now for the fun part! We need to create an F# module with functions for creating, getting, listing, counting, updating and deleting Employee records. More interestingly, and more relevant to the topic of F#, we'll also need a couple of utility functions to map queried SQL column values from Dapper's base dictionary-ish type to the union types we've set up. If we were to let Dapper try to figure it out on its own, we'd get an exception because it doesn't know how to map multiples columns into one type.

First thing's first, let's set up the database module itself, along with the open (using) statements and the signatures for all of the create/get/list/etc. functions we want to support. I'm also going to add a private function (only available to the module) for mapping an IDataReader to a list of employee records.

Remember, in F# the order matters! There is no hoisting or recursion, so a function can only use the functions and values that come before it.

open Dapper
open System.Data.SqlClient

module Database =
    let private mapRowsToRecords (reader: IDataReader): Employee list =
        failwith "Not implemented"

    let get (connStr: string) (id: int): Async<Employee option> =
        failwith "Not implemented"

    let list (connStr: string): Async<Employee seq> =
        failwith "Not implemented"

    let create (connStr: string) (data: Employee): Async<Employee> =
        failwith "Not implemented"

    let update (connStr: string) (id: int) (data: Employee): Async<Employee> =
        failwith "Not implemented"

    let delete (connStr: string) (id: int): Async =
        failwith "Not implemented"

A couple of things to note here: the get function will return an option, which is F#'s concept of a "null" or "nothing" value. The option type forces developers to deal with the case where a value may not exist. In this case, it's easy to imagine scenarios where the get function does not return an Employee record, such as when a record with the given id does not exist.

If you're mainly a C# developer, you're might also be wondering why each function needs the connection string variable passed to it, instead of just passing it once to a class constructor. In F#, a module is analogous to a static class, where there is no constructor. Instead of instantiating a new instance each time you want to use the Database module, you can just call it from anywhere like Database.create. Since each function exists in its own little static vaccuum, they need to be passed a connection string. At the end of this post, I'll show you how the module can be wrapped in a class, which makes it much more ergonomic when consuming the database library from C#.

Utility functions for mapping rows to Employee records and vice versa

Before we start writing SQL, there are three functions we'll want to set up for mapping SQL rows to Employee records, and mapping Employee records to SQL rows. Let's start off with the two simpler utility functions, which will map the two union types to "raw" values.

/// Maps a WeekendAvailability union type to a string which can be inserted into the database.
let mapAvailabilityToString = function
    | None -> "None"
    | Saturdays -> "Saturdays"
    | Sundays -> "Sundays"
    | SaturdaysAndSundays -> "SaturdaysAndSundays"

/// Maps a HealthCarePlan to a tuple where the first element is the name of the plan and the second is the value of the plan.
let mapHealthCareToTuple = function
    | Bronze r -> "Bronze ", r
    | Silver r -> "Silver", r
    | Gold r -> "Gold", r
    | Platinum r -> "Platinum", r

These certainly don't need to be their own functions since there isn't much to them, but because they'll be used in more than once place it's best to extrapolate the functionality out into dedicated functions. Doing so will help protect us from subtle mistakes, where e.g. we might accidentally mispell one of the strings in the create function but not the get function.

The most important function in the Database module, and indeed the most important function in this whole tutorial, is going to be the function for mapping rows to records. In a lot of cases with Dapper, this would be as easy as pie (even in F#). You just give Dapper a type and it will handle mapping the rows to that type:

IEnumerable<MyClass> classes = await connection.QueryAsync<MyClass>(sql, parameters);

Like I mentioned above, though, this becomes much more involved when you need to store and query F# union types. Instead of a simple QueryAsync, we'll need to get a data reader and use it to map raw row values to union types. Luckily this isn't super hard, but it does involve pulling in column values based on the index of a column, and then looping through every row in the data reader. We use those column indexes to get the actual value of the column.

So with those things in mind, here's what the mapRowsToRecords function looks like:

let mapRowsToRecords (reader: IDataReader): Employee list =
    // Get the indexes of each column
    let idIndex = reader.GetOrdinal "Id"
    let firstNameIndex = reader.GetOrdinal "FirstName"
    let lastNameIndex = reader.GetOrdinal "LastName"
    let startDateIndex = reader.GetOrdinal "StartDate"
    let availabilityIndex = reader.GetOrdinal "Availability"
    let planTypeIndex = reader.GetOrdinal "HealthCarePlanType"
    let planRateIndex = reader.GetOrdinal "HealthCarePlanRate"

    // Loop through each row in the reader and map it to an employee record list
    [
        while reader.Read() do
            let availability =
                match reader.GetString availabilityIndex with
                | "None" -> WeekendAvailability.None
                | "Saturdays" -> Saturdays
                | "Sundays" -> Sundays
                | "SaturdaysAndSundays" -> SaturdaysAndSundays
                | s -> failwithf "Unrecognized weekend availability type %s." s
            let healthPlan =
                match reader.GetString planTypeIndex, reader.GetDecimal planRateIndex with
                | "Bronze", d -> Bronze d
                | "Silver", d -> Silver d
                | "Gold", d -> Gold d
                | "Platinum", d -> Platinum d
                | s, _ -> failwithf "Unrecognized health care plan type %s." s

            // Map the values to an employee record and add it to the list
            yield {
                Id = reader.GetInt idIndex
                FirstName = reader.GetString firstNameIndex
                LastName = reader.GetString lastNameIndex
                StartDate = reader.GetDateTime startDateIndex
                Availability = availability
                HealthCarePlan = healthPlan
            }
    ]

We're not done with the database module yet, but that function right there is the meat of it. It contains all of the logic behind mapping raw SQL column values to F# union types using Dapper. While it's certainly more involved than just passing in a type and letting Dapper do the rest, it's by no means difficult.

You'll note that the function does return a list, but that list only represents the rows returned by whatever SQL query you execute. If your query is only selecting one single row, the list will only contain one single Employee record (or zero records if the query turns up empty).

Get, list, create, update and delete Employee records

Let's finish up the rest of our database module. Hopefully most of what follows is fairly self-explanatory, but I will point out a few things as we go along. I'll trust that you're at least passingly familiar with SQL, so hopefully the statements you see in these functions won't be too foreign.

We'll start with the get function, which will be used to get one single Employee record by the record's id. You'll see that we'll use an async computation block (code wrapped in async { }), which most of the other functions will not need to use. This block is going to get the result of the query, map it to employee records, and check if the result contained any records at all.

Whether the select result contains any records or not, the function will return an Option<Employee>. Like I mentioned above, in general F# does not have a concept of null except when working with code or packages written in other .NET languages (the ones that do have null); instead the languages uses the Option type to represent a value that either does or does not exist.

module Database =
    // ...
    // previous code snipped for brevity

    let get (connStr: string) (id: int): Async<Employee option> =
        let sql = "SELECT * FROM EMPLOYEES_SAMPLE WHERE [Id] = @id"
        // Parameterize the id into a Dictionary<string, object>
        let data = [
            "id" => id
        ]

        // Execute an async block which connects to the database and maps the result
        async {
            // Connect to the SQL database
            use conn = new SqlConnection(connStr)
            // Execute the SQL query and get a reader
            use! reader = conn.ExecuteReaderAsync(sql, data) |> Async.AwaitTask

            // Map the rows to Employee records and try to get the first element of the list, converting it to an option
            return mapRowsToRecords reader |> Seq.tryHead
        }

It's only a few lines of code, but that's all we need to query for one specific record and return an option based on whether it exists. Let's continue on with the next function for listing employee records. This one is just going to use a simple select statement to select all the records in a table and return the result.

module Database =
    // ...
    // previous code snipped for brevity

    let list (connStr: string): Async<Employee seq> =
        let sql = "SELECT * FROM EMPLOYEES_SAMPLE"

        async {
            use conn = new SqlConnection(connStr)
            use! reader = conn.ExecuteReaderAsync(sql, data) |> Async.AwaitTask

            return mapRowsToRecords reader
        }

The list function here returns an Employee seq, which is just shorthand for Sequence<Employee>. The sequence type is analagous to C#'s IEnumerable type, and in fact is compatible with that very interface.

Creating and updating records is going to involve a SQL "trick", which uses an OUTPUT INSERTED.* statement to make the insert/update operation also return the values it inserted or updated. More importantly, the create and update functions will also be mapping our entire Employee record type to a parameter dictionary. We need to use the mapAvailabilityToString and mapHealthCareToTuple functions we wrote earlier, which will ensure we don't make a mistake and end up with the create/update functions are inserting slightly different strings that would fail to deserialize.

module Database =
    // ...
    // previous code snipped for brevity

    let create (connStr: string) (data: Employee): Async<Employee>
        let sql =
            """
            INSERT INTO EMPLOYEES_SAMPLE (
                FirstName,
                LastName,
                StartDate,
                Availability,
                HealthCarePlanType,
                HealthCarePlanRate
            )
            OUTPUT INSERTED.*
            VALUES (
                @firstName,
                @lastName,
                @startDate,
                @availability,
                @healthCarePlanType,
                @healthCarePlanRate
            )
            """
        let (healthCarePlanType, healthCarePlanRate) = mapHealthCareToTuple data.HealthCarePlan
        let data = dict [
            "firstName" => data.FirstName
            "lastName" => data.LastName
            "startDate" => data.StartDate
            "availability" => mapAvailabilityToString data.Availability
            "healthCarePlanType" => healthCarePlanType
            "healthCarePlanRate" => healthCarePlanRate
        ]
        
        // Return an async block that gets the result and maps the row to a record
        async {
            use conn = new SqlConnection(connStr)
            use! reader = conn.ExecuteReaderAsync(sql, data) |> Async.AwaitTask

            return mapRowsToRecords reader |> Seq.head
        }

    let update (connStr: string) (id: int) (data: Employee): Async<Employee> = 
        let sql = 
            """
            UPDATE EMPLOYEES_SAMPLE SET
                FirstName = @firstName,
                LastName = @lastName,
                StartDate = @startDate,
                Availability = @availability,
                HealthCarePlanType = @healthCarePlanType,
                HealthCarePlanRate = @healthCarePlanRate
            WHERE [Id] = @id
            """
        let (healthCarePlanType, healthCarePlanRate) = mapHealthCareToTuple data.HealthCarePlan
        let data = dict [
            "firstName" => data.FirstName
            "lastName" => data.LastName
            "startDate" => data.StartDate
            "availability" => mapAvailabilityToString data.Availability
            "healthCarePlanType" => healthCarePlanType
            "healthCarePlanRate" => healthCarePlanRate
            "id" => id
        ]

        // Return an async block that gets the result and maps the row to a record
        async {
            use conn = new SqlConnection(connStr)
            use! reader = conn.ExecuteReaderAsync(sql, data) |> Async.AwaitTask

            return mapRowsToRecords reader |> Seq.head
        }

Once you get the hang of mapping your record types to a dictionary and then using the mapper function to convert the returned rows back to records, writing a database function becomes very quick. And there are a number of ways you could improve it to make it even shorter, such as writing a utility function for creating the row reader and automatically calling mapRowsToRecords.

Time to finish up the database module with the delete function. There are no tricks or wacky F# functions to deal with here, just a simple SQL statement that gets executed by Dapper. We don't even need to return an async block since we're not doing anything with the result.

module Database = 
    // ...
    // previous code snipped for brevity

    let delete (connStr: string) (id: int) = 
        let sql = "DELETE FROM EMPLOYEES_SAMPLE WHERE [Id] = @id"
        let data = dict [
            "id" => id
        ]
        use conn = new SqlConnection(connStr)

        // Delete the row and ignore the result
        conn.ExecuteAsync(sql, data)
        |> Async.AwaitTask
        |> Async.Ignore

Extra tweaks to make your database library play nicely with C#

And with that, the database module is complete. You can easily use it from your F# code with Database.create connStr record, or you can reference it in a C# project and use it like Database.create(connStr, record). However, there are a few roughspots when consuming F# code in a C# project. For example, F# record types are immutable, and when you try to use them from C# you have to pass in all of the arguments in the constructor rather than using object instantiation.

// This does not work, you can't set any of these properties outside of the constructor
var employee = new Employee
{
    FirstName = "Jane",
    LastName = "Doe",
    // etc
};
// This does work and is the only way to create an F# record from C#
var employee = new Employee("Jane", "Doe", DateTime.Now, ...)

A couple other rough spots:

• You need to pass in the connection string to every function, rather than once to a class constructor.
• F#'s Async and C#'s Task are not compatible, so an F# async method cannot be awaited in C#.

Luckily there are some easy tweaks we can make in our F# code to make this library feel much more C# friendly, without sacrificing its usability in F#. First, we're going to add a "PartialEmployee" class (not record), and then just convert that class to the proper Employee record wherever it's used.

In your F# database project, create a new F# file and give it a namespace rather than a module. Inside that namespace, we'll create the PartialEmployee class with getters and setters, plus an instance method to convert the class to the Employee record:

// In a different F# file. This must come after the Database module file in the project hierarchy
namespace MyDatabase.CSharp
open System
open System.Threading.Tasks

type PartialEmployee() = 
    member this.FirstName: string = "" with get, set
    member this.LastName: string = "" with get, set
    member this.StartDate: DateTime = DateTime() with get, set
    member this.Availability: Database.WeekendAvailability = Database.WeekendAvailability.None with get, set
    member this.HealthCarePlan: Database.HealthCarePlan = Database.HealthCarePlan.Bronze 0 with get, set
    /// Converts the PartialEmployee to an Employee record
    member this.ConvertToRecord (): Database.Employee = 
        {
            Id = 0
            FirstName = this.FirstName
            LastName = this.LastName
            StartDate = this.StartDate
            Availability = this.Availability
            HealthCarePlan = this.HealthCarePlan
        }

Once compiled, this PartialEmployee type in F# gets turned into a plain old C# class, which is much more friendly for C# consumers. You'll see in a moment how we'll use this class without sacrificing F# usability, but next we're going to create one more class called DatabaseWrapper, which is going to receive the database connection string in the constructor.

namespace MyDatabase.CSharp
open System
open System.Threading.Tasks

type PartialEmployee() = // snipped for brevity

type DatabaseWrapper(connStr: string) = 
    failwith "Class not implemented" 

This "shim" class will be responsible for wrapping all of our lovely F# database functions in more C#-friendly methods. Chiefly, the methods on this database wrapper class will hide the connection string parameter that every F# function requires, and it will convert the F# async result to a C# task, which can then be awaited in C#. Doing so will be super easy, we just need to call the original F# database function and pipe it to Async.StartAsTask.

namespace MyDatabase.CSharp
open System
open System.Threading.Tasks

type PartialEmployee() = // snipped for brevity

type DatabaseWrapper(connStr: string) = 
    member this.GetAsync (id: int): Task<Employee option> = 
        Database.get connStr id
        |> Async.StartAsTask

    member this.ListAsync (): Task<Employee seq> = 
        Database.list connStr
        |> Async.StartAsTask

    member this.CreateAsync (employee: PartialEmployee): Task<Employee> = 
        employee.ToRecord()
        |> Database.create connStr
        |> Async.StartAsTask

    member this.UpdateAsync (id: int) (employee: PartialEmployee): Task<Employee> = 
        employee.ToRecord()
        |> Database.update connStr id
        |> Async.StartAsTask

    member this.DeleteAsync (id: int): Task = 
        // Database.delete returns an Async<unit>, which converts into Task<unit>
        // Task<unit> is not the same as Task! Cast the result to Task with :> Task
        Database.delete connStr id
        |> Async.StartAsTask 
        :> Task

Voila! That's all it takes to wrap the F# database module in a C#-friendly class, and you didn't sacrifice any usability from F# since you can still use the original Database module. To use the wrapper from C#, you just import the database project and instantiate a new instance of the DatabaseWrapper:

var db = new MyDatabase.CSharp.DatabaseWrapper("sql connection string");
var employeeList = await db.ListAsync();