Why I like F# over C# – Less Noise

Oh, jingle bells, jingle bells
Jingle all the way
Oh, what fun it is to ride
Oh shit a blog deadline ♫


I did my first ever session a few months ago, I did a session called “Why you should learn F#”. I spent 30 minutes sweating and stuttering about pipelines and discriminated unions, people reactions were “Ok nice, but why is it better than C#, why do I need this, show some more code comparisons”. At first, this is what I NOT wanted to do, go the F# is better then C# blablabla way. I don’t hate C#. But I would like F# to be the language I use for projects at work, so if this is the way to convince people/companies to use F#, so be it.

I’m not a playa I just F# a lot – Big Pun

Succint Code

So in this blog I’ll start with a simple example why I like F# over C#. It’s how small and clean the code looks, because there’s less ceremony. This is because of:

  • Less curly brackets
  • No semicolons
  • Records instead class
    • Not getters &setters
    • Immutable by default
  • Not every fart in a separate file

To show the difference I simplified the code from a tool I wrote in F# and translated that to C#. I kept the C# in the same file, that removed some more code you normally had to add if you follow the C# code guidelines. The screenshot says it all I think, you can see how many, unneccesary, noise is added to the C# code.

Code from FolderTimeLine.fs to make sense of the code in screenshot 😉

That’s all for now, this is just one argument of the F# over C# story. Sorry I had to squeeze this one out a little, last year when I saw the fsadvent and I was like “Next year, I’m going to write an awesome blog about how I learned F# by blogging this year”. Well I learned some F# and did some blogging, but waaaayyy less then I hoped. So next year, I will again try to make the world safe for fsharpocracy.

Authenticate with an OAuth provider in your Giraffe application (.Net Core 2.1)

Introduction (rambling)

When I started my F# learning journey some months ago, I planned to go full functional F#, to use what the community used. Suave, Code, FAKE, SQL Type Providers, Paket. But I found out that made it not easier and with the limited spare time to invest in F#, that plan has changed a bit. I realized that when I wrote my previous blog  about Using Entity Framework Core with F#. I’m now gonna use technologies closer to home and that’s more Microsoft stackish, so Entity Framework and now also Giraffe on Asp.net Core. I hope this will make learning all this easier and I hope I’ll pickup the other technologies later on my path. You can say these technologies will be my gateway drugs to F#, my.. my gateway stack!  I would love to introduce F# at places I work; these are mostly Microsoft shops and I think it will be easier like this to get clients interested to try all this F# goodness out.

Setup the Giraffe solution

You can do dotnet new giraffe to create a complete giraffe web sample project and that’s what I did to play around and find out stuff for this blog. When I wrote code sample for this blog I created everything from scratch because I want to learn & understand this stuff. So first we start by creating the project. The complete sample code for this blog is on GitHub.

To create the project, go to the command line and enter the following commands:

dotnet new web -lang F#
dotnet add package Giraffe --version 1.2.0-preview-1
dotnet add package AspNet.Security.OAuth.GitHub --version 2.0.0-rc2-final
dotnet restore

I removed Startup.fs and added some files to organize the code I mostly copied from the Giraffe sample template.

Setup Https on local machine

When you develop and test your application you’ll do this locally and because OAuth providers like Facebook requires https you also need to run your local application on https. To do this you can follow these next steps.

First you need a certificate. I followed these instructions: Configuring HTTPS in ASP.NET Core across different platforms. You can export the certificate as a pfx file and place this in your web root for example.

From that same link I ported and simplified the example code to configure Kestrel to use only https and assign the correct certificate. Warning! I only aimed at using this locally and tested accordingly! First the code load the certificate, you need to change the settings in the appsetting.json. If you omit the file path and the password settings, the function will try to retrieve the certificate from the store.

let loadCertificate (configuration: IConfiguration)  = 
    let filepath = configuration.GetValue<string> "CertificateFilename"
    let password = configuration.GetValue<string> "CertificatePassword"
    let storename = configuration.GetValue<string> "CertificateStoreName"
    let storelocation = configuration.GetValue<StoreLocation> "CertificateStoreLocation"

    let loadFromStore =
            use store = new X509Store(storename, storelocation)
            let certificate = store.Certificates.Find(X509FindType.FindBySubjectName, "localhost", false) // TODO

    let loadFromFile = 
        new X509Certificate2(filepath, password)

    match String.IsNullOrWhiteSpace filepath, String.IsNullOrWhiteSpace password with
    | false, false -> loadFromFile
    | _ -> loadFromStore

