ZetCode

C# SqlKata

last modified October 18, 2026

In this article we show how to program databases in C# with the SqlKata library. SqlKata uses the builder pattern to define queries.

SqlKata is a fluent SQL query builder for C#. It allows us to create SQL statements with method chaining, instead of concatenating strings. It supports SQL Server, MySQL, PostgreSQL, SQLite, Oracle, and Firebird, and generates the SQL dialect of each. Values are always passed as parameters, which protects against SQL injection. The library is split into two packages:

$ dotnet add package SqlKata
$ dotnet add package SqlKata.Execution

We add the SqlKata packages.

$ dotnet add package Npgsql

We add the driver for the PostgreSQL database.

The PostgreSQL server for the examples can run in a Docker container.

$ docker run -d --name pg -e POSTGRES_PASSWORD=passwd -e POSTGRES_DB=testdb \
    -p 5432:5432 postgres:18

We create the testdb database. In the examples we use these tables.

cars_postgre.sql
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS cars;
DROP TABLE IF EXISTS customers;

CREATE TABLE cars(id serial PRIMARY KEY, name VARCHAR(255), price INT);
INSERT INTO cars(name, price) VALUES('Audi', 52642);
INSERT INTO cars(name, price) VALUES('Mercedes', 57127);
INSERT INTO cars(name, price) VALUES('Skoda', 9000);
INSERT INTO cars(name, price) VALUES('Volvo', 29000);
INSERT INTO cars(name, price) VALUES('Bentley', 350000);
INSERT INTO cars(name, price) VALUES('Citroen', 21000);
INSERT INTO cars(name, price) VALUES('Hummer', 41400);
INSERT INTO cars(name, price) VALUES('Volkswagen', 21600);

CREATE TABLE customers(id serial PRIMARY KEY, name VARCHAR(255));
INSERT INTO customers(name) VALUES('John Doe'), ('Jane Doe');

CREATE TABLE orders(id serial PRIMARY KEY,
    customer_id INT REFERENCES customers(id), car_id INT REFERENCES cars(id));
INSERT INTO orders(customer_id, car_id) VALUES (1, 1), (1, 3), (2, 2);

The examples change the data. Run the script again to restore the original state.

SqlKata compile a query

The core of SqlKata is the Query class. A query is just an object; to get SQL, we compile it with a compiler. No database connection is needed for that, which makes it a good start.

Program.cs
using SqlKata;
using SqlKata.Compilers;

var compiler = new PostgresCompiler();

var query = new Query("cars")
    .Select("name", "price")
    .Where("price", ">", 20000)
    .WhereIn("name", new[] { "Audi", "Volvo", "Skoda" })
    .OrderByDesc("price")
    .Limit(5);

SqlResult result = compiler.Compile(query);

Console.WriteLine(result.Sql);
Console.WriteLine(string.Join(", ", result.Bindings));
Console.WriteLine(result.ToString());

// the same query for other databases
Console.WriteLine(new SqlServerCompiler().Compile(query).Sql);
Console.WriteLine(new MySqlCompiler().Compile(query).Sql);
Console.WriteLine(new SqliteCompiler().Compile(query).Sql);

We build one query and compile it for four databases.

var query = new Query("cars")
    .Select("name", "price")
    .Where("price", ">", 20000)
    .WhereIn("name", new[] { "Audi", "Volvo", "Skoda" })
    .OrderByDesc("price")
    .Limit(5);

The query selects two columns of the cars table, applies two conditions joined with AND, orders the result, and limits it to five rows.

SqlResult result = compiler.Compile(query);

The compiler returns an SqlResult with the SQL text, in which the values are replaced by placeholders, and the list of Bindings. Its ToString method inlines the values, which is useful for logging and debugging, but the inlined text should not be executed.

