A Better .NET SQL Builder: KnightMoves.SqlObjects

Software developers know all too well that you don’t have much of an application without an underlying data store to maintain the information that the user is interested in keeping. For many .NET applications, the data store of choice is a relational database. The database itself, such as Microsoft’s SQL Server, is a separate data platform unto itself. The application code that the .NET developer writes has to do the job of connecting to the database, communicating with it, and passing the data to other layers of the application code up the stack. Developers will recognize this as the Data Access Layer, Infrastructure, or simply the Repository.

If you’ve done this before you’ll know that it is typically implemented with the help of some sort of translation library that we call an Object-Relational Mapper or an “ORM” for short. Microsoft’s implementation of the ORM is called Entity Framework Core or EF Core and there’s another very popular alternative that is more lightweight (referred to as a Micro-ORM) that is called Dapper. They each have their pros and cons.

Let’s talk about the cons.

The Problem with ORMs

I’ve been around long enough in my career to remember the times when ORMs didn’t exist. The first one I can remember is Hibernate for the Java stack, which was later ported to a .NET version called NHibernate. A number of ORMs sprung up such as NetTiers, which generated the data access layer code instead of functioning as the data access layer itself. Eventually, the ORM wars seem to have settled down with EF Core and Dapper being the most popular and all others being competitors that use some flavor of the same strategy as these two popular solutions more or less.

EF Core is a robust ORM that handles just about everything you can want as a layer between your application and the database. Ultimately, for our purposes we’re most interested in its ability to store and retrieve data to and from the database respectively because that is what gets executed for the majority of the hits to your application’s functionality by users. This is where the SQL language comes in, which we’ll get into in a bit.

Dapper, on the other hand, attempts to do one and only one thing well and that is the aforementioned storing and retrieving of data. You will not find any of the other frills that EF Core offers like Migrations or the Unit of Work Pattern just to name a couple off the top of my head. It’s why it’s called a lightweight “Micro” ORM.

When it comes to the thing they have in common, that is, storing and retrieving data, they both have to submit SQL statements to the database to be executed and its results returned. This is where the challenges arise.

EF Core is known to create massive SQL statements that don’t perform well when the schema of the data being fetched is more complex. Furthermore, trying to figure out what those queries will look like when you’re using the EF Core library in C# code is difficult at best. You basically use the library and hope that the SQL statement it sends to the database is decent enough. When you run into problems, it’s difficult to troubleshoot because you have sniff out the SQL that it is sending using a profiler tool that captures the communication enroute to the database. Then when you do capture a view of the SQL code it generated you realize what a monstrosity it is. How then do you write the C# code using the EF Core library to ensure that it writes a better query? This is either extremely difficult or impossible at worst since you don’t have any control over how the EF Core library functions under the hood.

Dapper, on the other hand, gives you full control and many developers choose to use Dapper for this reason. You get to write the entire SQL statement yourself. The problem with Dapper is that you get to write the entire SQL statement yourself. It’s good to have this control but the reason why it’s a problem is because you now have to provide the SQL code as a string of text in your C# application. Anyone who’s done this for a single application will run into the problems well before they finish the first version of the app. Writing SQL as a bunch of strings of text in C# code is a nightmare for many reasons that are beyond the scope of this article. As a .NET developer I’m just going to assume that you know exactly what I’m talking about and probably explains why you’ve read this far in the article.

Enter the world of SQL Builders

To make development of SQL statements as a string of text easier, there have been multiple attempts at creating libraries that use C# code to generate the SQL for you, so you don’t run into the various problems involved with string concatenation/interpolation and the like. It’s like adding a feature of EF Core, the SQL building part, to Dapper by combining the efforts of two separate libraries in hopes to get the best of both worlds.

While this is most definitely an improvement in the right direction, the current solutions to the SQL string building dilemma have been less than ideal in my experience. And I say that with cautious compassion because I think the efforts put into those solutions were not only huge and beneficial but also an inspiration to the solution presented here so I’m grateful and highly respect those professionals.

A Better .NET SQL Builder

To resolve the problems with the current offerings of SQL builders and strike a better balance between ORM (e.g. EF Core) and Micro-ORM (e.g. Dapper) capabilities, I decided to build my own solution as a NuGet package library available for download at https://nuget.org.

Introducing: KnightMoves.SqlObjects