The following code configures Kestrel to use this certificate on port 443.

let configureKestrel (options : KestrelServerOptions) = 
    let configuration = options.ApplicationServices.GetRequiredService<IConfiguration>()
    let certificate = loadCertificate configuration

    options.Listen(IPAddress.Loopback, 443, 
        fun (listenOptions) -> listenOptions.UseHttps(certificate) |> ignore

Then you point the WebHostBuilder.UseKestrel method to the configureKestrel function.

    .Configure(Action<IApplicationBuilder> configureApp)

Configure OAuth Providers

Configure GitHub App

Well it took some time to get this working, even with this great blog Authenticate with OAuth 2.0 in ASP.NET Core 2.0. I ported this to the Giraffe example but got in a redirect loop. With some help from someone in the functional programming slack channel (thanks damukles!) that was fixed and as it turned out I needed only half of the code. A newer version of Core might have also come in play there, with the current version it is just super easy!

Make sure you configure the app to use Authentication, this is simply done by the UseAuthentication method on the ApplicationBuilder:

let configureApp (app : IApplicationBuilder) =
    let env = app.ApplicationServices.GetService<IHostingEnvironment>()
    (match env.IsDevelopment() with
    | true  -> app.UseDeveloperExceptionPage()
    | false -> app.UseGiraffeErrorHandler errorHandler)

Create GitHub App

First you need to create an OAuth application in GitHub. Go to OAuth Apps and click New OAuth App.

When you click Register application the application is created and Client Id and Client Secret are generated. You need these later to in your Giraffe application. The Authorization callback URL is also something we’ll need to configure in the application.

Create Facebook App

If you want to use Facebook as provider you also have to make an app. You can then go to Settings > Basic and retrieve the App Id App secrets there.  You’ll also need to add a product  ‘Facebook login’ to this app. Set the Valid OAuth Redirect URIs here, by default this is /signin-facebook.

Configure GitHub & Facebook

As you can see this really easy. There some default settings which meaning I didn’t really dive into, most important settings for now are the client id and secret and make sure the CallbackPath is the same as the one you configured in the GitHub App. For Facebook just add the app id and secret.

let configureServices (services : IServiceCollection) =
    services.AddAuthentication(fun options ->
            options.DefaultAuthenticateScheme <- CookieAuthenticationDefaults.AuthenticationScheme
            options.DefaultSignInScheme <- CookieAuthenticationDefaults.AuthenticationScheme
            options.DefaultChallengeScheme <- "GitHub"
        .AddGitHub(fun options -> 
            options.ClientId <- "<Your ClientId>"
            options.ClientSecret <- "<Your ClientSecret>"
            options.CallbackPath <- new PathString("/signin-github") 

            options.AuthorizationEndpoint <- "https://github.com/login/oauth/authorize"
            options.TokenEndpoint <- "https://github.com/login/oauth/access_token"
            options.UserInformationEndpoint <- "https://api.github.com/user"  
        .AddFacebook(fun options ->
            options.AppId <- "<Your AppId>"
            options.AppSecret <- "<Your AppSecret>"
        |> ignore
    services.AddCors()    |> ignore
    services.AddGiraffe() |> ignore


Now we gonna specify for which parts of the web application you should be authenticated, for this we use the mustBeLoggedIn HttpHandler. If not authenticated it will call the challenge function. This construction is something I still have to wrap my head around, I can use it but understanding it is something else…

let mustBeLoggedIn : HttpHandler =
    requiresAuthentication (challenge "GitHub") 

let webApp : App = 
    choose [
        GET >=> 
            choose [
                route "/" >=>  indexHandler
        mustBeLoggedIn >=>
            GET >=>
                choose [
                    route "/secured" >=> indexHandler
        setStatusCode 404 >=> text "Not Found" ]

We can use the same handler for unauthenticated and authenticated and check if the user is authenticated by checking the IsAuthenticated property of the User in the HttpContext.

let indexHandler (next : HttpFunc) (ctx: HttpContext) =
    let nameClaim = ctx.User.FindFirst (fun c -> c.Type = ClaimTypes.Name)

    if ctx.User.Identity.IsAuthenticated then 
        let model     = { Text = nameClaim.Value }
        let view      = Views.index model    
        htmlView view next ctx
        let view      = Views.loginView
        htmlView view next ctx

That’s it! Not too difficult I think. In next blogs that will appear sooner than this one and then I want to explore creating a user in a database based on the authenticated user. I also would like to know how to apply this to a more front-end talking to api back-end scenario. Again, the sample code for this blog is on GitHub.


Using Entity Framework Core with F#

So this was fun… again, learned a lot… again! I blogged before about building a simple crud app that used a type provider and that worked pretty good, until I wanted to configure my continuous integration in VSTS. This didn’t work as smoothly as I wanted, because the type provider needs to connect to the database during the build. I read that I had to cache the definition or have the database setup before the build task, but still it felt uncomfortable. I thought, well why not take a look closer to home and use Entity Framework. Latest bits of course, because why not. Example code is on GitHub.

UPDATE 8/15/2018: GitHub example is updated because of some issues and there might be some other issues not fixed right now.

The types

Yes, this went pretty well. I thought I had to create mutable types to work with EF, but just adding the [<CLIMutable>] was enough. This makes the type just mutable for the compiler, not for us mortal developers. I defined a separate type for record ids because I can! Love this. Then I got all kung fu by using the and keyword, known as mutual recursive types, so I could have a Serie with a list of Episodes and have those Episodes have a navigation property to Serie. I made the latter an Option type and made sure it wasn’t mapped by using [<NotMapped>]. That way I can just create an instance of an Epsiode and later add it to a Serie.

type [<CLIMutable>] Serie =
  { Id: SerieId
    Name: string
    Description: string
    Episodes: List<Episode>
    Status: SerieStatus
and [<CLIMutable>] Episode = 
  { Id: EpisodeId
    Number: int
    Season : int
    Name: string
    Description: string
    Status: EpisodeStatus
    [<NotMapped>]Serie: Option<Serie>

The context

Then the DbContext, we’ll add DbSets for the types here, this is pretty straight forward. I also tried a 2.1.0 preview 1 feature here, namely the ValueConverter. With this you can map a type before is added or retrieved from the database. I just it here to map Enum as strings to the database, instead of integer. (It might be more useful to put enums in a separate table, but that’s not the point here)

let esconvert = ValueConverter<EpisodeStatus, string>((fun v -> v.ToString()), (fun v -> Enum.Parse(typedefof<EpisodeStatus>, v) :?> EpisodeStatus))
modelBuilder.Entity<Episode>().Property(fun e -> e.Status).HasConversion(esconvert) |> ignore

The repository

Time for some abstraction over the EF bits by using a repository. I don’t if this is all functional, so suggestions are welcome.

I started with the getSerie function by trying to work with the FirstOrDefault method. Stackoverflow convinced me to be more FSharpy and use the Seq.find which flow more nicely I think. The add functionality was simple, I struggled with the update a little more because I was used to Attach entities back to context or set it Modified.

let updateSerie (context: SerieContext) (entity: Serie) = 
    let currentEntry = context.Series.Find(entity.Id)
    context.SaveChanges |> ignore

It might look if this code will do an extra call to the database, but this is only if the entity isn’t loaded before. Otherwise the entity is retrieved from the context (memory).

I wanted to know how to do some Linq-like queries use I created a getSeriesWithAiredEpisodes function to try that out.

let getSeriesWithAiredEpisodes (context: SerieContext) = 
    query {
        for serie in context.Series do
            where (serie.Episodes.Exists (fun e -> e.Status = EpisodeStatus.Aired))
            select serie 

Had some light bulb moments trying Async, this isn’t my strong point in C# either by the way.

let addSerieAsync (context: SerieContext) (entity: Serie) =
    async {
        context.Series.AddAsync(entity) |> Async.AwaitTask |> ignore
        let! _ = context.SaveChangesAsync true |> Async.AwaitTask
        return entity

The configuration

Last but not least, applying the configuration. Functional applying that is! I created a CompositionRoot module so that the context is applied to the repository functions. Here you might set the connection strings too.

let configureSqlServerContext = 
    (fun () ->
        let optionsBuilder = new DbContextOptionsBuilder<SerieContext>();
        optionsBuilder.UseSqlServer(@"Server=.\SQLExpress;Database=Series;Integrated Security=SSPI;") |> ignore
        new SerieContext(optionsBuilder.Options)

I used the (func () -> …) construction (don’t know how this is called) so that if you need the create a separate context, just call this function. As you can see in the code on GitHub, this sample works with SQLite and SQL Server, just change the getContext function.

So that’s it, Have fun with it! Comments, critique or suggestions = awesome & welcome!

My First Suave Api(tm)

Subtitle: You Know Nothing Eelco Mulder

With my previous blogs I’ve been setting up everything around developing with F#, but now it’s really time for me to just start coding. I’ve read a lot about F#, but when I start to code myself… I seem to know very little! So from now I’m just gonna get my hands dirty and just learn by coding, meanwhile blogging about it so I hope it’ll stick better.

So I started with this little simple Crud web api (source here: https://github.com/EelcoMulder/SuaveCrud), build using F# on .Net Core, Suave and a SQL Server Express database. To connect to the database I used the SQLProvider, I found it easy to use and it has the linq syntax to query the database; automatic win for me.

I see other F# developers put their code in a few files, sometimes just one, but because I’m a C# developer and not some SAVAGE I got the urge to bundle certain code in separate files 😉

In the file Types.fs I created a little type Serie. In Database.fs I create the connection to the database using the SqlProvider. At first, when I ran dotnet build, I got the following error:

The type ‘String’ is required here and is unavailable. You must add a reference to assembly ‘System.Private.CoreLib, Version=, Culture=neutral, PublicKeyToken=7cec85d7bea7798e’

This is because the SqlProvider is a Type Provider and those are not supported by .Net Core out-of-the-box. To fix this, add the FscToolPath and FscToolExe to your fsproj:

    C:\Program Files (x86)\Microsoft SDKs\F#\4.1\Framework\v4.0

In Serie.fs I created a module for database specific actions, you might call this a repository. I Had a little light bulb moment when I found out how to pass the datacontext. Use the type provider young Skywalker! When I implemented the add function and tested it by posting data it gave me this error:

System.NotSupportedException: Enlisting in Ambient transactions is not supported.

I found a solution on the GitHub of the SqlProvider here, so I could solve it quickly by changing to call the GetDataContext function.

    let getContext = 
        SeriesSqlProvider.GetDataContext( { Timeout = System.TimeSpan.MaxValue; IsolationLevel = IsolationLevel.DontCreateTransaction})

Then Api.fs, I use this to convert to or from Json and wrap http response around the calls to the repository. I made it generic what was a bit out of scope of this blog, but it was a good exercise.

Again check the GitHub for the code, I know it needs a lot of work but I’m happy I’m getting some where. Mostly it is fun! Always open to suggestions on how to solve things better or differently!

Setup basic CI/CD for a Suave .Net Core 2.0 application running on an Azure App Service – Part II

< Part 1 you’ll need to read this first 😉

Setup continuous integration

Now for the second part, setting up ci and cd. Warm up that mouse because this requires a lot of clicking around in VSTS.

Connect VSTS to GitHub

The first thing we’re gonna do is to connect VSTS to GitHub. To do this go to settings > services:

Then add a new GitHub endpoint and click ‘Authorize’:

Setup build

Now it’s time to setup the build. Just click ‘New build’ and select the empty template. For the Agent queue I choose the Hosted 2017. The first step in the build is to get the sources from GitHub. We connected that the step before and we’ll just have to select the repository.

After that, we’re gonna add the steps for restore, build and publish, so we’ll need 3 .NET Core Tasks.

First step choose ‘restore’ as the Command, set the Path to projects to */*.fsproj. All .NET Core steps need that.

Next the build step, pretty straight forward:

And now the next step publish, the part that cost me the most troubles. If you look at the Arguments, you’ll see the -r win10-x64 switch. That makes it a self contained deployment If you don’t do that, the combination .NET Core 2.0 + Suave will give the following error on the Azure Web App:

System.IO.FileNotFoundException: Could not load file or assembly ‘System.Runtime, Version=

I tried a lot of things to get it to work, I ended up using this solution (part of my quest here)

We now add the last Task ‘Publish Build Artifact’, which will drop the package in the $(BuildArtifactStagingDirectory)

And now some of that VSTS magic, just flip the switch and set the trigger so that every commit will result in a new build.

All Done! Now create a new build to test if everything works. (Or commit a change of course!)

Setup release

Last but not least, releasing the freshly build package. Create a new release definition and select the ‘Azure App Service Deployment’ template.

We need to select which artifact to build and we’ll choose the stuff we just created with the build. This is all just too easy.

Set the automatic deployment by clicking the trigger button and enable Continuous deployment.

Then we need to configure where we want to deploy to, do this by clicking the 1 phase, 1 task link.

Here you can select your Azure Subscription and the App service where you want to deploy. And that’s almost all folks.

I Added 2 extra steps to stop the app service before deployment. I got some file in use errors before so stopping the app prevents that. You might want a staging slot to prevent down time, but it’s a bit overkill for this blog post.

Now release! Just by clicking or again commit some code. If everything went well as it should, you should see this beautiful message:

Next blog, maybe unit testing, maybe some database access…. maybe both!

Setup basic CI/CD for a Suave .Net Core 2.0 application running on an Azure App Service – Part I


Well I started this to learn F#, but I haven’t written a lot of lines of code yet. Before the real dive into F# I wanted to test out my stack, I wanted to use a lot of new technologies because… shiny new things. So I tried using all technologies with the volume set to 11, as latest as possible. This blog is about setting up an Suave application on .Net Core 2.0 to run on an Azure Web App, using the ci and cd facilities of VSTS. Missing here is tests, I’ll get to that in a later blog. Also I thought I had things working with Paket, but I ran in some problems I might try to solve later, so I ditched that.


Before we start you’ll need to have a GitHub repository, a VSTS project and an Azure Web App setup. And of course the bits and pieces installed I described in my previous blog post, plus Git. I also installed a VS Code extension gitignore so folders won’t be committed to git.

Setup the application

Setup solution and project

We’ll start with adding a console project and add it to a newly created solution using the dotnet cli:

dotnet new console -n SuaveNetCoreOnAzure -lang F#
dotnet new sln -n SuaveNetCoreOnAzure
dotnet sln add SuaveNetCoreOnAzure/SuaveNetCoreOnAzure.fsproj

Next add a new file to the project directory named web.config. Make sure to add it to the fsproj too:


Another thing we should do for our application to deploy correctly later is the add the RuntimeIdentifier to the fsproj. This is used to created a self contained deployment package.


Add references

Since Paket didn’t work for me during the writing of this blog, so I tried using the Install-Package and the dotnet add package commands to add project references and both failed adding the PackageReference tag to the fsproj file. So I just manually added the tag to the project file so it looks like this:


By calling dotnet restore in the terminal the package was downloaded. So everybody happy I guess.

Add code

Next some code! I know nothing yet so I got this from some examples I found on internet and modified it little. I won’t get into the code too much now, but the function appWebPart is used to configure the routing, the getPort function translates an argument to a integer. We’ll use this later to get an argument passed to the console application and configure this as the port number the suave web server will listen to.

let appWebPart =
    [ GET >=> choose
    [ path "/hello">=> OK "Hello F#.Net Core 2.0 with Suave running as Azure App Service"]]

let getPort argv =
  match Array.length argv with
    |_-> argv.[0]|> uint16

let main argv =
  let conf =
    { defaultConfig with
      bindings = [ HttpBinding.create HTTP IPAddress.Loopback <| getPort argv ] }
    startWebServer conf appWebPart

The web.config should be looking like this:


Check if everything by running the build command or even running the application

dotnet build
dotnet run --project SuaveNetCoreOnAzure

Add to git

Now we need to put this in a git repository. I used gitignore extension to add the folder obj and bin to the .gitignore file. Then initialized git and committed the files. I normally would have used the UI more, but I'm a good boy and used the git commands for this blog post.

git init
git remote add origin https://github.com/EelcoMulder/SuaveNetCoreOnAzure.git
git add -A
git commit -m 'Initial commit'
git push -u origin master

The application is prepared and safely in GitHub

Next part in the next part we'll setup build & release.

Getting started with F# and Visual Studio Code on Windows – Part III Create, run and debug a F# Console application

Part I Introduction
Part II Installation development environment
Part III Create, run and debug a F# Console application

The first step is now to create a console application with the Command Palette, press CTRL + Shift + P, type F#: New Project

Then, select console. After you can type a project directory or leave blank.

After a last dialog, the enter the project name. The IDE will start to build the project.

To test this you press CTRL+~ so the integrated terminal opens and execute the build.cmd. If everything goes well there will be an application .exe in the bin folder.

Then debugging. As I write this I read on internet a next version will have this automated but for now I will explain how I got around getting this to work. First of all the C# extension should be installed in Code. When I started debugging I got this error:

I think this was because I first installed Code and run some local restore, no idea. The error is explains what to do so that was easy to fix.
Next thing is to sets some paths in the task.json and launch.json; these should point to the dll in the build folder.

If that’s, just set a break point and start the debugger.

So, there is it, creating, running and debugging F# console application in VS Code!

Getting started with F# and Visual Studio Code on Windows – Part II Installation development environment

Part I Introduction
Part II Installation development environment
Part III Create, run and debug a F# Console application

The initial goal was to do a simple Hello World with F# + Suave, I quickly found out my initial dev rig had some issues. So, I decided to start with a clean Windows Virtual Machine and then install it piece by piece, starting with Visual Studio Code. This results in a list of components that might not all be need to be installed, but I think like this you’ll have good a base to build applications on .Net 4.6 and .Net Core.

There are at least two ways to create a basic project. One is to use to .Net core CLI, the other, what drove this blog post, is the functionality provided by Ionide-FSharp and the Command Palette of VS Code. This means pressing CTRL + Shift + P and selecting/typing F#: New Project. I didn’t create a .Net Core project yet, but an installation of the .Net core was needed to get things working.

The libraries:

Visual Studio Code

The next bits I didn’t install initially, the sample in the next blog could run without out it (works on .Net 4.6.1). I think you’ll need this to build F# applications that use .Net Core.

With this I eventually could do create, run and debug a Hello Worldish console application.

Getting started with F# and Visual Studio Code on Windows – Part I Introduction

Part I Introduction
Part II Installation development environment
Part III Create, run and debug a F# Console application

For more than a year I’m a spectator of the F# community, eager to learn something new but as a dad of two young boys no time & energy to really dive into to the material. Luckily, children do grow and a slowly I begin to claim back my life ? Time to pick-up some learning and blogging! I’m a Visual Studio C# guy, no previous experience with functional programming languages so this all new stuff for me.

This is the technology I want focus on in the coming time:

  • F#, of course
  • Visual Studio Code, because I found the Visual Studio experience not as smooth and people kept tweeting Code is the best
  • Suave and/or Giraffe
  • .Net Core

Some nice side technologies:

  • FAKE, build tools F#. As a Visual Studio guy, I never really bothered much with the ins and outs of the build, this seems a nice way to start doing this some more.
  • Paket, this also seems to be the best thing since sliced bread according to some people.
  • Azure, it would be cool to eventually use the continuous integration and deployment tooling to make all this 2017 proof.

First things first, getting to a Hello World type of stage with the F# stack. This starts with a development environment, finding out how, what and installing this didn’t turned out to be a smooth and easy as I was led to believe. Big part of this is of course a lot new bits & bytes. So here is a blog with my findings to a get to a good start environment for developing F# stuff.

Create a Builder with Fluent API and C#

Having already read about this elsewhere, I still thought this stuff too much fun to not blog about: creating a builder with fluent API. You can read more about the subject on internet, this blog is just code 🙂

The code is an example implementation, it’s about the concepts. It is a stripped down but pimped version of an implementation I did at a client I worked. The application needed to provide the users with a way to dynamically construct queries for their database. Some queries came preconfigured and I used the fluent API to create these. It also enabled me to create tests more easily.

First let’s take a look at the object we’re building, the Query.

public class Query
public string TableName { get; set; }
public IList Columns { get; set; } = new List();
public IList Tables { get; set; } = new List
(); public IList Conditions { get; set; } = new List(); public IList OrderBys { get; set; } = new List(); }

If we build the structure without a builder this would look something like this:

Query query = new Query();
query.TableName = "MainTable";
Table subTable1 = new Table();
subTable1.TableName = "SubTabel1";
subTable1.ParenKey = "MainTableId";
subTable1.ChildKey = "SubTabel1Id";
CompareCondition compareCondition = new CompareCondition();
compareCondition.Column = "Column2";
compareCondition.Compare = CompareType.Equal;
compareCondition.Value = "SomeValue";
OrderBy orderBy1 = new OrderBy();
orderBy1.Column = "Column1";
orderBy1.Direction = OrderByDirection.Ascending;

This is a lot of code, not very readable, not very expressive. So now we will start with the first simple implementation for setting the table name and adding columns, we create the QueryBuilder with the method OnTable and AddColumn.

public class QueryBuilder
private readonly Query _query = new Query();

public QueryBuilder OnTable(string tableName)
_query.TableName = tableName;
return this;
public QueryBuilder AddColumn(string columnName)
return this;
public Query Done()
return _query;

The interesting part is the return value of this method, it returns the builder itself so we can call AddColumn again and again. This practice called method chaining. We can now use this code like this:

Query query = new QueryBuilder()

The Done method is to signal construction is done and to return the actual Query object. Fairly easy and the results are already more readable code. But we’re not done yet, we’re now going to use a second builder to add the OrderBy and take these concepts even further.

public class OrderByBuilder
private readonly Query _query;
private readonly QueryBuilder _queryBuilder;
private readonly OrderBy _orderBy = new OrderBy();

public OrderByBuilder(Query query, QueryBuilder queryBuilder)
_query = query;
_queryBuilder = queryBuilder;

public OrderByBuilder SetOrderOn(string column)
_orderBy.Column = column;
return this;

public QueryBuilder AndDirectionTo(OrderByDirection orderByDirection)
orderBy.Direction = orderByDirection;
return _queryBuilder;

We use the constructor the pass a reference to the Query object we’re building and also the calling QueryBuilder. This is so we can return to this builder when we’re done with the order by. The previous builder used the Done method to signal the end, for this builder I decided to use the AndDirectionTo method to do this. To use this first we’ll add the following to the QueryBuilder:

public OrderByBuilder AddOrderBy()
OrderByBuilder orderByBuilder = new OrderByBuilder(_query, this);
return orderByBuilder;

Now we can use it like this:

Query query = new QueryBuilder()

We’ve created a little domain language here by using the SetOrderOn().AndDirectionTo() methods. But this code doesn’t prohibit saying AndDirectionTo().SetOrderOn() or even AndDirectionTo().AndDirectionTo(). For this we’re going to use interfaces. We add these:

public interface ISetOrderOn
IAndDirectionTo SetOrderOn(string column);

public interface IAndDirectionTo
QueryBuilder AndDirectionTo(OrderByDirection orderByDirection);

And modify the OrderByBuilder like this:

public class OrderByBuilder : ISetOrderOn, IAndDirectionTo
private readonly Query _query;
private readonly QueryBuilder _queryBuilder;
private readonly OrderBy _orderBy = new OrderBy();

public OrderByBuilder(Query query, QueryBuilder queryBuilder)
_query = query;
_queryBuilder = queryBuilder;

public IAndDirectionTo SetOrderOn(string column)
_orderBy.Column = column;
return this;

public QueryBuilder AndDirectionTo(OrderByDirection orderByDirection)
_orderBy.Direction = orderByDirection;
return _queryBuilder;

Now we when the SetOrderOn method is called it returns the method of the IAndDirectionTo interface, not the complete builder’s interface. Nifty! We’ll make sure the QueryBuilder forces the first methods to be SetOrderOn using the same ‘trick’:

public ISetOrderOn AddOrderBy()
OrderByBuilder orderByBuilder = new OrderByBuilder(_query, this);
return orderByBuilder;

Now calling AddOrderBy can only be followed by SetOrderOn. But when you look at the usage code, using the AddOrderBy method seems a little redundant. So with I decided to go a little further with adding the Table objects. I’m not completely happy with this solution because the QueryBuilder needs to know too much about the TableBuilder, but it does result in a nicer syntax. Change the QueryBuilder method like this:

public IWithParentField JoinOnTable(string tableName)
TableBuilder tableBuilder = new TableBuilder(_query, this);
return tableBuilder;

Like this we’re able to call the first method in the chain and use the code like this:

Query query = new QueryBuilder()

That’s it. I created a sample project and put it on GitHub.