Good day and welcome to the thing...
I wrote this package to make writing sql queries that need to be maintainable a little bit more pleasant in certain use cases. The most common use case for this might be when using dapper or when writing raw NO SQL queries for CosmosDB or any other database where this is relevant.
What is the point?
Mainly to have a C# based query syntax with strongly typed property names. Therefore, if a property name changes in your code base, all your queries are still valid. Another perk is that should you need to migrate your queries to another platform, you can just change a few options and "Bobs your uncle!" -- it should work.
- CosmosDb
- MySql / MariaDb
- SqlServer
- PostgreSql
- When no prefix options are provided the table name will be appended to all SELECT, WHERE and ORDER BY clauses.
- If no casing options are provided the properties will remain with the default casing of the nameof(T) string output.
var sql =
new SqlBuilder()
.Select(new []
{
nameof(Person.FirstName),
nameof(Person.LastName),
nameof(Person.Age)
})
.From("c")
.Where<Person>(p => new[]
{
$"{nameof(p.Age)} = 18",
$"{nameof(p.Age)} = 20"
}, w => w.WithPropertyPrefix("w"))
.And()
.Where<Person>(p => new[]
{
$"{nameof(p.FirstName)} like %John%"
})
.Or()
.Where(nameof(Person.FirstName), o => o.EqualsString("John"))
.And()
.Where<Person>(p => nameof(p.LastName), w => w.Like("Williams"))
.Build();
SELECT
c.FirstName,
c.LastName,
c.Age
FROM c WHERE w.Age = 18
OR w.Age = 20
AND FirstName like %John%
OR FirstName = 'John'
AND LastName LIKE 'Williams'
using static QueryBuilders;
Query(q => q.SelectAll()
.From("people")
.Where<Person>(nameof(w.FirstName), w => w.Like("John")));
If you would like to specify options that should be applied to all your queries, simply use the ConfigurationBuilder.
If you have any suggestions, bug fixes or features you would like to add to improve this code, fell free to tag me in a PR and I will have a look as soon as possible.
Cheerio! C.K.