Installing SqlKata

Installation
Project setup
Fetch Records
Query Logging
Inserting, Updating, Deleting

Building Queries

Introduction
Supported Databases

Features

Compiler
Select
From
Select
Where
String Operations
Date Operations
Limit / Offset
Join
Group
Order
Having
Union, Except, Intersect
Common Table Expression(with)
Useful Methods
Insert, Update, Delete

How to contribute

Clone Rep
Select
From
Select
Where

Installation

SqlKata provide an easy way to execute your queries, by using the famous package Dapper.

Installing Database Providers
In order to execute the queries, you need to install the needed Drivers for each Database Provider.

Sql Server
For Sql Server install System.Data.SqlClient package

dotnet add package System.Data.SqlClient

Postgre Sql
For Postgre Sql install Npgsql package

dotnet add package Npgsql

MySql For MySql install MySql.Data package

dotnet add package MySql.Data --version 8.0.9-dmr

Setup Your Project

In order to execute the queries, you have to create instances of XQuery, that supports the execution methods.

XQuery class
XQuery constructor requires two mandatory parameters, the Database connection (of type IDbConnection) instance and the Compiler instance.

Note: XQuery extends the Query class and support all methods supported by the later

var connection = new MySqlConnection(    
    "Host=localhost;Port=3306;User=user;Password=secret;Database=Users;SslMode=None"    
);
var compiler = new MySqlCompiler();    
var users = new XQuery(connection, compiler).From("Users").Limit(10).Get();

While this approach works as expected but it becomes easily cumbersome to use on big projects, since you have to keep reference for those required parameters, and on environments using DI containers.

In these cases it's recommended to use the QueryFactory factory class.

QueryFactory
As it's name imply, it helps in creating Executable Queries (XQuery) instances, without the hassle of keeping references for the IDbConnection and the Compiler instances.

var connection = new MySqlConnection(    
    "Host=localhost;Port=3306;User=user;Password=secret;Database=Users;SslMode=None"    
);   

var db = new QueryFactory(connection, new MySqlCompiler());    

// From now on, you can use the `db.Query()` method`   
var users = db.Query().From("Users").Get();   

// or more simpler
var users = db.Query("Users").Get();

Asp.Net Core DI Container
On Asp.Net core projects, you can setup your container to resolve the needed instances of the factory.

In Startup.cs

services.Add<QueryFactory>(() => {   

    // In real life you may read the configuration dynamically    
    var connection = new MySqlConnection(    
        "Host=localhost;Port=3306;User=user;Password=secret;Database=Users;SslMode=None"    
    );    

    var compiler = new MySqlCompiler();    

    return new QueryFactory(connection, compiler);    
});

and in the HomeController.cs

using SqlKata;      
using SqlKata.Execution;        
public class HomeController {        
    private readonly QueryFactory db;        
    public HomeController(QueryFactory db) {        
        this.db = db;       
    }     
    public IActionResult Index() {    
       // Here books is of type IEnumerable<dynamic>        
       var books = db.Query("Books").Where("IsPublished", true).Get();    
       // or IEnumerable<Book> if using the Generic overload         
       var books = db.Query("Books").Where("IsPublished", true).Get<Book>();    
       return Ok(books);      
    }       
}

Fetch Records

SqlKata provides the following methods to help executing your queries:

Get()
First()
FirstOrDefault()
Paginate()
Chunk()

Retrieving Records
By default calling the Get method returns an IEnumerable, giving you the max flexibility

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

IEnumerable<dynamic> users = db.Query("Users").Get();

However you can use the generic overload when you prefer strong types instead
IEnumerable<User> users = db.Query("Users").Get<User>();

Getting One Record
use the First or FirstOrDefault to get the first record of the query

var book = db.Query("Books").Where("Id", 1).First<Book>();
Note: The First and FirstOrDefault add the Limit(1) clause implicitly to the query, so there is no need to add it by yourself.

Data Pagination
To paginate your data, use the Paginate(pageNumber, perPage?) method instead of Get.

the Paginate method accept two parameters, the page number (1 based) and an optional perPage that defaults to 25, and return an instance of type PaginationResult.

The PaginationResult expose the Each property that implements the Enumerable interface so you can safely iterate over the returned data.

// users is of type `PaginationResult`
var users = query.Paginate(1, 10);    

foreach(var user in users.Each)   
{    
    Console.WriteLine($"Id: {user.Id}, Name: {user.Name}");    
}

Next and Previous
You can call the Next and Previous methods to get the Next/Previous page respectively.