$ dotnet run
SELECT "name", "price" FROM "cars" WHERE "price" > @p0 AND "name" IN (@p1, @p2, @p3) ORDER BY "price" DESC LIMIT @p4
20000, Audi, Volvo, Skoda, 5
SELECT "name", "price" FROM "cars" WHERE "price" > 20000 AND "name" IN ('Audi', 'Volvo', 'Skoda') ORDER BY "price" DESC LIMIT 5
SELECT [name], [price] FROM [cars] WHERE [price] > @p0 AND [name] IN (@p1, @p2, @p3) ORDER BY [price] DESC OFFSET @p4 ROWS FETCH NEXT @p5 ROWS ONLY
SELECT `name`, `price` FROM `cars` WHERE `price` > @p0 AND `name` IN (@p1, @p2, @p3) ORDER BY `price` DESC LIMIT @p4
SELECT "name", "price" FROM "cars" WHERE "price" > @p0 AND "name" IN (@p1, @p2, @p3) ORDER BY "price" DESC LIMIT @p4

The compilers quote the identifiers in the way of each database and translate the limit: LIMIT for PostgreSQL, MySQL, and SQLite, and OFFSET ... FETCH NEXT for SQL Server.

SqlKata query single row

In the first database example, we query a single row.

Program.cs
using Npgsql;
using SqlKata.Compilers;
using SqlKata.Execution;

string cs = "User ID=postgres;Password=passwd;Host=localhost;Database=testdb";

using var con = new NpgsqlConnection(cs);
con.Open();

var db = new QueryFactory(con, new PostgresCompiler());

var car = db.Query("cars").Where("id", 1).First();

Console.WriteLine(car);
Console.WriteLine($"{car.id} {car.name} {car.price}");

// FirstOrDefault returns null when there is no match
var none = db.Query("cars").Where("id", 999).FirstOrDefault();
Console.WriteLine(none is null);

We select the row with the id equal to 1.

string cs = "User ID=postgres;Password=passwd;Host=localhost;Database=testdb";

using var con = new NpgsqlConnection(cs);
con.Open();

First, we define the connection string and open a database connection.

var db = new QueryFactory(con, new PostgresCompiler());

We choose the compiler for our database and create a QueryFactory. The factory builds queries and executes them on the connection.

var car = db.Query("cars").Where("id", 1).First();

The query is built by chaining three method calls: Query, which selects the table name, Where, which defines the WHERE clause, and First, which executes the query and returns the first row. First throws an exception if there is no row; FirstOrDefault returns null instead. The row is a dynamic object, so its columns are properties.

$ dotnet run
{DapperRow, id = '1', name = 'Audi', price = '52642'}
1 Audi 52642
True

SqlKata fetch all rows

In the next example, we fetch all rows.

Program.cs
using Npgsql;
using SqlKata.Compilers;
using SqlKata.Execution;

string cs = "User ID=postgres;Password=passwd;Host=localhost;Database=testdb";

using var con = new NpgsqlConnection(cs);
con.Open();

var db = new QueryFactory(con, new PostgresCompiler());

var cars = db.Query("cars").OrderByDesc("name").Get();

foreach (var car in cars)
{
    Console.WriteLine($"{car.id} {car.name} {car.price}");
}

The program takes all rows from the table and orders the output by name in descending order.

var cars = db.Query("cars").OrderByDesc("name").Get();

We get the result by chaining the Query, OrderByDesc, and Get calls. The query is executed by Get.

$ dotnet run
4 Volvo 29000
8 Volkswagen 21600
3 Skoda 9000
2 Mercedes 57127
7 Hummer 41400
6 Citroen 21000
5 Bentley 350000
1 Audi 52642

SqlKata where, order and paging

The builder has methods for conditions, ordering, and paging. Conditions are combined with AND by default; the Or... variants combine with OR.

Program.cs
using Npgsql;
using SqlKata.Compilers;
using SqlKata.Execution;

string cs = "User ID=postgres;Password=passwd;Host=localhost;Database=testdb";

using var con = new NpgsqlConnection(cs);
con.Open();

var db = new QueryFactory(con, new PostgresCompiler());

var cars = db.Query("cars")
    .Select("name", "price")
    .Where("price", ">", 20000)
    .WhereNotIn("name", new[] { "Bentley" })
    .OrWhere("name", "Skoda")
    .OrderBy("price")
    .Get();

