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.

//:playground
new Query("Posts").Where("Id", 10);

// since `=` is the default operator
new Query("Posts").Where("Id", "=", 10);

//:playground
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.

//:playground
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.

//:playground
db.Query("Users").WhereFalse("IsActive").OrWhereNull("LastActivityDate");

SELECT * FROM [Users] WHERE [IsActive] = cast(0 as bit) OR [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.

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

var query = 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.

//:playground
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.

//:playground
new Query("Posts").WhereColumns("Upvotes", ">", "Downvotes");

SELECT * FROM [Posts] WHERE [Upvotes] > [Downvotes]

Where In

Pass an IEnumerable<T> to apply the SQL WHERE IN condition.

//:playground
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

//:playground
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.

//:playground
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.

//:playground
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.

//:playground
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'