Advanced methods

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

SELECT `Id`, `Title` FROM `Posts`

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

Off course you can use any method you want inside these lambdas

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