sqlkata - SqlKata 2.2.0-ci-804

A powerful Dynamic Sql Query Builder supporting Sql Server, MySql, PostgreSql, Oracle and Firebird

PM> Install-Package SqlKata -Version 2.2.0-ci-804 -Source https://www.myget.org/F/sqlkata/api/v3/index.json

Copy to clipboard

> nuget.exe install SqlKata -Version 2.2.0-ci-804 -Source https://www.myget.org/F/sqlkata/api/v3/index.json

Copy to clipboard

> dotnet add package SqlKata --version 2.2.0-ci-804 --source https://www.myget.org/F/sqlkata/api/v3/index.json

Copy to clipboard
<PackageReference Include="SqlKata" Version="2.2.0-ci-804" />
Copy to clipboard
source https://www.myget.org/F/sqlkata/api/v3/index.json

nuget SqlKata  ~> 2.2.0-ci-804
Copy to clipboard

> choco install SqlKata --version 2.2.0-ci-804 --source https://www.myget.org/F/sqlkata/api/v2

Copy to clipboard
Import-Module PowerShellGet
Register-PSRepository -Name "sqlkata" -SourceLocation "https://www.myget.org/F/sqlkata/api/v2"
Install-Module -Name "SqlKata" -RequiredVersion "2.2.0-ci-804" -Repository "sqlkata" -AllowPreRelease
Copy to clipboard

SqlKata Query Builder

Twitter

Follow and Upvote SqlKata on Product Hunt to encourage the development of this project

SqlKata - Dynamic Sql query builder for dotnet | Product Hunt

Quick Demo

SqlKata Query Builder is a powerful Sql Query Builder written in C#.

It's secure and framework agnostic. Inspired by the top Query Builders available, like Laravel Query Builder and Knex.

SqlKata has an expressive API. it follows a clean naming convention, which is very similar to the SQL syntax.

By providing a level of abstraction over the supported database engines, that allows you to work with multiple databases with the same unified API.

SqlKata supports complex queries, such as nested conditions, selection from SubQuery, filtering over SubQueries, Conditional Statements and others. Currently, it has built-in compilers for SqlServer, MySql, PostgreSQL, and Firebird.

The SqlKata.Execution package provides the ability to submit the queries to the database, using Dapper under the covers.

Checkout the full documentation on https://sqlkata.com

Installation

$ dotnet add package SqlKata
$ dotnet add package SqlKata.Execution # (optional) If you want the execution support

Quick Examples

Setup Connection

var connection = new SqlConnection("...");
var compiler = new SqlCompiler();

var db = new QueryFactory(connection, compiler)

QueryFactory is provided by the SqlKata.Execution package.

Retrieve all records

var books = db.Query("Books").Get();

Retrieve published books only

var books = db.Query("Books").WhereTrue("IsPublished").Get();

Retrieve one book

var introToSql = db.Query("Books").Where("Id", 145).Where("Lang", "en").First();

Retrieve recent books: last 10

var recent = db.Query("Books").OrderByDesc("PublishedAt").Limit(10).Get();

Include Author information

var books = db.Query("Books")
    .Include(db.Query("Authors")) // Assumes that the Books table have a `AuthorId` column
    .Get();

This will include the property "Author" on each "Book"

[{
    "Id": 1,
    "PublishedAt": "2019-01-01",
    "AuthorId": 2,
    "Author": { // <-- included property
        "Id": 2,
        "...": ""
    }
}]

Join with authors table

var books = db.Query("Books")
    .Join("Authors", "Authors.Id", "Books.AuthorId")
    .Select("Books.*", "Authors.Name as AuthorName")
    .Get();

foreach(var book in books)
{
    Console.WriteLine($"{book.Title}: {book.AuthorName}");
}

Conditional queries

var isFriday = DateTime.Today.DayOfWeek == DayOfWeek.Friday;

var books = db.Query("Books")
    .When(isFriday, q => q.WhereIn("Category", new [] {"OpenSource", "MachineLearning"}))
    .Get();

Pagination

var page1 = db.Query("Books").Paginate(10);

foreach(var book in page1.List)
{
    Console.WriteLine(book.Name);
}

...

var page2 = page1.Next();

Insert

int affected = db.Query("Users").Insert(new {
    Name = "Jane",
    CountryId = 1
});

Update

int affected = db.Query("Users").Where("Id", 1).Update(new {
    Name = "Jane",
    CountryId = 1
});

Delete

int affected = db.Query("Users").Where("Id", 1).Delete();

FAQ

How to know when a new release or a feature is available?

