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

Add RowNumber Column *New

Query query = new Query();
query.Select("Id", "Name").From("MyTable").OrderBy("Name").AddRowNumber("RowNumber");
SqlKata.Compilers.SqlServerCompiler sqlServerCompiler = new SqlKata.Compilers.SqlServerCompiler();
string sqlResult = sqlServerCompiler.Compile(query).ToString();
//sqlResult -> SELECT ROW_NUMBER() OVER (ORDER BY [Name]) AS [RowNumber], [Id], [Name] FROM [MyTable]

Setup Connection

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

Get all records

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

Published books only

var books = db.Query("Books").WhereTrue("IsPublished").Get();

Get one book by Id

var introToSql = db.Query("Books").Where("Id", 145).Where("Lang", "en").First();

Recent books: last 10

var recent = db.Query("Books").OrderByDesc("PublishedAt").Limit(10).Get();

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();

josap/querybuilder