SqlKata Query Builder Documentation

To use SqlKata for query building, install the core package. If you need to execute queries against a database using Dapper, you must also install the SqlKata.Execution package.

$ dotnet add package SqlKata
$ dotnet add package SqlKata.Execution # (optional) If you want the execution support

To execute queries, initialize a QueryFactory using a database connection and a SqlCompiler. The QueryFactory class is part of the SqlKata.Execution package.

var connection = new SqlConnection("...");
var compiler = new SqlCompiler();
var db = new QueryFactory(connection, compiler);

Use the db.Query(tableName) method to start building queries. Common retrieval methods include:

• .Get(): Retrieve all records matching the criteria.
• .First(): Retrieve the first record matching the criteria.
• .WhereTrue(column): Filter records where a boolean column is true.
• .Where(column, value): Filter records by a specific column value.
• .OrderByDesc(column).Limit(n).Get(): Retrieve the top n records sorted by a column in descending order.

The .Include(query) method allows you to automatically include related data into your result set. This assumes the primary table has a foreign key column corresponding to the related table's identity (e.g., Books table having an AuthorId column to match the Authors table).

var books = db.Query("Books")
    .Include(db.Query("Authors"))
    .Get();

Use .Paginate(pageSize) to handle result sets in chunks. The returned object contains a List of items for the current page and a .Next() method to fetch the subsequent page.

var page1 = db.Query("Books").Paginate(10);

foreach(var book in page1.List)
{
    Console.WriteLine(book.Name);
}

// Fetch the next page
var page2 = page1.Next();

The .When(condition, callback) method allows you to conditionally add clauses to your query based on a boolean expression. The callback provides a query object to apply the additional constraints.

var isFriday = DateTime.Today.DayOfWeek == DayOfWeek.Friday;

var books = db.Query("Books")
    .When(isFriday, q => q.WhereIn("Category", new [] {"OpenSource", "MachineLearning"}))
    .Get();

Use the .Join(table, first, second) method to perform standard SQL joins. You can combine this with .Select() to specify which columns to retrieve from both tables.

var books = db.Query("Books")
    .Join("Authors", "Authors.Id", "Books.AuthorId")
    .Select("Books.*", "Authors.Name as AuthorName")
    .Get();

foreach(var book in books)
{
    Console.WriteLine($"{book.Title}: {book.AuthorName}");
}

SqlKata supports basic CRUD operations using anonymous objects to represent the data:

• .Insert(object): Inserts a new record.
• .Where(...).Update(object): Updates existing records matching the criteria.
• .Where(...).Delete(): Deletes records matching the criteria.

// Insert
int affected = db.Query("Users").Insert(new {
    Name = "Jane",
    CountryId = 1
});

// Update
int affected = db.Query("Users").Where("Id", 1).Update(new {
    Name = "Jane",
    CountryId = 1
});

// Delete
int affected = db.Query("Users").Where("Id", 1).Delete();