Skip to content

Latest commit

 

History

History
649 lines (478 loc) · 16.1 KB

fluent-sql.md

File metadata and controls

649 lines (478 loc) · 16.1 KB
Raw

Fluent SQL

Serenity contains a set of query builders for SELECT, INSERT, UPDATE, and DELETE statements.

These builders can be used with simple strings or the Serenity entity (row) system.

Their output can be executed directly, through a micro-ORM like Dapper (which is integrated with Serenity), or Serenity extensions.

SQL Query

SqlQuery is an object to compose dynamic SQL SELECT queries through a fluent interface.

SqlQuery offers some advantages over hand-crafted SQL:

  • Using the intelli-sense feature of Visual Studio while composing SQL

  • Fluent interface with minimal overhead

  • Reduced syntax errors as the queries are checked compile time, not execution time.

  • Clauses like Select, Where and Order By can be used in any order. They are placed at the correct positions when converting the query to a string. Similarly, such clauses can be used more than once and they are merged during string conversion. So you can conditionally build SQL depending on input parameters.

  • No need to mess up with parameters and parameter names. All values used are converted to auto-named parameters (as long as you don't use string concatenation or pass non-escaped user input). You can also use manually named parameters if required.

  • It can generate a special query to perform paging on server types that don't support it natively (e.g. SQL Server 2000)

  • With the dialect system, the query can be targeted at specific server types and versions.

  • If it is used along with Serenity entities (it can also be used with micro ORMs like Dapper), helps to load query results from a data reader with zero reflection. It also does left/right joins automatically.

IMPORTANT WARNING!

Never pass a user-provided string to one of the SQLQuery methods. It is VERY DANGEROUS as it would open your code to SQL-Injection attacks! This includes using the user-provided string in a string concatenation operation and passing it.

The user-provided string here includes anything entered on a client-side form, in addition to any parameters that are sent to one of your API/actions/services via JSON, XML, query-string, form, request-body, etc.

https://en.wikipedia.org/wiki/SQL_injection

A Simple Select Query

var query = new SqlQuery();
query.Select("Firstname");
query.Select("Surname");
query.From("People");
query.OrderBy("Age");

Console.WriteLine(query.ToString());

This will result in output:

SELECT 
Firstname,
Surname 
FROM People 
ORDER BY Age

In the first line of our program, we called SqlQuery with its sole parameterless constructor. If ToString() was called at this point, the output would be:

SELECT FROM

SqlQuery doesn't perform any syntax validation. It just converts the query you build yourself, by calling its methods. Even if you don't select any fields or call from methods, it will generate this basic SELECT FROM statement.

SqlQuery can't generate empty queries.

Next, we called the Select method with a string parameter "FirstName". Our query is now like this:

SELECT Firstname FROM

When the Select("Surname") statement is executed, SqlQuery put a comma between the previously selected field (Firstname) and this one:

SELECT Firstname, Surname FROM

After executing From and OrderBy methods, our final output is:

SELECT Firstname, Surname FROM People ORDER BY Age

Method Call Order and Its Effects

In the previous sample, the output wouldn't change even if we reordered From, OrderBy, and Select lines. It would change only if we changed the order of Select statements...

var query = new SqlQuery();
query.From("People");
query.OrderBy("Age");
query.Select("Surname");
query.Select("Firstname");

Console.WriteLine(query.ToString());

...but, only the column ordering inside the SELECT statement would change:

SELECT 
Surname,
Firstname 
FROM People 
ORDER BY Age

You might use methods like Select, From, OrderBy and GroupBy in any order, and can also mix them (e.g. call Select, then OrderBy, Select again...)

Putting FROM at start is recommended, especially when used with Serenity entities, as it helps with auto joins and determining database dialect etc.

Method Chaining

It is a bit verbose and not so readable to start every line with "query.". Almost all SqlQuery methods are chainable and return the query itself as result.

We may rewrite the query like this:

var query = new SqlQuery()
    .From("People")
    .Select("Firstname")
    .Select("Surname")
    .OrderBy("Age");
Console.WriteLine(query.ToString());
}

This feature is similar to jQuery and LINQ enumerable method chaining.

We could even get rid of the query variable:

Console.WriteLine(
    new SqlQuery()
        .From("People")
        .Select("Firstname")
        .Select("Surname")
        .OrderBy("Age")
        .ToString());

It is strongly recommended to put every method on its own line, and indent properly for readability and consistency reasons.

Select Method

public SqlQuery Select(string expression)

In the samples we had so far, we used the overload of the Select method shown above (it has about 11 overloads).

The expression parameter can be a simple field name or an expression like "FirstName + ' ' + LastName"

Whenever this method is called, the expression you set is added to the SELECT statement of the resulting query with a comma in-between.

There is also a SelectMany method to select multiple fields in one call:

public SqlQuery SelectMany(params string[] expressions)

For example:

var query = new SqlQuery()
    .From("People")
    .SelectMany("Firstname", "Surname", "Age", "Gender")
    .ToString();

Console.WriteLine(query.ToString());
SELECT 
Firstname,
Surname,
Age,
Gender 
FROM People

