The purpose of this library library is to build syntactically correct parameterized Postgres SQL statements for injection into dapper queries.
QueryForDapper is designed for one time configuration.
Access Configuration:
Query.ConfigureTo()
A naming scheme is required to be configured prior to use. The library includes the following default naming schemes.
Query.ConfigureTo().UseDefaultNaming();
Query.ConfigureTo().UsePassthroughNaming();
Query.ConfigureTo().UseSnakeCaseNaming();
Type | Name | PassthroughNaming | DefaultNaming | SnakeCaseNaming |
---|---|---|---|---|
Class | DemoType | DemoType | DemoTypes | demo_types |
Property | DemoProp | DemoProp | DemoProp | demo_prop |
You can define custom naming delegate by passing a Func<string, string>
to .NameColumnsWith()
and .NameTablesWith()
.
Alternative, you can implement INamingScheme
and pass the implementing type to .UseCustomNamingScheme()
Additionally, you can configure QueryForDapper to utilize ColumnAttribute
and TableAttribute
.
Query.ConfigureTo().UseColumnAttributeNames()
.UseTableAttributeNames();
The last naming option is defining a column name or table name during configuration.
Query.ConfigureTo().DefineColumnName<Table>(t => t.ColumnName, "defined_column_name")
.DefineTableName<Table>("defined_table_name");
Defintion naming takes precedence over attribute naming which takes precedence over naming methods.
By convention anytime a columnName is passed in via string it will not be passed through the configured naming methods. If required, you can call the string extension method .ToColumnName()
.
QueryForDapper allows you to define a mapping for join tables to make usage in queries easier.
Query.ConfigureTo().MapManyToMany<Left,Join,Right>(join => join.leftId, join => join.rightId);
Query.ConfigureTo().MapManyToMany<Left,Join,Right>("leftId", "rightId");
Current implementation of .MapManyToMany()
requires the column names to match between the left and right table and the join table.
- SELECT
- JOIN
- WHERE
- ORDER BY
- LIMIT
- OFFSET
To start a query chain:
Query.FromTable<Table>()
If no select method as been called the resulting statement will have a SELECT * FROM
by default.
Extension Method | Result |
---|---|
Select<Table>() |
SELECT Table.* FROM |
Select<Table>("stringId") |
SELECT Table.stringId FROM |
Select<Table>(t => t.TableId) |
SELECT Table.TableId FROM |
Select<Table>("stringId", "stringName") |
SELECT Table.stringId, Table.stringName FROM |
Select<Table>(t => t.TableId, t => t.Name) |
SELECT Table.TableId, Table.Name FROM |
SelectAs<Table>("stringId", "asName") |
SELECT Table.stringId AS asName FROM |
SelectAs<Table>(t => t.TableId, "asName") |
SELECT Table.TableId AS asName FROM |
Query.FromTable<Authors>().Select<Authors>(a => a.FirstName, a.LastName)
.JoinOn<Books>(b => b.AuthorId)
.Select<Books>(b => b.Title);
result:
SELECT Authors.FirstName, Authors.LastName, Books.Title FROM Authors
INNER JOIN Books USING (AuthorId)
Extension Method | Result |
---|---|
.JoinOn<Table>(t => t.TableId) |
INNER JOIN Table USING (TableId) |
.JoinOn<Table>("table_id") |
INNER JOIN Table USING (table_id) |
.JoinOn<Left, Right>(l => l.LeftId, r => r.RightId) |
INNER JOIN Right ON Left.LeftId = Right.RightId |
.JoinOn<Left, Right>("left_id", "right_id") |
INNER JOIN Right ON Left.left_id = Right.right_id |
The last parameter of each Join Method is JoinType joinType = default
. The default join is INNER.
JoinType | Result |
---|---|
JoinType.inner |
INNER |
JoinType.LeftOuter |
LEFT OUTER |
JoinType.RightOuter |
RIGHT OUTER |
JoinType.FullOuter |
FULL OUTER |
If you defined a join map in configuration you can use the .JoinMany<,>()
method:
Query.ConfigureTo().MapManyToMany<LeftTable, JoinTable, RightTable>(j => j.LeftId, j => j.rightId);
Query.FromTable<Left>().JoinMany<Left,Right>()
.ToStatement();
result
SELECT * FROM LeftTable
INNER JOIN JoinTable USING (LeftId)
INNER JOIN RightTable USING (RightId)
The left and right types declared in .MapManyToMany<,,>()
can be called in reverse order in.JoinMany<,>()
.
Query.FromTable<Right>().JoinMany<Right,Left>(JoinType.LeftOuter)
.ToStatement();
result
SELECT * FROM RightTable
LEFT OUTER JOIN JoinTable USING (RightId)
LEFT OUTER JOIN LeftTable USING (LeftId)
All where extensions methods have an operator parameter. Operator @operator = default
. Default is AND
.
Operator | Result |
---|---|
Operator.And |
AND |
Operator.Or |
OR |
Operator.Not |
NOT |
Extension Method | Result |
---|---|
.WhereCompared<Table>(t => t.Value, "CompareValue",Operator.None, Comparison.Equals) |
WHERE Table.Value = 'ComparedValue' |
.WhereComparedWith<Table>(t => t.Value, () => variable, Operator.None, Comparison.GreaterThan) |
WHERE Table.Value > @variable |
WhereCompared methods have a comparision parameter. Operator @operator = default
. Default is Equals.
Operator | Result |
---|---|
Comparison.Equal |
= |
Comparison.NotEqual |
<> |
Comparison.LessThan |
< |
Comparison.LessThanEqual |
<= |
Comparison.GreaterThan |
> |
Comparison.GreaterThanEqual |
>= |
public string QueryAuthor(int authorId)
{
var query = Query.FromTable<Authors>.WhereComparedWith<Authors>(a => a.AuthorId, () => authorId)
.JoinOn<Books>(b => b.AuthorId)
.WhereCompared<Books>(b => b.Title, "BookTitle", Operator.And, Comparison.NotEqual)
.ToStatement();
...
result
SELECT * FROM Authors
INNER JOIN Books USING (AuthorId)
WHERE Authors.AuthorId = @authorId AND Books.Title <> 'BookTitle'
Extension Method | Result |
---|---|
.WhereLike<Table>(t => t.Value, "searchValue") |
`WHERE Table.Value ILIKE '%' |
.WhereLikeWith<Table>(t => t.Value, () => variable) |
`WHERE Table.Value ILIKE '%' |
WhereLike methods have a Case parameter. Case @case = default
. Default Is Insensitive.
Operator | Result |
---|---|
Case.Insensitive |
ILIKE |
Case.Sensitive |
LIKE |
WhereLike methods have a Like parameter. Like like = default
. Default Is Anywhere.
Operator | Result |
---|---|
Like.Anywhere |
'%' || {'VALUE'/@VARIABLE} || '%' |
Like.Begins |
{'VALUE'/@VARIABLE} || '%' |
Like.Ends |
'%' || {'VALUE'/@VARIABLE} |
var book = "Book";
Query.FromTable<Authors>.WhereLike<Authors>(a => a.Name, "Lastname", @case: Case.Sensitive, like: Like.Ends)
.WhereLikeWith<Authors>(a => a.LastBook, () => book, Operator.Or);
Result
SELECT * FROM Authors
WHERE Authors.Name LIKE '%' || 'Lastname' OR Authors.LastBook ILIKE '%' || @book || '%'
public void QueryList(IEnuermable<Books> books)
{
Query.FromTable<Books>().WhereAnyWith<Books>(b => b.Title, () => books)
result
SELECT * FROM Books
WHERE Books.Title = ANY(@books)
var subQuery = Query.FromTable<Authors>().WhereLike<Authors>(a => a.Name, "Smith", like: Like.Ends)
.Select<Authors>(a => a.AuthorId);
Query.FromTable<Books>().WhereInSubQuery<Books>(b => b.AuthorId, subQuery);
result
SELECT * FROM Books
WHERE Books.AuthorId IN (SELECT Authors.AuthorId FROM Authors WHERE Authors.Name ILIKE '%' || 'Smith')
Extension Method | Result |
---|---|
.OrderBy<Table>(t => t.TableId) |
ORDER BY Table.TableId ASC |
.OrderBy<Table>(t => t.TableId, Order.DESC) |
ORDER BY Table.TableId DESC |
OrderBy has a Order parameter. Order order = default
. Default is Order.ASC
Operator | Result |
---|---|
Order.ASC |
ASC |
Order.DESC |
DESC |
public void GetBooks(int count)
{
Query.FromTable<Books>().TakeWith(() => count)
...
result
SELECT * FROM Books
LIMIT @count
public void GetBooks(int skip)
{
Query.FromTable<Books>().SkipWith(() => skip)
...
result
SELECT * FROM Books
LIMIT @skip
Caching is performed by the ConcurrentQueryCacheService
which implements IQueryCacheService
.
Method | Description |
---|---|
AddIfNew(string id, Func<IQuery> query) |
Will add query defined in func if the id is not found |
AddIfNew<T>(string id, Func<IQuery> query) |
Will add query defined in func if the id for type is not found |
IQuery GetQuery(string id) |
Will return a shallow clone of the cached query by id |
IQuery GetQuery<T>(string id) |
Will return a shallow clone of the cached query by id and type |
string GetStatement(string id ) |
Will return the cached SQL statement by id |
string GetStatement<T>(string id ) |
Will return the cached SQL statement by id and type |
Note: An exception of type CachedQueryNotFound
will be thrown if no query has been cached for id or combination of id and type when using Get methods.
Example:
_queryCache.AddIfNew("QueryId",() => Query.FromTable<Books>
.WhereComparedWith<Book>(b => b.Author, () => requestedAuthor)
.Select<Book>(b => b.Title);
var statement = _queryCache.GetStatement("QueryId");
For caching to be effective the query must be defined inside the Func<IQuery>
.
Caching is most effective with large queries.
public QueryTest()
{
Query.ConfigureTo().UseSnakeCaseNaming().MapManyToMany<Book, BookAuthorJoin, Author>(j => j.BookId, j => j.AuthorId)
.MapManyToMany<Book, BookGenreJoin, Genre>(j => j.BookId, j => j.GenreId);
}
[Benchmark]
public void RunQuery(string lastName, IEnumerable<string> publishers)
{
var genreSubQuery = Query.FromTable<Genre>().WhereLike<Genre>(g => g.Name, "A", like: Like.Begins).Select<Genre>(g => g.Name);
var query = Query.FromTable<Book>().Select<Book>(b => b.Title)
.JoinOn<Publisher>(p => p.PublisherId).WhereAnyWith<Publisher>(p => p.PublisherId, () => publishers)
.JoinMany<Book, Author>(JoinType.LeftOuter).WhereComparedWith<Author>(a => a.LastName, () => lastName, Operator.And).Select<Author>()
.JoinMany<Book, Genre>(JoinType.LeftOuter).WhereInSubQuery<Genre>(g => g.Name, genreSubQuery, Operator.And).Select<Genre>(g => g.Name)
.ToStatement();
}
Query Result
SELECT
books.title,
authors.*,
genres.name
FROM
books
INNER JOIN publishers USING ( publisher_id )
LEFT OUTER JOIN book_author_joins USING ( book_id )
LEFT OUTER JOIN authors USING ( author_id )
LEFT OUTER JOIN book_genre_joins USING ( book_id )
LEFT OUTER JOIN genres USING ( genre_id )
WHERE
publishers.publisher_id = ANY ( @publishers )
AND authors.last_name = @lastName
AND genres.name IN ( SELECT genres.name FROM genres WHERE genres.name ILIKE 'A' || '%' )
Benchmark Results
Runtime = .NET Core 3.1.0 (CoreCLR 4.700.19.56402, CoreFX 4.700.19.56404), X64 RyuJIT; GC = Concurrent Workstation
Mean = 177.7263 us, StdErr = 0.6719 us (0.38%); N = 15, StdDev = 2.6024 us
Min = 174.4226 us, Q1 = 175.6583 us, Median = 177.0236 us, Q3 = 179.6323 us, Max = 183.1319 us
IQR = 3.9740 us, LowerFence = 169.6974 us, UpperFence = 185.5933 us
ConfidenceInterval = [174.9442 us; 180.5084 us] (CI 99.9%), Margin = 2.7821 us (1.57% of Mean)
Skewness = 0.66, Kurtosis = 2.17, MValue = 2
-------------------- Histogram --------------------
[173.499 us ; 178.605 us) | @@@@@@@@@@
[178.605 us ; 184.055 us) | @@@@@
---------------------------------------------------
| Method | Mean | Error | StdDev |
|--------- |---------:|--------:|--------:|
| RunQuery | 177.7 us | 2.78 us | 2.60 us |
* Legends *
lastName : Value of the 'lastName' parameter
publishers : Value of the 'publishers' parameter
Mean : Arithmetic mean of all measurements
Error : Half of 99.9% confidence interval
StdDev : Standard deviation of all measurements
1 us : 1 Microsecond (0.000001 sec)