var page1 = query.Paginate(1);
foreach(var item in page1.Each)    
{    
    // print items in the first page    
}

var page2 = page1.Next(); // same as query.Paginate(2)    

foreach(var item in page2.Each)    
{   
    // print items in the 2nd page   
}

Next and Previous Queries
Sometimes you may need to access the underlying queries for the next and previous methods. Use the NextQuery and PreviousQuery respectively in this case.

Accessing the queries can sometime more beneficial if you want more control, i.e. adding additional constraint.

var currentPage = query.Paginate(1, 10);    

foreach(var item in currentPage.Each)    
{    
    // print all books in the first page    
}   

var publishedInPage2 = currentPage.NextQuery().WhereTrue("IsPublished").Get();     

foreach(var item in publishedInPage2.Each)        
{   
    // print published books only in page 2     
}

Looping over all records example
This example may not be used in real cases, use the Chunk method instead if you need such functionality

var currentPage = db.Query("Books").OrderBy("Date").Paginate(1);     

while(currentPage.HasNext)    
{    
    Console.WriteLine($"Looping over the page: {currentPage.Page}");   
    foreach(var book in currentPage.Each)    
    {    
        // process book    
    }     
    currentPage = currentPage.Next();    
}

Data Chunks
Sometimes you may want to retrieve data in chunks to prevent loading the whole table one time in memory, for this you can use the Chunk method.

This is useful in situations where you have thousands of records.

query.Chunk(100, (rows, page) => {    
    Console.WriteLine($"Fetching page: {page}");    
    foreach(var row in rows)    
    {    
        // do something with row   
    }   
});

To stop retrieving chunks simply use the Chunk(int chunkSize, Func<IEnumerable, int, bool> func) overload and return false from the invoked action

query.Chunk(100, (rows, page) => {    
    // process rows    
    if(page == 3) {    
        // stop retrieving other chunks    
        return false;    
    }    
    // return true to continue    
    return true;    
});

Execute Raw Statements
If you want to execute free raw statements, use the QueryFactory.Select and QueryFactory.Statement methods.

var users = db.Select("exec sp_get_users_by_date @date", new {date = DateTime.UtcNow});
QueryFactory.Statement allows you to execute arbitrary statements like truncate table, create database, etc ...

db.Statement("truncate table Users");

Query Logging

SqlKata allows you to log your queries by implementing the Logger function on the QueryFactory class.

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

// Log the compiled query to the console   
db.Logger = compiled => {    
    Console.WriteLine(compiled.ToString());    
};

var users = db.Query("Users").Get();
Will print to the console
> SELECT * FROM [Users]

Insert / Update / Delete

SqlKata provides the following methods to help with updating/inserting/deleting against your database:

Update()
Insert()
InsertGetId()
Delete()

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

Update Existing Data

int affected = db.Query("Books").Where("Id", 1).Update(new {   
    Price = 18,   
    Status = "active",   
});

Insert One Record

int affected = db.Query("Books").Insert(new {   
    Title = "Introduction to C#",    
    Price = 18,   
    Status = "active",    
});

Insert One Record and get the Inserted Id
In version 1.1.0 we've added the ability to get the inserted id, in the database.

var id = db.Query("Books").InsertGetId<int>(new {    
    Title = "Introduction to Dart",    
    Price = 0,   
    Status = "active"   
});

Note: Currently this method is able to get the Id for single insert statements. Multiple records is not supported yet.

Insert Multiple Record

var cols = new [] {"Name", "Price"};    

var data = new [] {   
    new object[] { "A", 1000 },    
    new object[] { "B", 2000 },   
    new object[] { "C", 3000 },   
};   

db.Query("Products").Insert(cols, data);

Insert From Existing Query

var articlesQuery = new Query("Articles").Where("Type", "Book").Limit(100);   
var columns = new [] { "Title", "Price", "Status" };    

int affected = db.Query("Books").Insert(columns, articlesQuery);

Delete
int affected = db.Query("Books").Where("Status", "inactive").Delete();

Introduction to query building

An elegant Query Builder and Executor helps you deal with SQL queries in an elegant and predictable way.

Written in C#, the language we all love, you can check the source code on SqlKata on Github

It uses parameter binding technique to protect your application against SQL injection attacks. There is no need to clean strings being passed as bindings.

In addition to protection against SQL injection attacks, this technique speeds up your query execution by letting the SQL engine caches and reuses the same query plan even if the parameters are changed.