I announce updates on My Twitter Account, and you can subscribe to our newsletters from the website https://sqlkata.com

The database that I want is not supported. Why?

It's impossible to support all available database vendors, this is why we focus on the major ones, and we encourage you to create your own compiler for your database.

Do you accept new compilers?

Unfortunately, no, the reason is this will add overhead for the project contributors. We prefer to improve the quality of the existing compilers instead.

How can I support the project?

  • Star the project here in Github, and share it with your friends
  • Follow and upvote it on Product Hunt SqlKata - Dynamic Sql query builder for dotnet | Product Hunt
  • You can also donate to support the project financially on open collection.
  • .NETFramework 4.5
    • System.Collections.Concurrent (>= 4.3.0)
  • .NETStandard 1.1
    • NETStandard.Library (>= 1.6.1)
    • System.Collections.Concurrent (>= 4.3.0)
  • .NETFramework 4.5: 4.5.0.0
  • .NETStandard 1.1: 1.1.0.0

Owners

clipse2g ahmad

Authors

Ahmad Moussawi

Project URL

https://github.com/sqlkata/querybuilder

License

Unknown

Tags

sql query-builder dynamic-query

Info

3601 total downloads
18 downloads for version 2.2.0-ci-804
Download (106.98 KB)
Found on the current feed only

Package history

