sqlkata - SqlKata 1.0.3
A powerful Dynamic Sql Query Builder supporting Sql Server, MySql, PostgreSql and Firebird
PM> Install-Package SqlKata -Version 1.0.3 -Source https://www.myget.org/F/sqlkata/api/v3/index.json
> nuget.exe install SqlKata -Version 1.0.3 -Source https://www.myget.org/F/sqlkata/api/v3/index.json
> dotnet add package SqlKata --version 1.0.3 --source https://www.myget.org/F/sqlkata/api/v3/index.json
> choco install SqlKata --version 1.0.3 --source https://www.myget.org/F/sqlkata/api/v2
Import-Module PowerShellGet
Register-PSRepository -Name "sqlkata" -SourceLocation "https://www.myget.org/F/sqlkata/api/v2"
Install-Module -Name "SqlKata" -RequiredVersion "1.0.3" -Repository "sqlkata"
Copy to clipboard
SqlKata Query Builder
Follow and Upvote SqlKata on Product Hunt to encourage the development of this project
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?
- .NETFramework 4.5: 4.5.0.0
- .NETStandard 1.0: 1.0.0.0
Ownersclipse2g ahmad |
AuthorsAhmad Moussawi |
Project URLhttps://github.com/sqlkata/querybuilder |
LicenseUnknown |
Tagssql query-builder dynamic-query |
Info1737 total downloads |
12 downloads for version 1.0.3 |
Download (79.32 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 | 7 | ||||
2.2.0-ci-811 | 57.12 KB | Sun, 14 Mar 2021 14:49:27 GMT | 7 | ||||
2.2.0-ci-810 | 57.11 KB | Sun, 14 Mar 2021 14:45:12 GMT | 8 | ||||
2.2.0-ci-809 | 57.11 KB | Sun, 14 Mar 2021 14:43:52 GMT | 6 | ||||
2.2.0-ci-808 | 57.12 KB | Sun, 14 Mar 2021 14:42:11 GMT | 7 | ||||
2.2.0-ci-807 | 57.14 KB | Sun, 14 Mar 2021 14:39:38 GMT | 5 | ||||
2.2.0-ci-806 | 57.11 KB | Sun, 14 Mar 2021 14:34:44 GMT | 6 | ||||
2.2.0-ci-805 | 106.95 KB | Sun, 14 Mar 2021 14:32:27 GMT | 7 | ||||
2.2.0-ci-804 | 106.98 KB | Sun, 14 Mar 2021 14:28:37 GMT | 6 | ||||
2.2.0-ci-803 | 106.99 KB | Fri, 19 Feb 2021 08:24:50 GMT | 7 | ||||
2.2.0-ci-802 | 106.99 KB | Wed, 17 Feb 2021 11:19:35 GMT | 8 | ||||
2.2.0-ci-801 | 106.96 KB | Wed, 17 Feb 2021 10:52:59 GMT | 7 | ||||
2.2.0-ci-800 | 106.96 KB | Mon, 21 Dec 2020 16:19:22 GMT | 10 | ||||
2.2.0-ci-799 | 106.68 KB | Wed, 11 Nov 2020 08:18:01 GMT | 10 | ||||
2.2.0-ci-798 | 106.12 KB | Wed, 07 Oct 2020 14:40:47 GMT | 10 | ||||
2.2.0-ci-794 | 106.15 KB | Sun, 20 Sep 2020 17:52:59 GMT | 13 | ||||
2.2.0-ci-791 | 104.38 KB | Wed, 15 Jul 2020 14:47:12 GMT | 11 | ||||
2.2.0-ci-790 | 104.51 KB | Mon, 08 Jun 2020 14:37:02 GMT | 11 | ||||
2.2.0-ci-788 | 104.49 KB | Mon, 01 Jun 2020 08:19:57 GMT | 10 | ||||
2.2.0-ci-787 | 104.51 KB | Mon, 01 Jun 2020 07:40:58 GMT | 11 | ||||
2.1.0-ci-786 | 104.53 KB | Fri, 29 May 2020 13:15:40 GMT | 12 | ||||
2.1.0-ci-785 | 104.54 KB | Fri, 29 May 2020 13:02:55 GMT | 11 | ||||
2.0.2-ci-783 | 104.51 KB | Thu, 28 May 2020 19:44:36 GMT | 9 | ||||
2.0.2-ci-780 | 104.49 KB | Thu, 28 May 2020 19:23:23 GMT | 10 | ||||
2.0.2-ci-778 | 104.47 KB | Thu, 07 May 2020 16:10:35 GMT | 10 | ||||
2.0.2-ci-776 | 104.34 KB | Mon, 20 Apr 2020 06:13:08 GMT | 12 | ||||
2.0.2-ci-775 | 104.39 KB | Fri, 27 Mar 2020 19:00:58 GMT | 12 | ||||
2.0.2-ci-773 | 104.33 KB | Sun, 26 Jan 2020 11:27:24 GMT | 14 | ||||
2.0.2-ci-771 | 104.27 KB | Sun, 05 Jan 2020 13:57:42 GMT | 11 | ||||
1.1.8-CI-768 | 104.27 KB | Sun, 05 Jan 2020 12:42:47 GMT | 11 | ||||
1.1.8-CI-767 | 104.36 KB | Sun, 05 Jan 2020 12:40:46 GMT | 11 | ||||
1.1.8-CI-766 | 104.29 KB | Sun, 05 Jan 2020 12:32:37 GMT | 12 | ||||
1.1.8-CI-765 | 104.3 KB | Sun, 05 Jan 2020 12:22:33 GMT | 11 | ||||
1.1.8-CI-739 | 104.28 KB | Wed, 01 Jan 2020 11:39:49 GMT | 11 | ||||
1.1.8-CI-738 | 104.29 KB | Wed, 01 Jan 2020 11:07:21 GMT | 11 | ||||
1.1.8-CI-737 | 104.29 KB | Wed, 01 Jan 2020 10:21:14 GMT | 10 | ||||
1.1.8-CI-735 | 104.32 KB | Wed, 01 Jan 2020 10:16:38 GMT | 10 | ||||
1.1.8-CI-733 | 104.28 KB | Wed, 01 Jan 2020 10:09:32 GMT | 10 | ||||
1.1.8-CI-732 | 104.27 KB | Wed, 01 Jan 2020 10:06:33 GMT | 10 | ||||
1.1.8-CI-731 | 104.32 KB | Wed, 01 Jan 2020 10:04:21 GMT | 13 | ||||
1.1.8-CI-730 | 104.29 KB | Wed, 01 Jan 2020 10:02:10 GMT | 11 | ||||
1.1.8-CI-724 | 104.34 KB | Fri, 27 Dec 2019 21:29:34 GMT | 11 | ||||
1.1.8-CI-722 | 104.28 KB | Fri, 13 Dec 2019 06:28:57 GMT | 10 | ||||
1.1.8-CI-721 | 102.96 KB | Tue, 03 Dec 2019 12:20:48 GMT | 13 | ||||
1.1.8-CI-720 | 102.97 KB | Fri, 25 Oct 2019 22:30:35 GMT | 17 | ||||
1.1.8-ci-712 | 102.66 KB | Wed, 07 Aug 2019 19:18:22 GMT | 13 | ||||
1.1.8-ci-711 | 102.68 KB | Wed, 07 Aug 2019 19:15:22 GMT | 11 | ||||
1.1.8-ci-710 | 102.7 KB | Wed, 07 Aug 2019 19:09:10 GMT | 10 | ||||
1.1.8-ci-709 | 102.68 KB | Wed, 07 Aug 2019 19:02:46 GMT | 9 | ||||
1.1.8-ci-708 | 102.68 KB | Wed, 07 Aug 2019 19:01:55 GMT | 10 | ||||
1.1.8-ci-707 | 102.69 KB | Mon, 29 Jul 2019 12:54:38 GMT | 11 | ||||
1.1.8-ci-705 | 102.69 KB | Fri, 28 Jun 2019 16:57:21 GMT | 12 | ||||
1.1.8-ci-703 | 102.69 KB | Fri, 28 Jun 2019 16:21:14 GMT | 11 | ||||
1.1.8-ci-693 | 101.54 KB | Mon, 10 Jun 2019 12:41:01 GMT | 10 | ||||
1.1.8-ci-692 | 100.52 KB | Fri, 07 Jun 2019 12:03:06 GMT | 12 | ||||
1.1.8-ci-691 | 100.55 KB | Wed, 29 May 2019 08:35:49 GMT | 11 | ||||
1.1.8-ci-688 | 100.77 KB | Sat, 18 May 2019 16:57:18 GMT | 11 | ||||
1.1.8-ci-687 | 99.53 KB | Thu, 02 May 2019 12:40:06 GMT | 10 | ||||
1.1.8-ci-686 | 99.03 KB | Thu, 02 May 2019 12:38:08 GMT | 11 | ||||
1.1.8-ci-685 | 98.01 KB | Wed, 24 Apr 2019 07:01:30 GMT | 11 | ||||
1.1.8-ci-684 | 95.91 KB | Tue, 16 Apr 2019 08:47:32 GMT | 12 | ||||
1.1.8-ci-683 | 95.9 KB | Mon, 15 Apr 2019 18:11:13 GMT | 11 | ||||
1.1.8-ci-681 | 95.71 KB | Mon, 15 Apr 2019 16:19:13 GMT | 12 | ||||
1.1.8-ci-679 | 95.72 KB | Mon, 15 Apr 2019 15:40:46 GMT | 10 | ||||
1.1.8-ci-677 | 94.88 KB | Wed, 03 Apr 2019 15:56:47 GMT | 13 | ||||
1.1.8-ci-676 | 94.83 KB | Wed, 03 Apr 2019 15:55:59 GMT | 11 | ||||
1.1.8-ci-675 | 94.6 KB | Wed, 03 Apr 2019 11:40:25 GMT | 11 | ||||
1.1.8-ci-674 | 94.6 KB | Tue, 26 Mar 2019 22:10:51 GMT | 11 | ||||
1.1.8-ci-673 | 94.63 KB | Tue, 26 Mar 2019 21:57:25 GMT | 10 | ||||
1.1.8-0-718 | 102.97 KB | Thu, 24 Oct 2019 10:29:18 GMT | 11 | ||||
1.1.8-0-717 | 102.98 KB | Tue, 22 Oct 2019 19:40:53 GMT | 10 | ||||
1.1.8-0-716 | 102.99 KB | Tue, 22 Oct 2019 19:17:40 GMT | 10 | ||||
1.1.8-0-714 | 102.87 KB | Wed, 11 Sep 2019 10:44:26 GMT | 12 | ||||
1.1.8-0-713 | 102.69 KB | Wed, 07 Aug 2019 19:27:36 GMT | 10 | ||||
1.1.7 | 94.62 KB | Tue, 26 Mar 2019 21:52:22 GMT | 13 | ||||
1.1.7-ci-672 | 94.65 KB | Tue, 26 Mar 2019 21:54:23 GMT | 11 | ||||
1.1.6 | 93.93 KB | Thu, 21 Feb 2019 11:32:57 GMT | 12 | ||||
1.1.5 | 94.98 KB | Tue, 08 Jan 2019 11:29:49 GMT | 11 | ||||
1.1.4 | 94.99 KB | Fri, 04 Jan 2019 15:09:35 GMT | 11 | ||||
1.1.3 | 94.98 KB | Sat, 29 Dec 2018 20:04:12 GMT | 13 | ||||
1.1.2 | 83.14 KB | Fri, 28 Sep 2018 15:22:53 GMT | 11 | ||||
1.1.0 | 83.11 KB | Thu, 27 Sep 2018 09:30:33 GMT | 11 | ||||
1.0.5 | 80.53 KB | Mon, 24 Sep 2018 17:18:30 GMT | 17 | ||||
1.0.3 | 79.32 KB | Fri, 03 Aug 2018 10:38:15 GMT | 12 | ||||
1.0.0 | 36.54 KB | Sun, 05 Jan 2020 11:19:05 GMT | 11 | ||||
1.0.0-beta-553 | 76.76 KB | Sun, 15 Jul 2018 19:22:46 GMT | 11 | ||||
1.0.0-beta-536 | 76.76 KB | Sun, 15 Jul 2018 15:50:18 GMT | 11 | ||||
1.0.0-beta-535 | 72.25 KB | Sat, 14 Jul 2018 06:51:00 GMT | 11 | ||||
1.0.0-beta-534 | 71.96 KB | Sat, 14 Jul 2018 06:42:03 GMT | 11 | ||||
1.0.0-beta-533 | 71.4 KB | Sat, 14 Jul 2018 06:16:43 GMT | 12 | ||||
1.0.0-beta-532 | 72.75 KB | Fri, 06 Jul 2018 14:42:04 GMT | 12 | ||||
1.0.0-beta-531 | 72.64 KB | Wed, 04 Jul 2018 13:11:10 GMT | 13 | ||||
1.0.0-beta-528 | 70.92 KB | Wed, 27 Jun 2018 14:16:45 GMT | 12 | ||||
1.0.0-beta-527 | 70.88 KB | Tue, 26 Jun 2018 22:21:08 GMT | 11 | ||||
1.0.0-beta-526 | 70.92 KB | Tue, 26 Jun 2018 22:16:26 GMT | 11 | ||||
1.0.0-beta-525 | 80.76 KB | Tue, 26 Jun 2018 22:12:47 GMT | 13 | ||||
1.0.0-beta-524 | 41.4 KB | Tue, 26 Jun 2018 17:19:46 GMT | 10 | ||||
1.0.0-beta-510 | 70.03 KB | Fri, 25 May 2018 08:07:54 GMT | 18 | ||||
1.0.0-beta-509 | 69.74 KB | Fri, 18 May 2018 08:42:13 GMT | 12 | ||||
1.0.0-beta-507 | 69.87 KB | Fri, 18 May 2018 08:12:11 GMT | 10 | ||||
1.0.0-beta-506 | 69.86 KB | Fri, 18 May 2018 07:48:29 GMT | 11 | ||||
1.0.0-beta-485 | 69.64 KB | Wed, 16 May 2018 09:07:57 GMT | 11 | ||||
1.0.0-beta-484 | 69.64 KB | Mon, 14 May 2018 06:09:38 GMT | 11 | ||||
1.0.0-beta-483 | 69.64 KB | Tue, 08 May 2018 08:31:54 GMT | 11 | ||||
1.0.0-beta-482 | 70.46 KB | Tue, 08 May 2018 08:18:08 GMT | 9 | ||||
1.0.0-beta-481 | 70.53 KB | Tue, 08 May 2018 08:13:48 GMT | 11 | ||||
1.0.0-beta-480 | 77.19 KB | Tue, 08 May 2018 08:00:56 GMT | 10 | ||||
1.0.0-beta-479 | 77.19 KB | Tue, 08 May 2018 07:58:55 GMT | 11 | ||||
1.0.0-beta-476 | 77.92 KB | Thu, 03 May 2018 19:26:23 GMT | 13 | ||||
1.0.0-beta-475 | 77.81 KB | Thu, 03 May 2018 14:57:23 GMT | 12 | ||||
1.0.0-beta-474 | 77.72 KB | Tue, 01 May 2018 15:02:50 GMT | 12 | ||||
1.0.0-beta-473 | 77.42 KB | Mon, 30 Apr 2018 08:44:26 GMT | 11 | ||||
1.0.0-beta-472 | 77.55 KB | Fri, 27 Apr 2018 21:12:57 GMT | 10 | ||||
1.0.0-beta-471 | 77.74 KB | Fri, 27 Apr 2018 09:26:49 GMT | 12 | ||||
1.0.0-beta-470 | 77.71 KB | Wed, 25 Apr 2018 07:35:09 GMT | 11 | ||||
1.0.0-beta-469 | 75.51 KB | Wed, 25 Apr 2018 06:53:11 GMT | 11 | ||||
1.0.0-beta-468 | 81.11 KB | Tue, 24 Apr 2018 08:08:46 GMT | 11 | ||||
1.0.0-beta-467 | 80.96 KB | Mon, 23 Apr 2018 14:38:04 GMT | 11 | ||||
1.0.0-beta-466 | 80.93 KB | Fri, 20 Apr 2018 16:11:44 GMT | 10 | ||||
1.0.0-beta-465 | 80.94 KB | Thu, 19 Apr 2018 15:35:48 GMT | 18 | ||||
1.0.0-beta-464 | 80.9 KB | Thu, 19 Apr 2018 15:28:09 GMT | 10 | ||||
1.0.0-beta-463 | 80.96 KB | Tue, 17 Apr 2018 08:41:56 GMT | 11 | ||||
1.0.0-beta-461 | 80.94 KB | Tue, 17 Apr 2018 07:27:23 GMT | 11 | ||||
1.0.0-beta-460 | 80.69 KB | Sun, 15 Apr 2018 20:17:45 GMT | 12 | ||||
1.0.0-beta-459 | 80.72 KB | Sun, 15 Apr 2018 19:51:25 GMT | 11 | ||||
1.0.0-beta-458 | 80.74 KB | Sun, 08 Apr 2018 02:43:44 GMT | 9 | ||||
1.0.0-beta-457 | 80.74 KB | Sun, 08 Apr 2018 02:40:39 GMT | 11 | ||||
1.0.0-beta-455 | 80.67 KB | Thu, 05 Apr 2018 13:11:50 GMT | 14 | ||||
1.0.0-beta-454 | 79.88 KB | Thu, 29 Mar 2018 00:10:20 GMT | 12 | ||||
1.0.0-beta-453 | 79.87 KB | Thu, 29 Mar 2018 00:03:47 GMT | 10 | ||||
1.0.0-beta-450 | 79.89 KB | Wed, 28 Mar 2018 23:18:55 GMT | 11 | ||||
1.0.0-beta-449 | 79.89 KB | Wed, 28 Mar 2018 23:17:12 GMT | 10 | ||||
1.0.0-beta-445 | 79.87 KB | Wed, 28 Mar 2018 23:02:05 GMT | 11 | ||||
1.0.0-beta-442 | 79.89 KB | Thu, 22 Mar 2018 11:20:29 GMT | 10 | ||||
1.0.0-beta-441 | 79.79 KB | Wed, 21 Mar 2018 23:21:12 GMT | 11 | ||||
1.0.0-beta-440 | 79.79 KB | Wed, 21 Mar 2018 23:16:35 GMT | 11 | ||||
1.0.0-beta-430 | 79.78 KB | Sun, 04 Mar 2018 08:12:04 GMT | 11 | ||||
1.0.0-beta-429 | 80.28 KB | Fri, 02 Mar 2018 21:35:09 GMT | 11 | ||||
1.0.0-beta-427 | 80.23 KB | Wed, 14 Feb 2018 11:34:26 GMT | 12 | ||||
1.0.0-beta-426 | 80.21 KB | Wed, 14 Feb 2018 10:50:50 GMT | 10 | ||||
1.0.0-beta-425 | 80.24 KB | Wed, 14 Feb 2018 10:48:56 GMT | 11 | ||||
1.0.0-beta-424 | 80.25 KB | Wed, 14 Feb 2018 10:37:19 GMT | 11 | ||||
1.0.0-beta-423 | 80.23 KB | Mon, 12 Feb 2018 17:29:37 GMT | 19 | ||||
1.0.0-beta-422 | 80.22 KB | Mon, 12 Feb 2018 16:33:27 GMT | 10 | ||||
1.0.0-beta-421 | 80.23 KB | Mon, 12 Feb 2018 13:37:12 GMT | 10 | ||||
1.0.0-beta-420 | 79.88 KB | Mon, 12 Feb 2018 13:08:37 GMT | 12 | ||||
1.0.0-beta-419 | 77.2 KB | Sun, 11 Feb 2018 15:13:02 GMT | 12 | ||||
1.0.0-beta-411 | 77.07 KB | Sun, 21 Jan 2018 12:06:21 GMT | 11 | ||||
1.0.0-beta-410 | 77.07 KB | Sun, 21 Jan 2018 12:05:21 GMT | 11 | ||||
1.0.0-beta-409 | 77.08 KB | Sun, 21 Jan 2018 11:14:53 GMT | 11 | ||||
1.0.0-beta-408 | 77.08 KB | Sun, 21 Jan 2018 11:04:51 GMT | 10 | ||||
1.0.0-beta-407 | 77.08 KB | Sun, 21 Jan 2018 10:47:32 GMT | 10 | ||||
1.0.0-beta-405 | 77.09 KB | Sat, 20 Jan 2018 21:04:06 GMT | 11 | ||||
1.0.0-beta-379 | 77.1 KB | Sat, 06 Jan 2018 17:58:35 GMT | 10 | ||||
1.0.0-beta-373 | 77.09 KB | Fri, 05 Jan 2018 20:15:42 GMT | 11 | ||||
1.0.0-beta-369 | 76.97 KB | Tue, 12 Dec 2017 16:39:35 GMT | 10 | ||||
1.0.0-beta-368 | 77.04 KB | Mon, 27 Nov 2017 15:57:53 GMT | 11 | ||||
1.0.0-beta-357 | 77.04 KB | Sat, 18 Nov 2017 18:04:26 GMT | 11 | ||||
1.0.0-beta-356 | 77.04 KB | Sat, 18 Nov 2017 17:55:54 GMT | 11 |