IEnumerable<Post> posts = await db.Query("Posts")    
     .Where("Likes", ">", 10)       
     .WhereIn("Lang", new [] {"en", "fr"})     
     .WhereNotNull("AuthorId")      
     .OrderByDesc("Date")      
     .Select("Id", "Title")       
     .GetAsync<Post>();   
      
SELECT [Id], [Title] FROM [Posts] WHERE       
  [Likes] > @p1 AND      
  [Lang] IN ( @p2, @p3 ) AND          
  [AuthorId] IS NOT NULL       
ORDER BY [Date] DESC

Installation

SqlKata is supported on both net fx and netstandard frameworks.

Run the following commands from your terminal to include it in your project.

Using dotnet cli

dotnet add package SqlKata
dotnet add package SqlKata.Execution

Or from the Package Manager Console

Install-Package SqlKata
Install-Package SqlKata.Execution

Supported Databases

MySQL
Sqlite
Postgres
Sql Server
Oracle
Firebird

Features

Compilers

Compilers are the component responsible to transform a Query instance to a SQL string that can be executed directly by the database engine.

Supported compilers Currently, SqlKata query builder supports natively the following compilers Sql Server, SQLite, MySql, PostgreSql, Oracle and Firebird.

Some noticeable difference Theoretically the output of different compilers should be similar, this is true for the 80% of the cases, however in some edge cases the output can be very different, for instance take a look how the Limit and Offset clause get compiled in each compiler

new Query("Posts").Limit(10).Offset(20);

Sql Server
SELECT * FROM [Posts] ORDER BY (SELECT 0) OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

Legacy Sql Server (< 2012)

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [row_num] FROM [Posts]
) WHERE [row_num] BETWEEN 21 AND 30

MySql
SELECT * FROM Posts LIMIT 10 OFFSET 20

PostgreSql

SELECT * FROM "Posts" LIMIT 10 OFFSET 20

In this documentation, we will display the queries compiled by the SqlServer Compiler only, except for the queries where the output is not the same.

Supporting Legacy SqlServer < 2012 Set the UseLegacyPagination flag to true if you want to target legacy Sql Server.

var compiler = new SqlServerCompiler {
     UseLegacyPagination = true
 }

Select

Select a single or many columns

new Query("Posts").Select("Id", "Title", "CreatedAt as Date");    
SELECT [Id], [Title], [CreatedAt] AS [Date] FROM [Posts]

Note: You can use the as keyword to alias a column in the select list

Sub query
Select from a sub query

var countQuery = new Query("Comments").WhereColumns("Comments.PostId", "Posts.Id").AsCount();   

new Query("Posts").Select("Id").Select(countQuery, "CommentsCount");    
SELECT [Id], (SELECT COUNT(*) AS [count] FROM [Comments] WHERE [Comments].[PostId] = [Posts].[Id]) AS [CommentsCount] FROM [Posts]

Raw Your friend when you need the full freedom

new Query("Posts").Select("Id").SelectRaw("count(1) over(partition by AuthorId) as PostsByAuthor")   
SELECT [Id], count(1) over(partition by AuthorId) as PostsByAuthor FROM [Posts]

Identify columns and tables inside Raw
You can wrap your identifier inside [ and ] so they get recognized by SqlKata as an identifier, so we can rewrite the same example above as

new Query("Posts").Select("Id").SelectRaw("count(1) over(partition by [AuthorId]) as [PostsByAuthor]")

Now AuthorId and PostsByAuthor get wrapped with the compiler identifiers, this is helpful especially for case sensitive engine like PostgreSql.

In SqlServer
SELECT [Id], count(1) over(partition by [AuthorId]) as [PostsByAuthor] FROM [Posts]

In Postgres
SELECT "Id", count(1) over(partition by "AuthorId") as "PostsByAuthor" FROM "Posts"

In MySql
SELECT `Id`, count(1) over(partition by `AuthorId`) as `PostsByAuthor` FROM `Posts`

Expanding Columns Expression (Braces Expansions)
Starting v1.1.2, you can use the Braces Expansions feature, to select multiple columns at the same time. This will allow
you to write the same query in a more compact way.

new Query("Users")   
    .Join("Profiles", "Profiles.UserId", "Users.Id")   
    .Select("Users.{Id, Name, LastName}", "Profiles.{GithubUrl, Website}")

Same as writing

new Query("Users")   
    .Join("Profiles", "Profiles.UserId", "Users.Id")   
    .Select("Users.Id", "Users.Name", "Users.LastName", "Profiles.GithubUrl", "Profiles.Website")   

