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:
SqlKata- the query builder and the compilers that turn a query into SQLSqlKata.Execution- theQueryFactorythat runs the queries through a database connection; internally, it uses Dapper
$ 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
In this article we have shown how to program databases in C# with SqlKata.
Author
List all C# tutorials.