FluentQueryValidator

What is it?

The purpose of this project is to make easier the construction of dynamic queries that are commonly used in the implementation of reports with several optional filters.

What is not the purpose of this project?

This project does not want to be like the awesome Dapper that is an Object Mapper. We only want to build the query as easy as possible and then we use this query with Dapper.

This project also does not validade order of the method calls, so beware!

Below is an example of converting queries with IFs to SelectBuilder.

var sql = string.Concat("SELECT C.Id, C.Name, C.Identity, C.Phone, A.State ",
                        "FROM Client C ",
                        "INNER JOIN Address A ON C.Id = E.ClientId ",
                        "WHERE C.Active = 1 AND C.RegisterDate > '2017-01-01'";
                        
if(!string.IsNullOrEmpty(filter.Name))
{
  sql = string.Concat("AND C.Name LIKE '%' + @clientName + '%'");
}
if(!string.IsNullOrEmpty(filter.State))
{
  sql = string.Concat("AND A.State = @state");
}

This type of code become confuse and hard to read very quickly. The approach using SelectBuilder is:

var sql = new SelectBuilder()
  .Select("C.Id, C.Name, C.Identity, C.Phone, A.State")
  .From("Cliente C")
  .InnerJoin("Address A", "C.Id = A.ClientId")
  .Where("C.Active = 1")
  .And("C.Active = 1")
  .And("C.RegisterDate > '2017-01-01'")
  .AndIf("C.Name LIKE '%' + @clientName + '%'", !string.IsNullOrEmpty(filter.Name))
  .AndIf("E.Staste = @state", !string.IsNullOrEmpty(filter.State));

As you can see, the SelectBuilder approach is much cleaner than using IFs. The API will be detailed in the next sections.

Getting Started

You only need to install the Braspag.FluentQueryBuilder package from Nuget.org and you a ready to build your queries.

PM> install-package Braspag.FluentQueryBuilder

SelectBuilder Fluent Api

The SelectBuilder is designed to have an API as fluent as possible, so it will not be hard to you understand. Below are the list of all the methods:

• Select( string fields )
  → Adds the SELECT statement to Query

• Select( string[] fields )
  → Adds the SELECT statement to Query

• From( string table )
  → Adds the FROM of the query

• InnerJoin( string table, string on )
  → Adds an INNER JOIN with ON clause

• LeftJoin( string table, string on )
  → Adds an LEFT JOIN with ON clause

• FullOuterJoin( string table, string on )
  → Adds an FULL OUTER JOIN with ON clause

• Where( string condition )
  → Adds the where clause with the first condition

• And( string predicate )
  → Adds an AND condition to WHERE statement

• And( string predicate, Func<WhereBuilder, WhereBuilder> predicates )
  → Adds an AND group condition to WHERE statement

• AndIf( string predicate, bool condition )
  → Conditionally adds an AND condition to WHERE statement

• AndIf( string predicate, Func<WhereBuilder, WhereBuilder> predicates, bool condition )
  → Conditionally adds an AND group condition to WHERE statement

• Or( string predicate )
  → Adds an OR condition to WHERE statement

• Or( string predicate, Func<WhereBuilder, WhereBuilder> predicates )
  → Adds an OR group condition to WHERE statement

• OrIf( string predicate, bool condition )
  → Conditionally adds an OR condition to WHERE statement

• OrIf( string predicate, Func<WhereBuilder, WhereBuilder> predicates, bool condition ) → Conditionally adds an OR group condition to WHERE statement

• GroupBy( string group )
  → Adds the GROUP BY statement to query

• GroupBy( string[] group )
  → Adds the GROUP BY statement to query

• OrderBy( string order )
  → Adds the ORDER BY statement to query

• OrderBy( string[] order )
  → Adds the ORDER BY statement to query

• Paginated( int pageSize, int currentPage )
  → Adds pagination using the OFFSET/FETCH statements

• With( string hint )
  → Adds the WITH statement to use query hints

• With( string[] hint )
  → Adds multiple hints to use the WITH statement

• Option( string hint )
  → Adds the OPTION clause to use query hints

• Option( string[] hint )
  → Adds multiple hints to use with OPTION clause

Sample

Selecting filds "Field1, Field2, Field3" from table "Table1" where "Field1 = 'A very cool value'".

var sql = new SelectBuilder()
  .Select("Field1,Field2,Field3")
  .From("Table1")
  .Where("Field1 = 'A very cool value'")
  .Build();

sql.Should().Be("SELECT Campo1,Campo2,Campo3 FROM Tabela1 WHERE Campo1 = 'A very cool value'");

Want more samples? Look a the tests! Every method has a test.

Build and Test

To build this project you will need Visual Studio 2017.3. If you already have it, clone this repo and have fun!