SELECT [Users].[Id], [Users].[Name], [Users].[LastName], [Profiles].[GithubUrl], [Profile].[Website] FROM [Users]   
    JOIN [Profiles] ON [Profiles.UserId] = [Users].[Id]

From

The Query constructor takes an optional parameter to set the from clause

new Query("Posts");
Or you can use the From method to set it

new Query().From("Posts");
SELECT * FROM [Posts]
Alias To alias the table you should use the as syntax

new Query("Posts as p")
SELECT * FROM [Posts] AS [p]

From a Sub Query
You can select from a sub query by passing a Query instance to the From method or you can use the Lambda function overload.

var fewMonthsAgo = DateTime.UtcNow.AddMonths(-6);   
var oldPostsQuery = new Query("Posts").Where("Date", "<", fewMonthsAgo).As("old");   

new Query().From(oldPostsQuery).OrderByDesc("Date");   
SELECT * FROM (SELECT * FROM [Posts] WHERE [Date] < '2017-06-01 6:31:26') AS [old] ORDER BY [Date] DESC

You can rewrite the same query by using the Lambda function overload

new Query().From(q =>   
    q.From("Posts").Where("Date", "<", fewMonthsAgo).As("old")   
).OrderByDesc("Date");

From a Raw expression
The FromRaw method let you write raw expressions.

for example in SqlServer you can use the TABLESAMPLE to get a 10% sample of the total rows in the comments table.

var query = new Query().FromRaw("Comments TABLESAMPLE SYSTEM (10 PERCENT)")    
SELECT * FROM Comments TABLESAMPLE SYSTEM (10 PERCENT)

Where

SqlKata offers many useful methods to make it easy writing Where conditions.

All these methods comes with overloads for the NOT and OR operators.

So you can use OrWhereNull to apply a boolean OR operator and WhereNotNull or OrWhereNotNull to negate the condition.

Basic Where
The second parameter of the where method is optional and defaulting to = if omitted, so these two statements are totally the same.

new Query().Where("Id", 10);    

// since `=` is the default operator     
new Query().Where("Id", "=", 10);     
new Query("Posts").WhereFalse("IsPublished").Where("Score", ">", 10);     
SELECT * FROM [Posts] WHERE [IsPublished] = 0 AND [Score] > 10

Note: The same apply for WhereNot, OrWhere and OrWhereNot.

Multiple fields
If you want to filter your query against multiple fields, pass an object that represents col/values.

var query = new Query("Posts").Where(new {   
    Year = 2017 ,    
    CategoryId = 198 ,      
    IsPublished = true,   
});   
SELECT * FROM [Posts] WHERE [Year] = 2017 AND [CategoryId] = 198 AND [IsPublished] = True

WhereNull, WhereTrue and WhereFalse
To filter against NULL, boolean true and boolean false values.

db.Query("Users").WhereFalse("IsActive").OrWhereNull("LastActivityDate");   
SELECT * FROM [Users] WHERE [IsActive] = 0 AND [LastActivityDate] IS NULL

Note: the above methods will put the values literally in the generated sql and do not use parameter bindings techniques.

Sub Query
You can pass a Query instance to compare a column against a sub query.

var averageQuery = new Query("Posts").AsAverage("score");

new Query("Posts").Where("Score", ">", averageQuery);
SELECT * FROM [Posts] WHERE [Score] > (SELECT AVG([score]) AS [avg] FROM [Posts])

Note: The sub query should return one scalar cell to compare with, so you may need to set Limit(1) and select one column if needed

Nested conditions and Grouping
To group your conditions, just wrap them inside another Where block.

new Query("Posts").Where(q =>   
    q.WhereFalse("IsPublished").OrWhere("CommentsCount", 0)   
);   
SELECT * FROM [Posts] WHERE ([IsPublished] = 0 OR [CommentsCount] = 0)

Comparing two columns
Use this method when you want to compare two columns together.

new Query("Posts").WhereColumns("Upvotes", ">", "Downvotes");     
SELECT * FROM [Posts] WHERE [Upvotes] > [Downvotes]

Where In
Pass an IEnumerable to apply the SQL WHERE IN condition.

`new Query("Posts").WhereNotIn("AuthorId", new [] {1, 2, 3, 4, 5});`      
`SELECT * FROM [Posts] WHERE [AuthorId] NOT IN (1, 2, 3, 4, 5)`

You can pass a Query instance to filter against a sub query

var blocked = new Query("Authors").Where("Status", "blocked").Select("Id");     

