Working with SqlKata

If you've ever had to write raw SQL strings in C# and felt uneasy about them — maybe worrying about SQL injection, or just hating how hard it is to maintain a long query string — then SqlKata might be exactly what you need. It's a lightweight, fluent query builder for .NET that lets you write SQL in a clean, readable, object-oriented way. No heavy ORM baggage, no magic. Just clean query construction.

I started using SqlKata in a project where we needed highly dynamic queries — filters that change at runtime, optional joins, and pagination. Dapper was already doing our data mapping, so a full ORM would've been overkill. SqlKata fit perfectly in that gap.

Setting Up SqlKata

First, install the NuGet packages. You'll need the core library plus the execution package (which wraps around Dapper), and a compiler for your specific database:

dotnet add package SqlKata
dotnet add package SqlKata.Execution
dotnet add package Microsoft.Data.SqlClient

SqlKata supports multiple databases out of the box — SQL Server, MySQL, PostgreSQL, SQLite, Firebird, and Oracle — each with its own compiler. You just swap the compiler and the connection type.

Creating the DbInstance

Here's how I structure the database instance using .NET's dependency injection. I like wrapping everything in an interface so it's easy to mock in unit tests:

using Microsoft.Data.SqlClient;
using SqlKata.Compilers;
using SqlKata.Execution;
using System.Data;

namespace MyDemoNamespace;

public interface IDbInstance
{
    IDbConnection GetConnection();
    Compiler Compiler { get; }
    QueryFactory QueryFactory { get; }
}

public class DbInstance(IConfiguration configuration) : IDbInstance
{
    private readonly Compiler _compiler = new SqlServerCompiler();

    public IDbConnection GetConnection()
    {
        SqlConnection sqlConnection = new(configuration.GetConnectionString("Connection"));
        return sqlConnection;
    }

    public QueryFactory QueryFactory
    {
        get
        {
            QueryFactory queryFactory = new(GetConnection(), _compiler);
            return queryFactory;
        }
    }

    public Compiler Compiler => _compiler;
}

builder.Services.AddScoped<IDbInstance, DbInstance>();

Basic Queries — SELECT, WHERE, ORDER BY

Once you have the QueryFactory, querying data feels almost like writing English:

// Simple select all
var users = await db.QueryFactory.Query("Users").GetAsync<User>();

// With WHERE and ORDER BY
var activeUsers = await db.QueryFactory
    .Query("Users")
    .Where("IsActive", true)
    .Where("Age", ">=", 18)
    .OrderByDesc("CreatedAt")
    .GetAsync<User>();

// Select specific columns
var names = await db.QueryFactory
    .Query("Users")
    .Select("Id", "Name", "Email")
    .Where("IsActive", true)
    .GetAsync<UserDto>();

INSERT, UPDATE, DELETE

Writing data is just as clean:

// INSERT
var newId = await db.QueryFactory.Query("Users").InsertGetIdAsync<int>(new {
    Name = "Alex",
    Email = "alex@example.com",
    CreatedAt = DateTime.UtcNow
});

// UPDATE
var rowsAffected = await db.QueryFactory
    .Query("Users")
    .Where("Id", userId)
    .UpdateAsync(new { Name = "Alex R.", UpdatedAt = DateTime.UtcNow });

// DELETE
await db.QueryFactory
    .Query("Users")
    .Where("Id", userId)
    .DeleteAsync();

Joins

Joins are where SqlKata really shines compared to raw SQL strings. Complex joins become much easier to read and compose:

var result = await db.QueryFactory
    .Query("Orders as o")
    .Join("Users as u", "u.Id", "o.UserId")
    .Join("Products as p", "p.Id", "o.ProductId")
    .Select("o.Id", "u.Name", "p.Title", "o.Quantity", "o.CreatedAt")
    .Where("o.Status", "confirmed")
    .OrderByDesc("o.CreatedAt")
    .GetAsync<OrderDetailDto>();

Pagination

SqlKata has first-class support for pagination, which is one of those things that's surprisingly annoying to do correctly in raw SQL (especially across different databases):

int page = 1;
int pageSize = 20;

var pagedResult = await db.QueryFactory
    .Query("Products")
    .Where("IsActive", true)
    .OrderBy("Name")
    .PaginateAsync<Product>(page, pageSize);

// pagedResult.List = the items
// pagedResult.Count = total count
// pagedResult.Page = current page
// pagedResult.TotalPages = total pages

Dynamic Query Building

This is the killer feature for me. You can conditionally add clauses without building ugly string concatenations:

var query = db.QueryFactory.Query("Products").Where("IsActive", true);

if (!string.IsNullOrEmpty(searchTerm))
    query = query.WhereLike("Name", "%" + searchTerm + "%");

if (categoryId.HasValue)
    query = query.Where("CategoryId", categoryId.Value);

if (minPrice.HasValue)
    query = query.Where("Price", ">=", minPrice.Value);

var products = await query.OrderBy("Name").GetAsync<Product>();

Tips and Gotchas

A few things I've learned the hard way:

Connection management: The QueryFactory creates a new connection per query by default. In high-throughput scenarios, consider sharing a connection or using connection pooling carefully.
Raw expressions: Sometimes you need a raw SQL fragment. Use QueryFactory.FromQuery() or the WhereRaw() method — but be careful about SQL injection with user inputs.
Logging: You can attach a logger to see the generated SQL: factory.Logger = compiled => Console.WriteLine(compiled.ToString()); — super useful during development.
Different compilers for tests: In unit tests, you can use SqliteCompiler with an in-memory SQLite database to avoid needing a real SQL Server. This is a big win for CI/CD pipelines.

Summary

SqlKata is a clean, focused tool that does one thing well: building SQL queries in C#. It's not trying to replace Entity Framework or be a full ORM — it's the thing you reach for when you want full control over your SQL but don't want to maintain a mess of string concatenations. If you're already using Dapper, SqlKata pairs with it beautifully. Give it a try on your next .NET project.

References:
SqlKata Official Documentation
SqlKata on GitHub
SqlKata.Execution on NuGet