As the author of this library, I can tell you sincerely that this is not a wheel I wanted to reinvent. But when I came up with the idea for a new approach to SQL building, I couldn’t resist making one, and now I’m proud to announce the delivery of this new SQL builder library to make .NET developers’ lives that much easier.

The Greatest Feature

This library has so many features that I’ve decided to break it up into a rather long series of smaller, more digestible articles. In this article, I will introduce what I believe is its greatest feature, which is SQL syntax matching.

SQL Syntax Matching

EF Core and other SQL builders use their own terminology for the syntax, which requires you to learn a different language version of SQL and then having to learn how it translates into SQL to boot. Matching SQL syntax means that you can use your knowledge of SQL in both the database tooling as well as your C# application code thereby removing the unnecessary burden of having to learn a whole new language just to use a single library.

Now, to be fair, Dapper has a solution called Dapper.SimpleSqlBuilder that does a much better job by using actual SQL syntax naming for its builder, which is the same feature in this library but not totally. KnightMoves.SqlObjects matches SQL syntax more closely than Dapper’s, but if that was the only competing feature offered in this new library, then it wouldn’t have been worth building. Apologies to other SQL builder solutions that I haven’t mentioned since I may not know they even exist.

Dapper’s SimpleSqlBuilder doesn’t do much beyond providing a SQL-like builder. Dapper’s builder is built completely on strings and string interpolation, whereas KnightMoves.SqlObjects is built entirely on objects, hence the name SqlObjects. This makes it orders of magnitude more powerful and feature-rich than any SQL builder that relies primarily on string manipulation could ever be.

For this article, we’re going to start with this basic offering, the SQL-matching syntax. So, without further delay, let’s dive into an example.

Demo

First, create a simple Console application in Visual Studio and add KnightMoves.SqlObjects NuGet package library from https://nuget.org.

That’s enough to put the demo code below into the boilerplate Main() method of the Program.cs file replacing the “Hello World” line of code.

First you import the library with a using statement.

                                 
using KnightMoves.SqlObjects; 
                                 
                              

The fluent SQL builder is available through the static TSQL class so you can begin there and code very much as though you’re coding in SQL.

                                 
var sql = TSQL

   .SELECT()
   .STAR()
   .FROM("Products")
   .Build()

;

Console.WriteLine(sql);                                 
                                 
                              

Notice that when you call the Build() method it returns the string of SQL from the way you built the SQL objects before that.

Here’s the output:

                                 
   SELECT
      *
   FROM [Products]
                                 
                              

There are various ways to specify the columns of the select list. The most basic way is to use the COLUMN() method for each column you specify.

                                 
var sql = TSQL

   .SELECT()
     .COLUMN("ProductID")
     .COLUMN("ProductName")
   .FROM("Products")
   .Build()

;

Console.WriteLine(sql);
                                 
                              

Output:

                                 
SELECT
 [ProductID],
 [ProductName]
FROM [Products]
                                 
                              

But we’re just getting started. You can provide a collection of column names and pass that to the COLUMNS() method (notice it is plural) and it will use those names to create the list of columns.

                                 
var columns = new List { "ProductID", "ProductName" };

var sql = TSQL

     .SELECT()
       .COLUMNS(columns)
     .FROM("dbo", "Products", "p")
     .Build()

;

Console.WriteLine(sql);
                                 
                              

Output:

                                 
SELECT
 [ProductID],
 [ProductName]
FROM [dbo].[Products] p
                                 
                              

If you know SQL well then you know that there is all manner of things you can do in the select list to make it a more robust query. This library handles them. Let’s start with a simple alias using .AS().

                                 
var sql = TSQL

   .SELECT()
     .COLUMN("ProductID").AS("Id")
     .COLUMN("ProductName")
   .FROM("Products")
   .Build()

;
                                 
                              

Output:

                                 
SELECT
 [ProductID] AS [Id],
 [ProductName]
FROM [Products]
                                 
                              

You can see it correctly produces the line [ProductID] AS [Id]

Do you need to specify the schema and a multipart identifier? Easy. Suppose you’re using dbo as the schema and p as an alias for the Products table. Then you can do so like this.

                                 
var sql = TSQL

   .SELECT()
     .COLUMN("p", "ProductID", "Id")
     .COLUMN("p", "ProductName")
   .FROM("dbo", "Products", "p")
   .Build()

;

Console.WriteLine(sql);
                                 
                              