new Query("Posts").WhereNotIn("AuthorId", blocked);     
SELECT * FROM [Posts] WHERE [AuthorId] NOT IN (SELECT [Id] FROM [Authors] WHERE [Status] = 'blocked')

Note: The sub query should return one column

Where Exists
To select all posts that have at least one comment.

new Query("Posts").WhereExists(q =>    
    q.From("Comments").WhereColumns("Comments.PostId", "=", "Posts.Id")    
);

In Sql Server
SELECT * FROM [Posts] WHERE EXISTS (SELECT TOP (1) 1 FROM [Comments] WHERE [Id] = [Posts].[Id])

In PostgreSql
SELECT * FROM "Posts" WHERE EXISTS (SELECT 1 FROM "Comments" WHERE "Id" = "Posts"."Id" LIMIT 1)

SqlKata tries to optimize the EXISTS query by disregarding the selected columns and limiting the result to 1 in order to provide a consistent behavior across all database engines.

Where Raw
The WhereRaw methods allow you to write anything not supported by the methods above, so it will give you the maximum flexibility.

new Query("Posts").WhereRaw("lower(Title) = ?", "sql");
SELECT * FROM [Posts] WHERE lower(Title) = 'sql'

Sometimes it's useful to wrap your table/columns by the engine identifier, this is helpful when the database is case sensitive like in PostgreSql, to do so just wrap your string with [ and ] and SqlKata will put the correspondent identifiers.

new Query("Posts").WhereRaw("lower([Title]) = ?", "sql");

In Sql Server
SELECT * FROM [Posts] WHERE lower([Title]) = 'sql'

In PostgreSql
SELECT * FROM "Posts" WHERE lower("Title") = 'sql'

String Operations

SqlKata Query Builder provides WhereStarts, WhereEnds, WhereContains and WhereLike methods to deal with string like types.

By default all string operations are case insensitive, by applying the database LOWER() and converting the value provided to lowercase.

To override this behavior you can pass a boolean true to the last parameter caseSensitive of these methods.
new Query("Posts").WhereEnds("Title", "Book")
SELECT * FROM [Posts] WHERE LOWER([Title]) LIKE '%book'

Using the case sensitive overload
new Query("Posts").WhereStarts("Title", "Book", true)
SELECT * FROM [Posts] WHERE [Title] LIKE Book%

Using the native WhereLike method
new Query("Posts").WhereLike("Title", "Book")
SELECT * FROM "Posts" WHERE LOWER("Title") LIKE 'book'

Date Operations

SqlKata Query Builder provides WhereDate, WhereTime and WhereDatePart methods to deal with date columns.
this is useful if you want to query against a specific date part of the column.

WhereDate
let you query against the date part of a datetime column

new Query("Posts").WhereDate("CreatedAt", "2018-04-01");

In Sql Server
SELECT * FROM [Posts] WHERE CAST([CreatedAt] as date) = '2018-04-01'

In PostgreSql
SELECT * FROM "Posts" WHERE "CreatedAt"::date = '2018-04-01'

In MySql
SELECT * FROM Posts WHERE DATE(CreatedAt) = '2018-04-01'

WhereTime
let you query against the time part of a datetime column

new Query("Posts").WhereTime("CreatedAt", ">", "16:30");

In Sql Server
SELECT * FROM [Posts] WHERE CAST([CreatedAt] as time) > '16:30'

In PostgreSql
SELECT * FROM "Posts" WHERE "CreatedAt"::time > '16:30'

In MySql
SELECT * FROM `Posts` WHERE TIME(`CreatedAt`) > '16:30'
WhereDatePart
WhereDatePart accept a datePart argument to specify the part you want to query against, the available options are: date, time, year, month, day, hour and minute.

For example to get the posts created in the first of February.
new Query("Posts").WhereDatePart("day", "CreatedAt", 1).WhereDatePart("month", "CreatedAt", 2);

In Sql Server
SELECT * FROM [Posts] WHERE DATEPART(DAY, [CreatedAt]) = 1 AND DATEPART(MONTH, [CreatedAt]) = 2
In Postgres
SELECT * FROM "Posts" WHERE DATE_PART('DAY', "CreatedAt") = 1 AND DATE_PART('MONTH', "CreatedAt") = 2

In MySql
SELECT * FROM `Posts` WHERE DAY(`CreatedAt`) = 1 AND MONTH(`CreatedAt`) = 2

Limit & Offset

Limit and Offset allows you to limit the number of results returned from the database, this method is highly correlated with the OrderBy and OrderByDesc methods.