foreach (var car in cars)
{
    Console.WriteLine($"{car.name,-12} {car.price,7}");
}

Console.WriteLine("--");

var page = db.Query("cars").OrderBy("id").Limit(3).Offset(3).Get();
foreach (var car in page)
{
    Console.WriteLine(car.name);
}

Console.WriteLine("--");

var paged = db.Query("cars").OrderBy("id").Paginate(2, 3);
Console.WriteLine($"page {paged.Page} of {paged.TotalPages}, {paged.Count} rows in total");
foreach (var car in paged.List)
{
    Console.WriteLine(car.name);
}

The program uses several conditions and two ways of paging.

var cars = db.Query("cars")
    .Select("name", "price")
    .Where("price", ">", 20000)
    .WhereNotIn("name", new[] { "Bentley" })
    .OrWhere("name", "Skoda")
    .OrderBy("price")
    .Get();

This builds WHERE price > 20000 AND name NOT IN ('Bentley') OR name = 'Skoda'. SQL gives AND a higher precedence than OR, so the conditions group as the sentence suggests: expensive cars other than Bentley, or the Skoda. For other groupings, we nest conditions with a lambda, such as Where(q => q.Where(...).OrWhere(...)), which adds parentheses.

var page = db.Query("cars").OrderBy("id").Limit(3).Offset(3).Get();

Limit and Offset select a window of rows. Paging without an OrderBy is not deterministic, so always order.

var paged = db.Query("cars").OrderBy("id").Paginate(2, 3);

Paginate takes the page number and the page size and returns an object with the rows in List and the paging information: Page, TotalPages, and Count, the total number of rows. It runs two queries, a count and the page.

$ dotnet run
Skoda           9000
Citroen        21000
Volkswagen     21600
Volvo          29000
Hummer         41400
Audi           52642
Mercedes       57127
--
Volvo
Bentley
Citroen
--
page 2 of 3, 8 rows in total
Volvo
Bentley
Citroen

SqlKata aggregates

The aggregate methods Count, Sum, Average, Min, and Max execute the query and return a single value. The type parameter is the type of the result.

Program.cs
using Npgsql;
using SqlKata.Compilers;
using SqlKata.Execution;

string cs = "User ID=postgres;Password=passwd;Host=localhost;Database=testdb";

using var con = new NpgsqlConnection(cs);
con.Open();

var db = new QueryFactory(con, new PostgresCompiler());

var count = db.Query("cars").Count<int>();
var max = db.Query("cars").Max<int>("price");
var avg = db.Query("cars").Average<double>("price");
var sum = db.Query("cars").Sum<long>("price");

Console.WriteLine($"{count} cars, max {max}, average {avg:F2}, total {sum}");

var expensive = db.Query("cars").Where("price", ">", 40000).Exists();
Console.WriteLine(expensive);

The program computes statistics of the car prices.

var expensive = db.Query("cars").Where("price", ">", 40000).Exists();

The Exists method asks the database whether any row matches, which is cheaper than counting.

$ dotnet run
8 cars, max 350000, average 72721.12, total 581769
True

SqlKata insert row

In the next example, we insert new rows into the table.

Program.cs
using Npgsql;
using SqlKata.Compilers;
using SqlKata.Execution;

string cs = "User ID=postgres;Password=passwd;Host=localhost;Database=testdb";

using var con = new NpgsqlConnection(cs);
con.Open();

var db = new QueryFactory(con, new PostgresCompiler());

int n = db.Query("cars").Insert(new
{
    name = "Toyota",
    price = 27890
});

Console.WriteLine($"{n} row inserted");

// the id of the new row
int id = db.Query("cars").InsertGetId<int>(new
{
    name = "Mazda",
    price = 24500
});

Console.WriteLine($"new id: {id}");

// insert several rows at once
db.Query("cars").Insert(new[] { "name", "price" }, new[]
{
    new object[] { "Fiat", 12000 },
    new object[] { "Seat", 14000 }
});

Console.WriteLine(db.Query("cars").Count<int>());

The Insert method generates an INSERT SQL statement.

int n = db.Query("cars").Insert(new
{
    name = "Toyota",
    price = 27890
});

