select.md

Select

Column

Select a single or many columns

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

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

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

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

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

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

Same as writing

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

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