var latestPosts = new Query("Posts").OrderByDesc("Date").Limit(10)

In Sql Server
SELECT TOP (10) * FROM [Posts] ORDER BY [Date] DESC

In PostgreSql
SELECT * FROM "Posts" ORDER BY "Date" DESC LIMIT 10

In MySql
SELECT * FROM `Posts` ORDER BY `Date` DESC LIMIT 10

Skipping records (Offset)
if you want to skip some records, use the Offset method.

var latestPosts = new Query("Posts").OrderByDesc("Date").Limit(10).Offset(5);

In Sql Server
SELECT * FROM [Posts] ORDER BY [Date] DESC OFFSET 5 ROWS FETCH NEXT 10 ROWS

In Legacy Sql Server (< 2012)
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY [Date] DESC) AS [row_num] FROM [Posts]) AS [subquery] WHERE [row_num] BETWEEN 6 AND 15

In PostgreSql
SELECT * FROM "Posts" ORDER BY "Date" DESC LIMIT 10 OFFSET 5

In MySql
SELECT * FROM `Posts` ORDER BY `Date` DESC LIMIT 10 OFFSET 5
Data pagination
You can use the ForPage method to easily paginate your data.

var posts = new Query("Posts").OrderByDesc("Date").ForPage(2);
By default this method will return 15 rows per page, you can override this value by passing an integer as a 2nd parameter.

Note: ForPage is 1-based so pass 1 for the first page
var posts = new Query("Posts").OrderByDesc("Date").ForPage(3, 50);
Skip & Take
If you are coming from a Linq background here is a bonus for you. You can use the Skip and Take methods as aliases for Offset and Limit enjoy :)

Join

Basic Join
To apply an inner join use the Join method

var query = new Query("Posts").Join("Authors", "Authors.Id", "Posts.AuthorId");
The methods LeftJoin, RightJoin and CrossJoin have the same signature as the Join method.

SELECT * FROM [Posts] INNER JOIN [Authors] ON [Authors].[Id] = [Posts].[AuthorId]
The 4th parameter is optional and default to =, pass any other operator to override the join operator.

var query = new Query("Posts").Join("Comments", "Comments.Date", "Posts.Date", ">");

Join with a Sub Query
var topComments = new Query("Comments").OrderByDesc("Likes").Limit(10);

var posts = new Query("Posts").LeftJoin(
topComments.As("TopComments"), // Don't forget to alias the sub query
j => j.On("TopComments.PostId", "Posts.Id")
);

SELECT * FROM [Posts] LEFT JOIN ( SELECT TOP (10) * FROM [Comments] ORDER BY [Likes] DESC
) AS [TopComments] ON ([TopComments].[PostId] = [Posts].[Id])
Warning: Always alias your sub queries with the As method

Advanced conditions
In some advanced cases you may need to apply some constraints on the join clause.

var comments = new Query("comments").LeftJoin("Posts", j =>
j.On("Posts.Id", "Comments.Id").WhereNotNull("Comments.AuthorId")
);
SELECT * FROM [comments] LEFT JOIN [Posts] ON ([Posts].[Id] = [Comments].[Id] AND [Comments].[AuthorId] IS NOT NULL)

Group

GroupBy
var query = new Query("Comments")
.Select("PostId")
.SelectRaw("count(1) as count")
.GroupBy("PostId");
SELECT [PostId], count(1) as count FROM [Comments] GROUP BY [PostId]
GroupByRaw var query = new Query("Companies")
.Select("Profit")
.SelectRaw("COUNT(*) as count")
.GroupByRaw("Profit WITH ROLLUP");

In PostgreSql
SELECT "Profit", COUNT(*) as count FROM "Companies" GROUP BY Profit WITH ROLLUP

Order

OrderBy
var query = new Query("Comments").OrderBy("Date").OrderByDesc("Name");
SELECT * FROM [Comments] ORDER BY [Date], [Name] DESC

OrderByRaw
var query = new Query("Comments").OrderByRaw("[Likes] DESC NULLS LAST")

In PostgreSql
SELECT * FROM "Comments" ORDER BY "Likes" DESC NULLS LAST

Having

var commentsCount = new Query("Comments").Select("PostId").SelectRaw("count(1) as Count").GroupBy("PostId");

var query = new Query().From(commentsCount).Having("Count", ">", 100);
SELECT * FROM (SELECT [PostId], count(1) as Count FROM [Comments] GROUP BY [PostId]) HAVING [Count] > 100