The properties of an anonymous object, or the entries of a dictionary, become the columns and values. The method returns the number of affected rows.

int id = db.Query("cars").InsertGetId<int>(new { name = "Mazda", price = 24500 });

InsertGetId inserts a row and returns the generated primary key. For PostgreSQL, it uses the RETURNING clause, and for SQL Server the SCOPE_IDENTITY() function.

db.Query("cars").Insert(new[] { "name", "price" }, new[]
{
    new object[] { "Fiat", 12000 },
    new object[] { "Seat", 14000 }
});

This overload inserts several rows with one statement: the first argument is the list of columns and the second the rows of values.

$ dotnet run
1 row inserted
new id: 10
12

SqlKata update row

The Update method generates an UPDATE statement. As with SQL itself, an update without a Where clause changes all rows.

Program.cs
using Npgsql;
using SqlKata.Compilers;
using SqlKata.Execution;

string cs = "User ID=postgres;Password=passwd;Host=localhost;Database=testdb";

using var con = new NpgsqlConnection(cs);
con.Open();

var db = new QueryFactory(con, new PostgresCompiler());

int n = db.Query("cars").Where("name", "Toyota").Update(new { price = 28500 });
Console.WriteLine($"{n} row updated");

var car = db.Query("cars").Where("name", "Toyota").First();
Console.WriteLine($"{car.name} {car.price}");

// increments
db.Query("cars").Where("name", "Toyota").Increment("price", 500);
Console.WriteLine(db.Query("cars").Where("name", "Toyota").Select("price").First<int>());

The program changes the price of the new Toyota.

db.Query("cars").Where("name", "Toyota").Increment("price", 500);

The Increment and Decrement methods change a column relative to its current value, in a single statement, which is safe against concurrent changes.

Console.WriteLine(db.Query("cars").Where("name", "Toyota").Select("price").First<int>());

To read a single column, we select it and get the first row as the given type.

$ dotnet run
1 row updated
Toyota 28500
29000

SqlKata delete row

In the next example, we delete rows from the table.

Program.cs
using Npgsql;
using SqlKata.Compilers;
using SqlKata.Execution;

string cs = "User ID=postgres;Password=passwd;Host=localhost;Database=testdb";

using var con = new NpgsqlConnection(cs);
con.Open();

var db = new QueryFactory(con, new PostgresCompiler());

int id = 9;
int n = db.Query("cars").Where("id", id).Delete();

if (n == 1)
{
    Console.WriteLine("row successfully deleted");
}

int removed = db.Query("cars").WhereIn("name", new[] { "Mazda", "Fiat", "Seat", "Toyota" }).Delete();
Console.WriteLine($"{removed} more rows deleted");
Console.WriteLine(db.Query("cars").Count<int>());

The program deletes the row with the id equal to 9 and then the rows added by the previous examples.

int n = db.Query("cars").Where("id", id).Delete();

To create the statement, we use the Query, Where, and Delete method calls. The returned value is the number of deleted rows.

$ dotnet run
row successfully deleted
3 more rows deleted
8

SqlKata joins, grouping and subqueries

Queries over several tables use Join, and we can group, aggregate, and nest queries.

Program.cs
using Npgsql;
using SqlKata;
using SqlKata.Compilers;
using SqlKata.Execution;

string cs = "User ID=postgres;Password=passwd;Host=localhost;Database=testdb";

using var con = new NpgsqlConnection(cs);
con.Open();

var db = new QueryFactory(con, new PostgresCompiler());

var rows = db.Query("orders")
    .Join("customers", "customers.id", "orders.customer_id")
    .Join("cars", "cars.id", "orders.car_id")
    .Select("customers.name as customer", "cars.name as car", "cars.price")
    .OrderBy("customers.name")
    .Get();

foreach (var row in rows)
{
    Console.WriteLine($"{row.customer,-10} {row.car,-10} {row.price,7}");
}

Console.WriteLine("--");

