Select

Select a single or many columns

new Query("Posts").Select("Id", "Title", "CreatedAt as Date");try 

SELECT [Id], [Title], [CreatedAt] AS [Date] FROM [Posts]

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

Select from a sub query

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

var query = new Query("Posts").Select("Id").Select(countQuery, "CommentsCount");try 

SELECT [Id], (SELECT COUNT(*) AS [count] FROM [Comments] WHERE [Comments].[PostId] = [Posts].[Id]) AS [CommentsCount] FROM [Posts]

Your friend when you need the full freedom

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

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

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]")try 

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`

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, Stars}"
    )try 

Same as writing

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

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