HavingRaw var query = new Query("Comments").Select("PostId").SelectRaw("count(1) as Count").GroupBy("PostId").HavingRaw("count(1) > 50");

SELECT [PostId], count(1) as Count FROM [Comments] GROUP BY [PostId] HAVING count(1) > 50

Union / Except / Intersect

SqlKata allows you to combine multiple queries using one of the following available operators union, intersect and except by providing the following methods Union, UnionAll, Intersect, IntersectAll, Except and ExceptAll.

the above methods accept either an instance of Query or a labmda expression

var phones = new Query("Phones");
var laptops = new Query("Laptops");

var mobiles = laptops.Union(phones);
(SELECT * FROM [Laptops]) UNION (SELECT * FROM [Phones])

Or by using the labmda overload

var mobiles = new Query("Laptops").ExceptAll(q => q.From("OldLaptops"));
(SELECT * FROM [Laptops]) EXCEPT ALL (SELECT * FROM [OldLaptops])

Combining Raw Expressions
You can always use the CombineRaw method to append raw expressions

var mobiles = new Query("Laptops").CombineRaw("union all select * from OldLaptops");

SELECT * FROM [Laptops] union all select * from OldLaptops
Off course you can use the table identifier characters [ and ] to instruct SqlKata to wrap the tables/columns keywords.

var mobiles = new Query("Laptops").CombineRaw("union all select * from [OldLaptops]");
SELECT * FROM [Laptops] union all select * from [OldLaptops]

Common Table Expression(With)

A common table expression (CTE) can be thought of as a temporary result set, it has some advantages over the sub query approach.

In Sql, usually CTE is represented as a with clause.

With
To add a CTE to your query simply use the With method.

var activePosts = new Query("Comments")
.Select("PostId")
.SelectRaw("count(1) as Count")
.GroupBy("PostId")
.HavingRaw("count(1) > 100");

var query = new Query("Posts")
.With("ActivePosts", activePosts) // now you can consider ActivePosts as a regular table in the database
.Join("ActivePosts", "ActivePosts.PostId", "Posts.Id")
.Select("Posts.*", "ActivePosts.Count")
WITH [ActivePosts] AS (
SELECT [PostId], count(1) as Count FROM [Comments] GROUP BY [PostId] HAVING count(1) > 100
)
SELECT [Posts].*, [ActivePosts].[Count] FROM [Posts] INNER JOIN [ActivePosts] ON [ActivePosts].[PostId] = [Posts].[Id]

WithRaw
You can use the WithRaw method if you want to pass an Sql Expression.

var query = new Query("Posts")
.WithRaw("ActivePosts", "select PostId, count(1) as count from Comments having count(1) > ?", new [] {50}) // now you can consider ActivePosts as a regular table in the database
.Join("ActivePosts", "ActivePosts.PostId", "Posts.Id")
.Select("Posts.*", "ActivePosts.Count")
WITH [ActivePosts] AS (
select PostId, count(1) as count from Comments having count(1) > 50
)
SELECT [Posts].*, [ActivePosts].[Count] FROM [Posts] INNER JOIN [ActivePosts] ON [ActivePosts].[PostId] = [Posts].[Id]
As in the example above, you can pass an IEnumerable to pass custom binding.

Useful Methods

Sometimes you need to do some actions only when certain conditions are met, in these cases you can use the When(condition, whenTrue, whenFalse = null) method.

var query = db.Query("Transactions");

query.When(
amount > 0,
q => q.Select("Debit as Amount"),
q => q.Select("Credit as Amount"),
)
is the same as

var query = db.Query("Transactions");

if(amount > 0)
{
query.Select("Debit as Amount");
}
else
{
query.Select("Credit as Amount");
}
Off course you can use it to build any part of the query.

Clone
Querys instances are mutable, this means that modifying the copied query will also change the original query.

To make a real clone of the query instance, you should use the Clone method.

var baseQuery = new Query().Select("Id", "Name").Limit(10).OrderBy("Date");

var posts = baseQuery.Clone().From("Posts");
var authors = baseQuery.Clone().From("Authors").Limit(100); // override the limit value
var sites = baseQuery.Clone().From("Sites");
Engine specific queries
SqlKata allows you to tune your queries against specific engines by using the ForXXX methods.

This is helpful when you want to apply some native functions, that are available in some vendors and not in others.

Casting Example
var query = new Query("Posts")
.Select("Id", "Title")
.ForPostgres(q => q.SelectRaw("[Date]::date"))
.ForSqlServer(q => q.SelectRaw("CAST([Date] as DATE)"));