var summary = db.Query("orders")
    .Join("customers", "customers.id", "orders.customer_id")
    .Join("cars", "cars.id", "orders.car_id")
    .GroupBy("customers.name")
    .Select("customers.name")
    .SelectRaw("count(*) as orders, sum(cars.price) as total")
    .Get();

foreach (var row in summary)
{
    Console.WriteLine($"{row.name,-10} {row.orders} {row.total}");
}

Console.WriteLine("--");

// a subquery
var average = new Query("cars").AsAverage("price");

var aboveAverage = db.Query("cars")
    .Where("price", ">", average)
    .OrderBy("name")
    .Get();

foreach (var car in aboveAverage)
{
    Console.WriteLine(car.name);
}

The program lists the orders of the customers, summarizes them per customer, and finds the cars with a price above the average.

var rows = db.Query("orders")
    .Join("customers", "customers.id", "orders.customer_id")
    .Join("cars", "cars.id", "orders.car_id")
    .Select("customers.name as customer", "cars.name as car", "cars.price")

Join takes the joined table and the two columns to compare; the default is an inner join, and LeftJoin and RightJoin are also available. Column names qualified with the table, and aliases written with as, are quoted properly.

.SelectRaw("count(*) as orders, sum(cars.price) as total")

SelectRaw inserts a fragment of SQL as it is, for the functions the builder does not know. Never put user input into raw fragments; use bindings, such as WhereRaw("price > ?", 100).

var average = new Query("cars").AsAverage("price");

var aboveAverage = db.Query("cars")
    .Where("price", ">", average)

A Query object can be used as a value in a condition, which creates a subquery. AsAverage builds the aggregate as a part of the query instead of executing it.

$ dotnet run
Jane Doe   Mercedes     57127
John Doe   Audi         52642
John Doe   Skoda         9000
--
John Doe   2 61642
Jane Doe   1 57127
--
Bentley

SqlKata map rows to objects

With a type argument, Get maps the rows to objects. Dapper does the mapping, so the column names are matched to the property names (case-insensitively) and constructor parameters.

Program.cs
using Npgsql;
using SqlKata.Compilers;
using SqlKata.Execution;

string cs = "User ID=postgres;Password=passwd;Host=localhost;Database=testdb";

using var con = new NpgsqlConnection(cs);
con.Open();

var db = new QueryFactory(con, new PostgresCompiler());

var cars = db.Query("cars").OrderBy("id").Get<Car>();

foreach (var car in cars)
{
    Console.WriteLine(car);
}

var cheap = db.Query("cars").Where("price", "<", 15000).Get<Car>().ToList();
Console.WriteLine(cheap.Count);

record Car(int Id, string Name, int Price);

We get the cars as instances of the Car record.

$ dotnet run
Car { Id = 1, Name = Audi, Price = 52642 }
Car { Id = 2, Name = Mercedes, Price = 57127 }
Car { Id = 3, Name = Skoda, Price = 9000 }
Car { Id = 4, Name = Volvo, Price = 29000 }
Car { Id = 5, Name = Bentley, Price = 350000 }
Car { Id = 6, Name = Citroen, Price = 21000 }
Car { Id = 7, Name = Hummer, Price = 41400 }
Car { Id = 8, Name = Volkswagen, Price = 21600 }
1

For tables whose column names differ from the property names, either alias the columns in the query (Select("price as Cost")) or configure Dapper's column mapping.

SqlKata transactions and logging

A transaction is created on the connection, and passed to the methods that execute the queries.

Program.cs
using Npgsql;
using SqlKata.Compilers;
using SqlKata.Execution;

string cs = "User ID=postgres;Password=passwd;Host=localhost;Database=testdb";

using var con = new NpgsqlConnection(cs);
con.Open();

var db = new QueryFactory(con, new PostgresCompiler());

using var tx = con.BeginTransaction();

try
{
    db.Query("cars").Where("name", "Audi").Update(new { price = 1 }, tx);

    // violates the foreign key of the orders table
    db.Query("cars").Where("name", "Audi").Delete(tx);

    tx.Commit();
}
catch (Exception e)
{
    tx.Rollback();
    Console.WriteLine("rolled back: " + e.Message.Split(new[] { (char)10 })[0]);
}

