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, usingDapperunder the covers.
Checkout the full documentation onhttps://sqlkata
$ dotnet add package SqlKata
$ dotnet add package SqlKata.Execution#(optional) If you want the execution support
varconnection=newSqlConnection("...");
varcompiler=newSqlCompiler();
vardb=newQueryFactory(connection,compiler)
QueryFactory
is provided by the SqlKata.Execution package.
varbooks=db.Query("Books").Get();
varbooks=db.Query("Books").WhereTrue("IsPublished").Get();
varintroToSql=db.Query("Books").Where("Id",145).Where("Lang","en").First();
varrecent=db.Query("Books").OrderByDesc("PublishedAt").Limit(10).Get();
varbooks=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,
"...":""
}
}]
varbooks=db.Query("Books")
.Join("Authors","Authors.Id","Books.AuthorId")
.Select("Books.*","Authors.Name as AuthorName")
.Get();
foreach(varbookinbooks)
{
Console.WriteLine($ "{book.Title}:{book.AuthorName}");
}
varisFriday=DateTime.Today.DayOfWeek==DayOfWeek.Friday;
varbooks=db.Query("Books")
.When(isFriday,q=>q.WhereIn("Category",new[]{"OpenSource","MachineLearning"}))
.Get();
varpage1=db.Query("Books").Paginate(10);
foreach(varbookinpage1.List)
{
Console.WriteLine(book.Name);
}
...
var page2=page1.Next();
intaffected=db.Query("Users").Insert(new{
Name="Jane",
CountryId=1
});
intaffected=db.Query("Users").Where("Id",1).Update(new{
Name="Jane",
CountryId=1
});
intaffected=db.Query("Users").Where("Id",1).Delete();
I announce updates on MyTwitter Account,and you can subscribe to our newsletters from the websitehttps://sqlkata
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.
Unfortunately, no, the reason is this will add overhead for the project contributors. We prefer to improve the quality of the existing compilers instead.