Version Size Last updated Downloads Mirrored?
2.2.0-ci-812 57.13 KB Sun, 14 Mar 2021 14:52:18 GMT 13
2.2.0-ci-811 57.12 KB Sun, 14 Mar 2021 14:49:27 GMT 20
2.2.0-ci-810 57.11 KB Sun, 14 Mar 2021 14:45:12 GMT 18
2.2.0-ci-809 57.11 KB Sun, 14 Mar 2021 14:43:52 GMT 13
2.2.0-ci-808 57.12 KB Sun, 14 Mar 2021 14:42:11 GMT 19
2.2.0-ci-807 57.14 KB Sun, 14 Mar 2021 14:39:38 GMT 15
2.2.0-ci-806 57.11 KB Sun, 14 Mar 2021 14:34:44 GMT 17
2.2.0-ci-805 106.95 KB Sun, 14 Mar 2021 14:32:27 GMT 12
2.2.0-ci-804 106.98 KB Sun, 14 Mar 2021 14:28:37 GMT 18
2.2.0-ci-803 106.99 KB Fri, 19 Feb 2021 08:24:50 GMT 17
2.2.0-ci-802 106.99 KB Wed, 17 Feb 2021 11:19:35 GMT 14
2.2.0-ci-801 106.96 KB Wed, 17 Feb 2021 10:52:59 GMT 20
2.2.0-ci-800 106.96 KB Mon, 21 Dec 2020 16:19:22 GMT 21
2.2.0-ci-799 106.68 KB Wed, 11 Nov 2020 08:18:01 GMT 23
2.2.0-ci-798 106.12 KB Wed, 07 Oct 2020 14:40:47 GMT 22
2.2.0-ci-794 106.15 KB Sun, 20 Sep 2020 17:52:59 GMT 34
2.2.0-ci-791 104.38 KB Wed, 15 Jul 2020 14:47:12 GMT 23
2.2.0-ci-790 104.51 KB Mon, 08 Jun 2020 14:37:02 GMT 32
2.2.0-ci-788 104.49 KB Mon, 01 Jun 2020 08:19:57 GMT 18
2.2.0-ci-787 104.51 KB Mon, 01 Jun 2020 07:40:58 GMT 21
2.1.0-ci-786 104.53 KB Fri, 29 May 2020 13:15:40 GMT 27
2.1.0-ci-785 104.54 KB Fri, 29 May 2020 13:02:55 GMT 24
2.0.2-ci-783 104.51 KB Thu, 28 May 2020 19:44:36 GMT 23
2.0.2-ci-780 104.49 KB Thu, 28 May 2020 19:23:23 GMT 23
2.0.2-ci-778 104.47 KB Thu, 07 May 2020 16:10:35 GMT 23
2.0.2-ci-776 104.34 KB Mon, 20 Apr 2020 06:13:08 GMT 29
2.0.2-ci-775 104.39 KB Fri, 27 Mar 2020 19:00:58 GMT 24
2.0.2-ci-773 104.33 KB Sun, 26 Jan 2020 11:27:24 GMT 25
2.0.2-ci-771 104.27 KB Sun, 05 Jan 2020 13:57:42 GMT 29
1.1.8-CI-768 104.27 KB Sun, 05 Jan 2020 12:42:47 GMT 21
1.1.8-CI-767 104.36 KB Sun, 05 Jan 2020 12:40:46 GMT 26
1.1.8-CI-766 104.29 KB Sun, 05 Jan 2020 12:32:37 GMT 28
1.1.8-CI-765 104.3 KB Sun, 05 Jan 2020 12:22:33 GMT 20
1.1.8-CI-739 104.28 KB Wed, 01 Jan 2020 11:39:49 GMT 18
1.1.8-CI-738 104.29 KB Wed, 01 Jan 2020 11:07:21 GMT 21
1.1.8-CI-737 104.29 KB Wed, 01 Jan 2020 10:21:14 GMT 19
1.1.8-CI-735 104.32 KB Wed, 01 Jan 2020 10:16:38 GMT 27
1.1.8-CI-733 104.28 KB Wed, 01 Jan 2020 10:09:32 GMT 23
1.1.8-CI-732 104.27 KB Wed, 01 Jan 2020 10:06:33 GMT 20
1.1.8-CI-731 104.32 KB Wed, 01 Jan 2020 10:04:21 GMT 27
1.1.8-CI-730 104.29 KB Wed, 01 Jan 2020 10:02:10 GMT 20
1.1.8-CI-724 104.34 KB Fri, 27 Dec 2019 21:29:34 GMT 23
1.1.8-CI-722 104.28 KB Fri, 13 Dec 2019 06:28:57 GMT 19
1.1.8-CI-721 102.96 KB Tue, 03 Dec 2019 12:20:48 GMT 22
1.1.8-CI-720 102.97 KB Fri, 25 Oct 2019 22:30:35 GMT 23
1.1.8-ci-712 102.66 KB Wed, 07 Aug 2019 19:18:22 GMT 23
1.1.8-ci-711 102.68 KB Wed, 07 Aug 2019 19:15:22 GMT 23
1.1.8-ci-710 102.7 KB Wed, 07 Aug 2019 19:09:10 GMT 20
1.1.8-ci-709 102.68 KB Wed, 07 Aug 2019 19:02:46 GMT 24
1.1.8-ci-708 102.68 KB Wed, 07 Aug 2019 19:01:55 GMT 19
1.1.8-ci-707 102.69 KB Mon, 29 Jul 2019 12:54:38 GMT 23
1.1.8-ci-705 102.69 KB Fri, 28 Jun 2019 16:57:21 GMT 24
1.1.8-ci-703 102.69 KB Fri, 28 Jun 2019 16:21:14 GMT 22
1.1.8-ci-693 101.54 KB Mon, 10 Jun 2019 12:41:01 GMT 19
1.1.8-ci-692 100.52 KB Fri, 07 Jun 2019 12:03:06 GMT 24
1.1.8-ci-691 100.55 KB Wed, 29 May 2019 08:35:49 GMT 28
1.1.8-ci-688 100.77 KB Sat, 18 May 2019 16:57:18 GMT 22
1.1.8-ci-687 99.53 KB Thu, 02 May 2019 12:40:06 GMT 26
1.1.8-ci-686 99.03 KB Thu, 02 May 2019 12:38:08 GMT 22
1.1.8-ci-685 98.01 KB Wed, 24 Apr 2019 07:01:30 GMT 31
1.1.8-ci-684 95.91 KB Tue, 16 Apr 2019 08:47:32 GMT 22
1.1.8-ci-683 95.9 KB Mon, 15 Apr 2019 18:11:13 GMT 30
1.1.8-ci-681 95.71 KB Mon, 15 Apr 2019 16:19:13 GMT 24
1.1.8-ci-679 95.72 KB Mon, 15 Apr 2019 15:40:46 GMT 21
1.1.8-ci-677 94.88 KB Wed, 03 Apr 2019 15:56:47 GMT 24
1.1.8-ci-676 94.83 KB Wed, 03 Apr 2019 15:55:59 GMT 20
1.1.8-ci-675 94.6 KB Wed, 03 Apr 2019 11:40:25 GMT 21
1.1.8-ci-674 94.6 KB Tue, 26 Mar 2019 22:10:51 GMT 23
1.1.8-ci-673 94.63 KB Tue, 26 Mar 2019 21:57:25 GMT 22
1.1.8-0-718 102.97 KB Thu, 24 Oct 2019 10:29:18 GMT 23
1.1.8-0-717 102.98 KB Tue, 22 Oct 2019 19:40:53 GMT 18
1.1.8-0-716 102.99 KB Tue, 22 Oct 2019 19:17:40 GMT 25
1.1.8-0-714 102.87 KB Wed, 11 Sep 2019 10:44:26 GMT 26
1.1.8-0-713 102.69 KB Wed, 07 Aug 2019 19:27:36 GMT 22
1.1.7 94.62 KB Tue, 26 Mar 2019 21:52:22 GMT 24
1.1.7-ci-672 94.65 KB Tue, 26 Mar 2019 21:54:23 GMT 21
1.1.6 93.93 KB Thu, 21 Feb 2019 11:32:57 GMT 20
1.1.5 94.98 KB Tue, 08 Jan 2019 11:29:49 GMT 20
1.1.4 94.99 KB Fri, 04 Jan 2019 15:09:35 GMT 23
1.1.3 94.98 KB Sat, 29 Dec 2018 20:04:12 GMT 26
1.1.2 83.14 KB Fri, 28 Sep 2018 15:22:53 GMT 25
1.1.0 83.11 KB Thu, 27 Sep 2018 09:30:33 GMT 23
1.0.5 80.53 KB Mon, 24 Sep 2018 17:18:30 GMT 29
1.0.3 79.32 KB Fri, 03 Aug 2018 10:38:15 GMT 28
1.0.0 36.54 KB Sun, 05 Jan 2020 11:19:05 GMT 22
1.0.0-beta-553 76.76 KB Sun, 15 Jul 2018 19:22:46 GMT 26
1.0.0-beta-536 76.76 KB Sun, 15 Jul 2018 15:50:18 GMT 21
1.0.0-beta-535 72.25 KB Sat, 14 Jul 2018 06:51:00 GMT 22
1.0.0-beta-534 71.96 KB Sat, 14 Jul 2018 06:42:03 GMT 18
1.0.0-beta-533 71.4 KB Sat, 14 Jul 2018 06:16:43 GMT 22
1.0.0-beta-532 72.75 KB Fri, 06 Jul 2018 14:42:04 GMT 22
1.0.0-beta-531 72.64 KB Wed, 04 Jul 2018 13:11:10 GMT 27
1.0.0-beta-528 70.92 KB Wed, 27 Jun 2018 14:16:45 GMT 26
1.0.0-beta-527 70.88 KB Tue, 26 Jun 2018 22:21:08 GMT 23
1.0.0-beta-526 70.92 KB Tue, 26 Jun 2018 22:16:26 GMT 23
1.0.0-beta-525 80.76 KB Tue, 26 Jun 2018 22:12:47 GMT 23
1.0.0-beta-524 41.4 KB Tue, 26 Jun 2018 17:19:46 GMT 21
1.0.0-beta-510 70.03 KB Fri, 25 May 2018 08:07:54 GMT 28
1.0.0-beta-509 69.74 KB Fri, 18 May 2018 08:42:13 GMT 22
1.0.0-beta-507 69.87 KB Fri, 18 May 2018 08:12:11 GMT 20
1.0.0-beta-506 69.86 KB Fri, 18 May 2018 07:48:29 GMT 29
1.0.0-beta-485 69.64 KB Wed, 16 May 2018 09:07:57 GMT 26
1.0.0-beta-484 69.64 KB Mon, 14 May 2018 06:09:38 GMT 20
1.0.0-beta-483 69.64 KB Tue, 08 May 2018 08:31:54 GMT 20
1.0.0-beta-482 70.46 KB Tue, 08 May 2018 08:18:08 GMT 21
1.0.0-beta-481 70.53 KB Tue, 08 May 2018 08:13:48 GMT 30
1.0.0-beta-480 77.19 KB Tue, 08 May 2018 08:00:56 GMT 19
1.0.0-beta-479 77.19 KB Tue, 08 May 2018 07:58:55 GMT 21
1.0.0-beta-476 77.92 KB Thu, 03 May 2018 19:26:23 GMT 30
1.0.0-beta-475 77.81 KB Thu, 03 May 2018 14:57:23 GMT 28
1.0.0-beta-474 77.72 KB Tue, 01 May 2018 15:02:50 GMT 23
1.0.0-beta-473 77.42 KB Mon, 30 Apr 2018 08:44:26 GMT 18
1.0.0-beta-472 77.55 KB Fri, 27 Apr 2018 21:12:57 GMT 22
1.0.0-beta-471 77.74 KB Fri, 27 Apr 2018 09:26:49 GMT 24
1.0.0-beta-470 77.71 KB Wed, 25 Apr 2018 07:35:09 GMT 21
1.0.0-beta-469 75.51 KB Wed, 25 Apr 2018 06:53:11 GMT 24
1.0.0-beta-468 81.11 KB Tue, 24 Apr 2018 08:08:46 GMT 23
1.0.0-beta-467 80.96 KB Mon, 23 Apr 2018 14:38:04 GMT 21
1.0.0-beta-466 80.93 KB Fri, 20 Apr 2018 16:11:44 GMT 20
1.0.0-beta-465 80.94 KB Thu, 19 Apr 2018 15:35:48 GMT 32
1.0.0-beta-464 80.9 KB Thu, 19 Apr 2018 15:28:09 GMT 25
1.0.0-beta-463 80.96 KB Tue, 17 Apr 2018 08:41:56 GMT 17
1.0.0-beta-461 80.94 KB Tue, 17 Apr 2018 07:27:23 GMT 29
1.0.0-beta-460 80.69 KB Sun, 15 Apr 2018 20:17:45 GMT 24
1.0.0-beta-459 80.72 KB Sun, 15 Apr 2018 19:51:25 GMT 35
1.0.0-beta-458 80.74 KB Sun, 08 Apr 2018 02:43:44 GMT 21
1.0.0-beta-457 80.74 KB Sun, 08 Apr 2018 02:40:39 GMT 22
1.0.0-beta-455 80.67 KB Thu, 05 Apr 2018 13:11:50 GMT 25
1.0.0-beta-454 79.88 KB Thu, 29 Mar 2018 00:10:20 GMT 23
1.0.0-beta-453 79.87 KB Thu, 29 Mar 2018 00:03:47 GMT 21
1.0.0-beta-450 79.89 KB Wed, 28 Mar 2018 23:18:55 GMT 26
1.0.0-beta-449 79.89 KB Wed, 28 Mar 2018 23:17:12 GMT 20
1.0.0-beta-445 79.87 KB Wed, 28 Mar 2018 23:02:05 GMT 25
1.0.0-beta-442 79.89 KB Thu, 22 Mar 2018 11:20:29 GMT 26
1.0.0-beta-441 79.79 KB Wed, 21 Mar 2018 23:21:12 GMT 26
1.0.0-beta-440 79.79 KB Wed, 21 Mar 2018 23:16:35 GMT 18
1.0.0-beta-430 79.78 KB Sun, 04 Mar 2018 08:12:04 GMT 19
1.0.0-beta-429 80.28 KB Fri, 02 Mar 2018 21:35:09 GMT 18
1.0.0-beta-427 80.23 KB Wed, 14 Feb 2018 11:34:26 GMT 21
1.0.0-beta-426 80.21 KB Wed, 14 Feb 2018 10:50:50 GMT 22
1.0.0-beta-425 80.24 KB Wed, 14 Feb 2018 10:48:56 GMT 19
1.0.0-beta-424 80.25 KB Wed, 14 Feb 2018 10:37:19 GMT 22
1.0.0-beta-423 80.23 KB Mon, 12 Feb 2018 17:29:37 GMT 33
1.0.0-beta-422 80.22 KB Mon, 12 Feb 2018 16:33:27 GMT 23
1.0.0-beta-421 80.23 KB Mon, 12 Feb 2018 13:37:12 GMT 18
1.0.0-beta-420 79.88 KB Mon, 12 Feb 2018 13:08:37 GMT 19
1.0.0-beta-419 77.2 KB Sun, 11 Feb 2018 15:13:02 GMT 24
1.0.0-beta-411 77.07 KB Sun, 21 Jan 2018 12:06:21 GMT 17
1.0.0-beta-410 77.07 KB Sun, 21 Jan 2018 12:05:21 GMT 21
1.0.0-beta-409 77.08 KB Sun, 21 Jan 2018 11:14:53 GMT 21
1.0.0-beta-408 77.08 KB Sun, 21 Jan 2018 11:04:51 GMT 26
1.0.0-beta-407 77.08 KB Sun, 21 Jan 2018 10:47:32 GMT 20
1.0.0-beta-405 77.09 KB Sat, 20 Jan 2018 21:04:06 GMT 22
1.0.0-beta-379 77.1 KB Sat, 06 Jan 2018 17:58:35 GMT 21
1.0.0-beta-373 77.09 KB Fri, 05 Jan 2018 20:15:42 GMT 21
1.0.0-beta-369 76.97 KB Tue, 12 Dec 2017 16:39:35 GMT 23
1.0.0-beta-368 77.04 KB Mon, 27 Nov 2017 15:57:53 GMT 26
1.0.0-beta-357 77.04 KB Sat, 18 Nov 2017 18:04:26 GMT 19
1.0.0-beta-356 77.04 KB Sat, 18 Nov 2017 17:55:54 GMT 21