SqlKata - Dynamic Sql query builder for dotnet | Product Hunt

🎉 SqlKata is now on Product Hunt

Please upvote to support the product development



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

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]


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")
        "Users.{Id, Name, LastName}",
        "Profiles.{GithubUrl, Website, Stars}"

Same as writing

new Query("Users")
    .Join("Profiles", "Profiles.UserId", "Users.Id")
  INNER JOIN [Profiles] ON [Profiles].[UserId] = [Users].[Id]
Heap apparel


one email per month about tips & tricks, new features, and maybe community feedback