sqlkata - SqlKata 2.0.2-ci-771

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

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

Copy to clipboard

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

Copy to clipboard

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

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

nuget SqlKata  ~> 2.0.2-ci-771
Copy to clipboard

> choco install SqlKata --version 2.0.2-ci-771 --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.0.2-ci-771" -Repository "sqlkata" -AllowPreRelease
Copy to clipboard

SqlKata Query Builder

Twitter

WE ARE NOT ACCEPTING NEW COMPILERS, if you want to add your own compiler, we recommend to create a separate repo like SqlKata-Oracle

Follow Twitter for the latest updates about SqlKata.

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

using dotnet cli

$ dotnet add package SqlKata

using Nuget Package Manager

PM> Install-Package SqlKata

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();
  • .NETFramework 4.5
  • .NETStandard 1.0
    • NETStandard.Library (>= 1.6.1)
  • .NETFramework 4.5: 4.5.0.0
  • .NETStandard 1.0: 1.0.0.0

Owners

clipse2g ahmad

Authors

Ahmad Moussawi

Project URL

https://github.com/sqlkata/querybuilder

License

Unknown

Tags

sql query-builder dynamic-query

Info

84 total downloads
0 downloads for version 2.0.2-ci-771
Download (104.27 KB)
Found on the current feed only

Package history

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