Output:

                                 
SELECT
 [p].[ProductID] AS [Id],
 [p].[ProductName]
FROM [dbo].[Products] p
                                 
                              

You can also see an alternative to provide the alias. Instead of using .AS() you can provide the alias as a third parameter to the COLUMN() method.

It’s a hassle to keep repeating the COLUMN() method call and we know that we can use a collection of column names, but what if we need to prefix them with the table alias? Easy, we can do it like this.

                                 
var columns = new List { "ProductID", "ProductName" };

var sql = TSQL

     .SELECT()
       .COLUMNS("p", columns)
     .FROM("dbo", "Products", "p")
     .Build()

;

Console.WriteLine(sql);
                                 
                              

Output:

                                 
SELECT
 [p].[ProductID],
 [p].[ProductName]
FROM [dbo].[Products] p
                                 
                              

The use of aliases becomes more important when you’re joining tables. So, let’s give that a try by joining Products and Categories.

                                 
var sql = TSQL

     .SELECT()
       .COLUMN("p", "ProductID")
       .COLUMN("c", "CategoryName")
     .FROM("dbo", "Products", "p")
     .INNERJOIN("dbo", "Categories", "c").ON("c", "CategoryID").IsEqualTo("p", "CategoryID")
     .Build()

;

Console.WriteLine(sql);
                                 
                              

Output:

                                 
SELECT
 [p].[ProductID],
 [c].[CategoryName]
FROM [dbo].[Products] p
INNER JOIN [dbo].[Categories] c ON [c].[CategoryID] = [p].[CategoryID]
                                 
                              

If you need to join more tables then all you have to do is slap another INNERJOIN() call exactly where you normally would if you’re coding in SQL with the schema and alias like so.

                                 
var sql = TSQL

 .SELECT()
   .COLUMN("p", "ProductID")
   .COLUMN("p", "ProductName")
   .COLUMN("c", "CategoryName")
   .COLUMN("s", "CompanyName")
 .FROM("dbo", "Products", "p")
 .INNERJOIN("dbo", "Categories", "c").ON("c", "CategoryID").IsEqualTo("p", "CategoryID")
 .INNERJOIN("dbo", "Suppliers", "s").ON("s", "SupplierID").IsEqualTo("p", "SupplierID")
 .Build()

;

Console.WriteLine(sql);
                                 
                              

Output:

                                 
SELECT
 [p].[ProductID],
 [p].[ProductName],
 [c].[CategoryName],
 [s].[CompanyName]
FROM [dbo].[Products] p
INNER JOIN [dbo].[Categories] c ON [c].[CategoryID] = [p].[CategoryID]
INNER JOIN [dbo].[Suppliers] s ON [s].[SupplierID] = [p].[SupplierID]
                                 
                              

Notice that throughout this demo you can see that when you’re using this library you can think in SQL terms. Some things will deviate slightly such as the use of COLUMN() instead of just literally typing in the column name where it belongs and later you’ll see that we use a fluent method call for operators such as IsEqualTo() instead of the = string character but the thought process is the same. You’re thinking in SQL even though you’re coding in C#.

For further assistance, because the library is SQL in C# dressing, its methods and signatures pop up in the intellisense features of the IDE where you can search through the options to find what you’re looking for easily.

We are barely scratching the surface here. Below is a list of parts of the SQL language that have been implemented in the KnightMoves.SqlObjects library, specifically for Microsoft’s SQL Server T-SQL implementation though most of it is the same for other database products.

• DISTINCT
• TOP
• INNER JOIN, LEFT JOIN, RIGHT JOIN
• INSERT
• UPDATE
• DELETE
• WHERE
  • AND, OR
  • BETWEEN
  • IN
  • LIKE
• CASE
• GROUP BY
• ORDER BY
• HAVING
• UNION
• Arithmetic Operators
  • Plus (+)
  • Minus (-)
  • Divide (/)
  • Multiply (*)
  • Modulo (%)
• Subqueries
• Functions
  • ABS
  • AVG
  • CEILING
  • COUNT
  • MIN
  • MAX
  • SUM
  • Etc.
• Scripts

And so much more.

All of this is fully documented here.

KnightMoves.SqlObject Documentation

Head on over there to get started and see what you can do with the basics. Stay tuned for other articles in this series where we’ll cover more and more features of this robust library.

Thanks for reading this far. I sincerely hope you enjoy this library as much as I enjoyed making it.