I'd personally prefer calling Select method multiple times.

You might be wondering, why multi-select is not just another Select overload. It's because the Select method has a more commonly used overload to select a column with an alias:

public SqlQuery Select(string expression, string alias)
var query = new SqlQuery()
    .Select("(Firstname + ' ' + Surname)", "Fullname")
    .From("People")
    .ToString();

Console.WriteLine(query.ToString());
SELECT 
(Firstname + ' ' + Surname) AS [Fullname] 
FROM People

From Method

public SqlQuery From(string table)

SqlQuery.From method should be called at least once (and usually once).

..and it is recommended to be called first.

When you call it a second time, the table name will be added to FROM statement with a comma between. Thus, it will be a CROSS JOIN:

var query = new SqlQuery()
    .From("People")
    .From("City")
    .From("Country")
    .Select("Firstname")
    .Select("Surname")
    .OrderBy("Age");

Console.WriteLine(query.ToString());
SELECT 
Firstname,
Surname 
FROM People, City, Country 
ORDER BY Age

Using Alias Object with SqlQuery

It is common to use table aliases when the number of referenced tables increases and our queries become longer:

var query = new SqlQuery()
    .From("Person p")
    .From("City c")
    .From("Country o")
    .Select("p.Firstname")
    .Select("p.Surname")
    .Select("c.Name", "CityName")
    .Select("o.Name", "CountryName")
    .OrderBy("p.Age")
    .ToString();

Console.WriteLine(query.ToString());
}
SELECT 
p.Firstname,
p.Surname,
c.Name AS [CityName],
o.Name AS [CountryName] 
FROM Person p, City c, Country o 
ORDER BY p.Age

Although it works like this, it is better to define p, c, and o as the Alias objects:

var p = new Alias("Person", "p");

The Alias object is like a short name assigned to a table. It has an indexer and operator overload to generate SQL member access expressions like p.Surname:

var p = new Alias("Person", "p");
Console.WriteLine(p + "Surname"); // + operator overload
Console.WriteLine(p["Firstname"]); // through indexer
p.Surname
p.Firstname

Unfortunately, the C# member access operator (.) can't be overridden, so we had to use (+). A workaround could be possible with dynamic, but it would perform poorly.

Let's modify our query by making use of Alias objects:

var p = new Alias("Person", "p");
var c = new Alias("City", "c");
var o = new Alias("Country", "o");

var query = new SqlQuery()
    .From(p)
    .From(c)
    .From(o)
    .Select(p + "Firstname")
    .Select(p + "Surname")
    .Select(c + "Name", "CityName")
    .Select(o + "Name", "CountryName")
    .OrderBy(p + "Age")
    .ToString();

Console.WriteLine(query.ToString());
SELECT 
p.Firstname,
p.Surname,
c.Name AS [CityName],
o.Name AS [CountryName] 
FROM Person p, City c, Country o 
ORDER BY p.Age

As seen above, the result is the same, but the code we wrote is a bit longer. So what is the advantage of using an alias?

If we had a list of constants with field names…

const string Firstname = "Firstname";
const string Surname = "Surname";
const string Name = "Name";
const string Age = "Age";

var p = new Alias("Person", "p");
var c = new Alias("City", "c");
var o = new Alias("Country", "o");
var query = new SqlQuery()
    .From(p)
    .From(c)
    .From(o)
    .Select(p + Firstname)
    .Select(p + Surname)
    .Select(c + Name, "CityName")
    .Select(o + Name, "CountryName")
    .OrderBy(p + Age)
    .ToString();

Console.WriteLine(query.ToString());

We would take advantage of the intelli-sense feature and have some more compile time checks.

As obvious, it is not logical and easy to define field names for every query. This should be in a central location or our entity declarations.

Let's create a poor mans simple ORM using Alias:

public class PeopleAlias : Alias
{
    public PeopleAlias(string alias) 
        : base("People", alias) { }

    public string ID { get { return this["ID"]; } }
    public string Firstname { get { return this["Firstname"]; } }
    public string Surname { get { return this["Surname"]; } }
    public string Age { get { return this["Age"]; } }
}

public class CityAlias : Alias
{
  public CityAlias(string alias)
      : base("City", alias) { }

  public string ID { get { return this["ID"]; } }
  public string CountryID { get { return this["CountryID"]; } }
  public new string Name { get { return this["Name"]; } }
}

public class CountryAlias : Alias
{
  public CountryAlias(string alias)
      : base("Country", alias) { }

  public string ID { get { return this["ID"]; } }
  public new string Name { get { return this["Name"]; } }
}

void Main()
{
    var p = new PeopleAlias("p");
    var c = new CityAlias("c");
    var o = new CountryAlias("o");
    var query = new SqlQuery()
        .From(p)
        .From(c)
        .From(o)
        .Select(p.Firstname)
        .Select(p.Surname)
        .Select(c.Name, "CityName")
        .Select(o.Name, "CountryName")
        .OrderBy(p.Age)
        .ToString();
    
    Console.WriteLine(query.ToString());
}

Now we have a set of table alias classes with field names and they can be reused in all queries.