In Sql Server SELECT [Id], [Title], CAST([Date] as DATE) FROM [Posts]

In PostgreSql SELECT "Id", "Title", "Date"::date FROM "Posts"

In this example, Mysql isn't affected SELECTId, TitleFROMPosts` ``
Generating date series example
Another example is to generate a date series between two given dates, you can use the generate_series in PostgreSql, and use a Recursion CTE in SqlServer.

var now = DateTime.UtcNow;
var format = "yyyy-MM-dd";

DateTime from = now.AddDays(-5).ToString(format),
now.ToString(format);

var rangeQuery = new Query()
.ForPostgres(q =>
// everything written here is available to the Postgre Compiler only
q.FromRaw("generate_series ( ?::timestamp, ?::timestamp, '1 day'::interval) dates", new[] { from, to })
.SelectRaw("dates::date as date")
)

.ForSqlServer(q =>
// everything written here is available to the SqlServer Compiler only
q.WithRaw("range", @"SELECT CAST(? AS DATETIME) 'date'
UNION ALL
SELECT DATEADD(dd, 1, t.date) FROM range t WHERE DATEADD(dd, 1, t.date) <= ?", new[] { from, to }
)
.From("range")
)
Although it's quite complicated, don't worry just focus on the concept for now.

The following will output:

In Sql Server
WITH [range] AS (
SELECT CAST('2017-08-23' AS DATETIME) 'date'
UNION ALL
SELECT DATEADD(dd, 1, t.date)
FROM range t
WHERE DATEADD(dd, 1, t.date) <= '2017-08-28'
)
SELECT * FROM [range]

In PostgreSql
SELECT dates::date as date FROM generate_series ( '2017-08-23'::timestamp, '2017-08-28'::timestamp, '1 day'::interval) dates

Insert / Update / Delete

Insert var query = new Query("Books").AsInsert(new {
Title = "Toyota Kata",
CreatedAt = new DateTime(2009, 8, 4),
Author = "Mike Rother"
});
INSERT INTO [Books] ([Name], [CreatedAt], [Author]) VALUES ('Toyota Kata', '2009-08-04 00:00:00', 'Mike Rother')

Note: While executing the query you can get the inserted id using the InsertGetId() method

Insert Many
you can use the insert many overload to insert multiple records

var cols = new [] {"Name", "Price"};

var data = new [] {
new object[] { "A", 1000 },
new object[] { "B", 2000 },
new object[] { "C", 3000 },
};

var query = new Query("Products")
.AsInsert(cols, data);
INSERT INTO [Products] ([Name], [Price]) VALUES ("A", 1000), ("B", 2000), ("C", 3000)

Insert from Query
You can also insert records for the result of another Select Query.

var cols = new [] { "Id", "Name", "Address" };
new Query("ActiveUsers").AsInsert(cols, new Query("Users").Where("Active", 1));
INSERT INTO [ActiveUsers] ([Id], [Name], [Address]) SELECT * FROM [Users] WHERE [Active] = 1

Update
var query = new Query("Posts").WhereNull("AuthorId").AsUpdate(new {
AuthorId = 10
});
UPDATE [Posts] SET [AuthorId] = 10 WHERE [AuthorId] IS NULL

Delete
var query = new Query("Posts").Where("Date", ">", DateTime.UtcNow.AddDays(-30)).AsDelete();
DELETE FROM [Posts] WHERE [Date] > ?

SQLKata Wiki - FranFranRobles/querybuilder GitHub Wiki

Installing SqlKata

Building Queries

Features

How to contribute

Installation

Setup Your Project

Fetch Records

Query Logging

Insert / Update / Delete

Introduction to query building

Installation

Supported Databases

Features

Compilers

Select

From

Where

String Operations

Date Operations

Limit & Offset

Join

Group

Order

Having

Union / Except / Intersect

Common Table Expression(With)

Useful Methods

Insert / Update / Delete

⚠️ GitHub.com Fallback ⚠️

SQLKata Wiki - FranFranRobles/querybuilder GitHub Wiki

Installing SqlKata

Building Queries

Features

How to contribute

Installation

Setup Your Project

Fetch Records

Query Logging

Insert / Update / Delete

Introduction to query building

Installation

Supported Databases

Features

Compilers

Select

From

Where

String Operations

Date Operations

Limit & Offset

Join

Group

Order

Having

Union / Except / Intersect

Common Table Expression(With)

Useful Methods

Insert / Update / Delete

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️