SqlKata Query Builder
Follow @ahmadmuzavi for the latest updates about SqlKata.
SqlKata Query Builder is a powerful Sql Query Builder written in C#.
It's secure and framework agnostic. Inspired by the top Query Builders available, like Laravel Query Builder, and Knex.
SqlKata has an expressive API. it follows a clean naming convention, which is very similar to the SQL syntax.
By providing a level of abstraction over the supported database engines, that allows you to work with multiple databases with the same unified API.
SqlKata supports complex queries, such as nested conditions, selection from SubQuery, filtering over SubQueries, Conditional Statements and others. Currently it has built-in compilers for SqlServer, MySql, PostgreSql and Firebird.
Checkout the full documentation on https://sqlkata.com
Installation
using dotnet cli
$ dotnet add package SqlKata
using Nuget Package Manager
PM> Install-Package SqlKata
Quick Examples
Setup Connection
var connection = new SqlConnection("...");
var compiler = new SqlCompiler();
var db = new QueryFactory(connection, compiler)
Retrieve all records
var books = db.Query("Books").Get();
Retrieve published books only
var books = db.Query("Books").WhereTrue("IsPublished").Get();
Retrieve one book
var introToSql = db.Query("Books").Where("Id", 145).Where("Lang", "en").First();
Retrieve recent books: last 10
var recent = db.Query("Books").OrderByDesc("PublishedAt").Limit(10).Get();
Include Author information
var books = db.Query("Books")
.Include(db.Query("Authors")) // Assumes that the Books table have a `AuthorId` column
.Get();
This will include the property "Author" on each "Book"
[{
"Id": 1,
"PublishedAt": "2019-01-01",
"AuthorId": 2
"Author": { // <-- included property
"Id": 2,
"...": ""
}
}]
Join with authors table
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}");
}
Conditional queries
var isFriday = DateTime.Today.DayOfWeek == DayOfWeek.Friday;
var books = db.Query("Books")
.When(isFriday, q => q.WhereIn("Category", new [] {"OpenSource", "MachineLearning"}))
.Get();
Pagination
var page1 = db.Query("Books").Paginate(10);
foreach(var book in page1.List)
{
Console.WriteLine(book.Name);
}
...
var page2 = page1.Next();
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();