Console.WriteLine(db.Query("cars").Where("name", "Audi").Select("price").First<int>());

// the QueryFactory can also log every compiled query
db.Logger = compiled => Console.WriteLine(compiled.ToString());
db.Query("cars").Where("id", 2).Select("name").First();

The program updates a car and then tries to delete it. The delete violates the foreign key from the orders table, so the whole transaction is rolled back.

db.Query("cars").Where("name", "Audi").Update(new { price = 1 }, tx);

The transaction is the last argument of Update, Delete, Get, and the other execution methods. Calls without it run outside of the transaction.

db.Logger = compiled => Console.WriteLine(compiled.ToString());

The Logger property receives every query that the factory executes, with the values inlined. This is useful for seeing what SQL is generated.

$ dotnet run
rolled back: 23503: update or delete on table "cars" violates foreign key constraint "orders_car_id_fkey" on table "orders"
52642
SELECT "name" FROM "cars" WHERE "id" = 2 LIMIT 1

The price of the Audi is unchanged after the rollback.

SQL Server example

We have worked with the PostgreSQL database. The next example shows the same library with SQL Server. Only the connection and the compiler change.

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

We add these three packages to our project.

cars_sqlserver.sql
DROP TABLE IF EXISTS cars;
CREATE TABLE cars(id INT PRIMARY KEY IDENTITY(1, 1), name VARCHAR(255), price INT);

INSERT INTO cars(name, price) VALUES('Audi',52642);
INSERT INTO cars(name, price) VALUES('Mercedes',57127);
INSERT INTO cars(name, price) VALUES('Skoda',9000);
INSERT INTO cars(name, price) VALUES('Volvo',29000);
INSERT INTO cars(name, price) VALUES('Bentley',350000);
INSERT INTO cars(name, price) VALUES('Citroen',21000);
INSERT INTO cars(name, price) VALUES('Hummer',41400);
INSERT INTO cars(name, price) VALUES('Volkswagen',21600);

We have the SQL for the SQL Server database. See the C# SQL Server tutorial for starting a server in a Docker container.

Program.cs
using Microsoft.Data.SqlClient;
using SqlKata.Compilers;
using SqlKata.Execution;

string cs = "Server=localhost,1433;Database=testdb;User Id=sa;Password=Passw0rd!2026;TrustServerCertificate=True";

using var con = new SqlConnection(cs);
con.Open();

var db = new QueryFactory(con, new SqlServerCompiler());

var cars = db.Query("cars").OrderBy("id").Get<Car>();

foreach (var car in cars)
{
    Console.WriteLine(car);
}

var top = db.Query("cars").OrderByDesc("price").Limit(2).Get<Car>();
Console.WriteLine(string.Join(", ", top.Select(c => c.Name)));

record Car(int Id, string Name, int Price);

In the example, we fetch rows from the table of an SQL Server database as objects.

using Microsoft.Data.SqlClient;

We use the Microsoft.Data.SqlClient provider; the older System.Data.SqlClient works as well.

var db = new QueryFactory(con, new SqlServerCompiler());

We use the SqlServerCompiler. The same query code works for all databases that SqlKata supports.

$ dotnet run
Car { Id = 1, Name = Audi, Price = 52642 }
Car { Id = 2, Name = Mercedes, Price = 57127 }
Car { Id = 3, Name = Skoda, Price = 9000 }
Car { Id = 4, Name = Volvo, Price = 29000 }
Car { Id = 5, Name = Bentley, Price = 350000 }
Car { Id = 6, Name = Citroen, Price = 21000 }
Car { Id = 7, Name = Hummer, Price = 41400 }
Car { Id = 8, Name = Volkswagen, Price = 21600 }
Bentley, Mercedes

The last line shows the two most expensive cars. For SQL Server, the compiler generates the OFFSET ... FETCH clause for Limit.

Source

SqlKata documentation

In this article we have shown how to program databases in C# with SqlKata.

Author

I am Jan Bodnar, a programmer and technical writer. I have been writing programming tutorials at ZetCode since 2007 and have published eight e-books. See about the author.

List all C# tutorials.