What is RepoDb?

RepoDb is a “hybrid” .NET object relational mapper library that aims to hit the sweet spot between micro-ORMs such as Dapper and full-ORMs like Entity Framework. It claims to be the best alternative to both and covers advanced cases as repositories, batch/bulk operations and more, as well as your day-to-day common CRUD functionality.

Why RepoDb?

I regularly find myself in situations where I need an ORM that’s quite light-weight and I tend to go with Dapper most of the time. Yet it’s also quite common for me to eventually find myself wanting more from my ORM than what Dapper can offer. This is why RepoDb appeals to me. Besides, who wants to write SQL when you can call .NET methods to do the same thing?

In this blog post, I will demonstrate how easy it is to set yourself up with RepoDB and use F# types to insert and retrieve data from a Microsoft SQL Database, as well as performing bulk operations. If you’d like to follow up with the code, first add the NuGet packages RepoDb.SqlServer and RepoDb.SqlServer.BulkOperations to your project.

A Simple Case

Before writing any F#, I will go on and create a table in a SQL Server database to use during this demonstration.

CREATE TABLE [dbo].[Person] 
(
    [Id] UNIQUEIDENTIFIER PRIMARY KEY,
    [Name] NVARCHAR(256) NOT NULL,
    [Age] INT NOT NULL,
    [CreatedDateUtc] DATETIME NOT NULL,
);

To use RepoDb, we first need to initialise all the dependencies for SQL Server using the following lines:

open RepoDb
SqlServerBootstrap.Initialize()

Now, in order to work with the table I created above, I will also go ahead create an F# record type that matches this table one-to-one.

open System

[<CLIMutable>]
type Person =
    { Id : Guid
      Name : string
      Age : int
      CreatedDateUtc : DateTime }

Note the CLIMutable attribute. This is necessary in order to make RepoDb work with F# types. However, there currently is an issue raised about this in the RepoDb GitHub page and we’re hopeful that this might change soon.

With this type in place, there is only one thing left to do in order to start working with our database, and that is to create a connection object using a connection string to our database and ensure that it’s open.

let connection = @"Data Source=(localdb)\mssqllocaldb;Database=MyDatabase;Integrated Security=True"
let connection = new SqlConnection(connectionString)
connection.EnsureOpen()

Having done all this, I can just go ahead and insert a row into the Person table by executing the following:

connection.Insert<Person,Guid>(
    { Id = Guid.NewGuid()
      Name = "Alican Demirtas"
      Age = 22
      CreatedDateUtc = DateTime.UtcNow }
)

Note that the first type parameter is the type of the record we’re inserting and the second is the type of its Id. Also note that this operation will return the value for the Id column back.

You can also query the database like so:

connection.Query<Person>(fun p -> p.Age <= 25)

This, as you might have guessed, will return every person with the age of 25 or lower, which in this case is just me.

The Possibilities

I will only be able to touch the tip of the iceberg that is what you can do with RepoDb in this post, but let me go ahead and demonstrate a bulk operation as I mentioned is possible in the beginning. To generate multiple records to insert, I will use Bogus. I’ve recently written a blogpost on using Bogus with F#. Check it out if you’d like but you don’t have to. All you need to know is that the FullName method below gives us random names:

let peeps =
    [ for i = 0 to 500 do
        { Id = Guid.NewGuid()
          Name = faker.Name.FullName()
          Age = random.Next(20,60)
          CreatedDateUtc = DateTime.UtcNow } ]

It’s as simple to insert all 500 of these records I’ve just created as executing the following single line:

connection.BulkInsert<Person>(peeps)

You can also make complex queries using multiple criteria using F# code you're already familiar with:

connection.Query(fun p ->
    p.Name.Contains 'a'
    && p.Age > 30
    && p.CreatedDateUtc < DateTime.Parse("19/08/2020")
)

This will return you all the person records that meets all the following criteria:

• Name contains the lowercase letter "a"
• Age is greater than thirty
• Is created before 19th of August 2020

Closing Thoughts

If you’re considering to use this brilliantly simple yet powerful ORM, I advise you to visit its official website to find out more about it. I do feel like a short blogpost like this doesn’t do it justice. In any case, I hope you enjoyed this blog post and got some value out of it. Thanks for reading!