This is just a sample to explain aliases. I don't recommend writing such classes. Entities offers much more.

In the sample above, we used SqlQuery.From overload that takes an Alias parameter:

public SqlQuery From(Alias alias)

When this method is called, the table name and its aliased name are added to FROM statement of the query.

OrderBy Method

public SqlQuery OrderBy(string expression, bool desc = false)

OrderBy can also be called with a field name or expression like Select.

If you assign the desc optional argument as true, the DESC keyword is appended to the field name or expression.

By default, OrderBy appends specified expressions to the end of the ORDER BY statement. Sometimes, you might want to insert an expression/field to start.

For example, you might have a query with some predefined order, but if the user orders by a column in a grid, the name of the column should be inserted at index 0.

public SqlQuery OrderByFirst(string expression, bool desc = false)
var query = new SqlQuery()
    .Select("Firstname")
    .Select("Surname")
    .From("Person")
    .OrderBy("PersonID");
    
query.OrderByFirst("Age");
    
Console.WriteLine(query.ToString());
}
SELECT 
Firstname,
Surname 
FROM Person 
ORDER BY Age, PersonID

Distinct Method

public SqlQuery Distinct(bool distinct)

Use this method to prepend a DISTINCT keyword before the SELECT statement.

var query = new SqlQuery()
    .Select("Firstname")
    .Select("Surname")
    .From("Person")
    .OrderBy("PersonID")
    .Distinct(true);
    
Console.WriteLine(query.ToString());
SELECT DISTINCT 
Firstname,
Surname 
FROM Person 
ORDER BY PersonID

GroupBy Method

public SqlQuery GroupBy(string expression)

GroupBy works similarly to the OrderBy but it doesn't have a GroupByFirst variant.

SELECT 
Firstname,
Lastname,
Count(*) 
FROM Person 
GROUP BY Firstname, LastName
SELECT 
Firstname,
Lastname,
Count(*) 
FROM Person 
GROUP BY Firstname, LastName

Having Method

public SqlQuery Having(string expression)

Having can be used with GroupBy (though it doesn't check for GroupBy) and appends the expression to the end of the HAVING statement.

var query = new SqlQuery()
    .From("Person")
    .Select("Firstname")
    .Select("Lastname")
    .Select("Count(*)")
    .GroupBy("Firstname")
    .GroupBy("LastName")
    .Having("Count(*) > 5");
    
Console.WriteLine(query.ToString());
SELECT 
Firstname,
Lastname,
Count(*) 
FROM Person 
GROUP BY Firstname, LastName 
HAVING Count(*) > 5

Paging Operations (SKIP / TAKE / TOP / LIMIT)

public SqlQuery Skip(int skipRows)

public SqlQuery Take(int rowCount)

SqlQuery has paging methods similar to LINQ Take and Skip.

These are mapped to SQL keywords depending on the database type.

As SqlServer versions before 2012 doesn't have a SKIP equivalent, to use SKIP your query should have at least one ORDER BY statement as ROW_NUMBER() will be used. This is not required if you are using SqlServer 2012+ dialect.

var query = new SqlQuery()
    .From("Person")
    .Select("Firstname")
    .Select("Lastname")
    .Select("Count(*)")
    .OrderBy("PersonId")
    .Skip(100)
    .Take(50);
    
Console.WriteLine(query.ToString());
SELECT 
Firstname,
Lastname,
Count(*) 
FROM Person 
ORDER BY PersonId OFFSET 100 ROWS FETCH NEXT 50 ROWS ONLY

In this sample we are using the default SQLServer2012 dialect.

Database Dialect Support

In our paging sample, SqlQuery used a syntax that is compatible with the SQL Server 2012.

With the Dialect method, we can change the server type that SqlQuery targets:

public SqlQuery Dialect(ISqlDialect dialect)

As of writing, this is the list of dialect types supported:

  • FirebirdDialect
  • PostgresDialect
  • SqliteDialect
  • SqlServer2000Dialect
  • SqlServer2005Dialect
  • SqlServer2012Dialect

If we wanted to target our query to the SQL Server 2005:

var query = new SqlQuery()
    .Dialect(SqlServer2005Dialect.Instance)
    .From("Person")
    .Select("Firstname")
    .Select("Lastname")
    .Select("Count(*)")
    .OrderBy("PersonId")
    .Skip(100)
    .Take(50);
    
Console.WriteLine(query.ToString());
SELECT * FROM (
SELECT TOP 150 
Firstname,
Lastname,
Count(*), ROW_NUMBER() OVER (ORDER BY PersonId) AS __num__ 
FROM Person) __results__ WHERE __num__ > 100

With SqliteDialect.Instance, the output would be:

SELECT 
Firstname,
Lastname,
Count(*) 
FROM Person 
ORDER BY PersonId LIMIT 50 OFFSET 100

If you are using only one type of database server with your application, you may avoid having to choose a dialect every time you start a query by setting the default dialect:

SqlSettings.DefaultDialect = SqliteDialect.Instance;

The dialect for a connection string is derived from the ProviderName field, or Dialect property of the connection string in appsettings.json. SqlSettings.DefaultDialect is just a fallback.