SqlQuery Object

[namespace: Serenity.Data] - [assembly: Serenity.Data]

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

Advantages

SqlQuery offers some advantages over hand crafted SQL:

  • Using IntelliSense feature of Visual Studio while composing SQL

  • Fluent interface with minimal overhead

  • Reduced syntax errors as the query is checked compile time, not execution time.

  • Clauses like Select, Where, Order By can be used in any order. They are placed at correct positions when converting the query to string. Similary, 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. You can also use manually named parameters if required.

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

  • With the dialect system, query can be targeted at specific server type and version.

  • 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. Also it does left/right joins automatically.

How To Try Samples Here

I recommend using LinqPad to try samples given here.

You should add reference to Serenity.Core, Serenity.Data and Serenity.Data.Entity NuGet packages.

Another option is to locate and reference these DLLs directly from a Serene application’s bin or packages directory.

Make sure you add Serenity and Serenity.Data to Additional Namespace Imports in Query Properties dialog.

A Simple Select Query

  1. void Main()
  2. {
  3.     var query = new SqlQuery();
  4.     query.Select("Firstname");
  5.     query.Select("Surname");
  6.     query.From("People");
  7.     query.OrderBy("Age");
  9.     Console.WriteLine(query.ToString());
  10. }

This will result in output:

  1. SELECT 
  2. Firstname,
  3. Surname 
  4. FROM People 
  5. 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:

  1. 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 Select method with string parameter "FirstName". Our query is now like this:

  1. SELECT Firstname FROM

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

  1. SELECT Firstname, Surname FROM

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

  1. SELECT Firstname, Surname FROM People ORDER BY Age

Method Call Order and Its Effects

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

  1. void Main()
  2. {
  3.     var query = new SqlQuery();
  4.     query.From("People");
  5.     query.OrderBy("Age");
  6.     query.Select("Surname");
  7.     query.Select("Firstname");
  9.     Console.WriteLine(query.ToString());
  10. }

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

  1. SELECT 
  2. Surname,
  3. Firstname 
  4. FROM People 
  5. ORDER BY Age

You might use methods like Select, From, OrderBy, GroupBy in any order, and can also mix them (e.g. call Select, then OrderBy, then 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 query.. Almost all SqlQuery methods are chainable, and returns the query itself as result.

We may rewrite the query like this:

  1. void Main()
  2. {
  3.     var query = new SqlQuery()
  4.         .From("People")
  5.         .Select("Firstname")
  6.         .Select("Surname")
  7.         .OrderBy("Age");
  9.     Console.WriteLine(query.ToString());
  10. }

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

We could even get rid of the query variable:

  1. void Main()
  2. {
  3.     Console.WriteLine(
  4.         new SqlQuery()
  5.             .From("People")
  6.             .Select("Firstname")
  7.             .Select("Surname")
  8.             .OrderBy("Age")
  9.             .ToString());
  10. }

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

Select Method

  1. 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).

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 resulting query with a comma between.

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

  1. public SqlQuery SelectMany(params string[] expressions)

For example:

  1. void Main()
  2. {
  3.     var query = new SqlQuery()
  4.         .From("People")
  5.         .SelectMany("Firstname", "Surname", "Age", "Gender")
  6.         .ToString();
  8.     Console.WriteLine(query.ToString());
  9. }
  1. SELECT 
  2. Firstname,
  3. Surname,
  4. Age,
  5. Gender 
  6. FROM People

I’d personally prefer calling Select method multiple times.

You might be wondering, why multiple selection is not just another Select overload. It’s because Select has a more commonly used overload to select a column with alias:

  1. public SqlQuery Select(string expression, string alias)
  1. void Main()
  2. {
  3.     var query = new SqlQuery()
  4.         .Select("(Firstname + ' ' + Surname)", "Fullname")
  5.         .From("People")
  6.         .ToString();
  8.     Console.WriteLine(query.ToString());
  9. }
  1. SELECT 
  2. (Firstname + ' ' + Surname) AS [Fullname] 
  3. FROM People

From Method

  1. 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, table name will be added to FROM statement with a comma between. Thus, it will be a CROSS JOIN:

  1. void Main()
  2. {
  3.     var query = new SqlQuery()
  4.         .From("People")
  5.         .From("City")
  6.         .From("Country")
  7.         .Select("Firstname")
  8.         .Select("Surname")
  9.         .OrderBy("Age");
  11.     Console.WriteLine(query.ToString());
  12. }
  1. SELECT 
  2. Firstname,
  3. Surname 
  4. FROM People, City, Country 
  5. ORDER BY Age

Using Alias Object with SqlQuery

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

  1. void Main()
  2. {
  3.     var query = new SqlQuery()
  4.         .From("Person p")
  5.         .From("City c")
  6.         .From("Country o")
  7.         .Select("p.Firstname")
  8.         .Select("p.Surname")
  9.         .Select("c.Name", "CityName")
  10.         .Select("o.Name", "CountryName")
  11.         .OrderBy("p.Age")
  12.         .ToString();
  14.     Console.WriteLine(query.ToString());
  15. }
  1. SELECT 
  2. p.Firstname,
  3. p.Surname,
  4. c.Name AS [CityName],
  5. o.Name AS [CountryName] 
  6. FROM Person p, City c, Country o 
  7. ORDER BY p.Age

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

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

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

  1. void Main()
  2. {
  3.     var p = new Alias("Person", "p");
  4.     Console.WriteLine(p + "Surname"); // + operator overload
  5.     Console.WriteLine(p["Firstname"]); // through indexer
  6. }
  1. p.Surname
  2. p.Firstname

Unfortunately 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 making use of Alias objects:

  1. void Main()
  2. {
  3.     var p = new Alias("Person", "p");
  4.     var c = new Alias("City", "c");
  5.     var o = new Alias("Country", "o");
  7.     var query = new SqlQuery()
  8.         .From(p)
  9.         .From(c)
  10.         .From(o)
  11.         .Select(p + "Firstname")
  12.         .Select(p + "Surname")
  13.         .Select(c + "Name", "CityName")
  14.         .Select(o + "Name", "CountryName")
  15.         .OrderBy(p + "Age")
  16.         .ToString();
  18.     Console.WriteLine(query.ToString());
  19. }
  1. SELECT 
  2. p.Firstname,
  3. p.Surname,
  4. c.Name AS [CityName],
  5. o.Name AS [CountryName] 
  6. FROM Person p, City c, Country o 
  7. ORDER BY p.Age

As seen above, 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…

  1. void Main()
  2. {
  3.     const string Firstname = "Firstname";
  4.     const string Surname = "Surname";
  5.     const string Name = "Name";
  6.     const string Age = "Age";
  8.     var p = new Alias("Person", "p");
  9.     var c = new Alias("City", "c");
  10.     var o = new Alias("Country", "o");
  11.     var query = new SqlQuery()
  12.         .From(p)
  13.         .From(c)
  14.         .From(o)
  15.         .Select(p + Firstname)
  16.         .Select(p + Surname)
  17.         .Select(c + Name, "CityName")
  18.         .Select(o + Name, "CountryName")
  19.         .OrderBy(p + Age)
  20.         .ToString();
  22.     Console.WriteLine(query.ToString());
  23. }

…we would take advantage of intellisense feature and have some more compile time checks.

Obviously, 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:

  1. public class PeopleAlias : Alias
  2. {
  3.     public PeopleAlias(string alias) 
  4.         : base("People", alias) { }
  6.     public string ID { get { return this["ID"]; } }
  7.     public string Firstname { get { return this["Firstname"]; } }
  8.     public string Surname { get { return this["Surname"]; } }
  9.     public string Age { get { return this["Age"]; } }
  10. }
  12. public class CityAlias : Alias
  13. {
  14.   public CityAlias(string alias)
  15.       : base("City", alias) { }
  17.   public string ID { get { return this["ID"]; } }
  18.   public string CountryID { get { return this["CountryID"]; } }
  19.   public new string Name { get { return this["Name"]; } }
  20. }
  22. public class CountryAlias : Alias
  23. {
  24.   public CountryAlias(string alias)
  25.       : base("Country", alias) { }
  27.   public string ID { get { return this["ID"]; } }
  28.   public new string Name { get { return this["Name"]; } }
  29. }
  31. void Main()
  32. {
  33.     var p = new PeopleAlias("p");
  34.     var c = new CityAlias("c");
  35.     var o = new CountryAlias("o");
  36.     var query = new SqlQuery()
  37.         .From(p)
  38.         .From(c)
  39.         .From(o)
  40.         .Select(p.Firstname)
  41.         .Select(p.Surname)
  42.         .Select(c.Name, "CityName")
  43.         .Select(o.Name, "CountryName")
  44.         .OrderBy(p.Age)
  45.         .ToString();
  47.     Console.WriteLine(query.ToString());
  48. }

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 sample above, we used SqlQuery.From overload that takes an Alias parameter:

  1. public SqlQuery From(Alias alias)

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

OrderBy Method

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

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

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

By default, OrderBy appends specified expressions to 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 user orders by a column in a grid, name of the column should be inserted at index 0.

  1. public SqlQuery OrderByFirst(string expression, bool desc = false)
  1. void Main()
  2. {
  3.     var query = new SqlQuery()
  4.         .Select("Firstname")
  5.         .Select("Surname")
  6.         .From("Person")
  7.         .OrderBy("PersonID");
  9.     query.OrderByFirst("Age");
  11.     Console.WriteLine(query.ToString());
  12. }
  1. SELECT 
  2. Firstname,
  3. Surname 
  4. FROM Person 
  5. ORDER BY Age, PersonID

Distinct Method

  1. public SqlQuery Distinct(bool distinct)

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

  1. void Main()
  2. {
  3.     var query = new SqlQuery()
  4.         .Select("Firstname")
  5.         .Select("Surname")
  6.         .From("Person")
  7.         .OrderBy("PersonID")
  8.         .Distinct(true);
  10.     Console.WriteLine(query.ToString());
  11. }
  1. SELECT DISTINCT 
  2. Firstname,
  3. Surname 
  4. FROM Person 
  5. ORDER BY PersonID

GroupBy Method

  1. public SqlQuery GroupBy(string expression)

GroupBy works similar to OrderBy but it doesn’t have a GroupByFirst variant.

  1. SELECT 
  2. Firstname,
  3. Lastname,
  4. Count(*) 
  5. FROM Person 
  6. GROUP BY Firstname, LastName
  1. SELECT 
  2. Firstname,
  3. Lastname,
  4. Count(*) 
  5. FROM Person 
  6. GROUP BY Firstname, LastName

Having Method

  1. public SqlQuery Having(string expression)

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

  1. void Main()
  2. {
  3.     var query = new SqlQuery()
  4.         .From("Person")
  5.         .Select("Firstname")
  6.         .Select("Lastname")
  7.         .Select("Count(*)")
  8.         .GroupBy("Firstname")
  9.         .GroupBy("LastName")
  10.         .Having("Count(*) > 5");
  12.     Console.WriteLine(query.ToString());
  13. }
  1. SELECT 
  2. Firstname,
  3. Lastname,
  4. Count(*) 
  5. FROM Person 
  6. GROUP BY Firstname, LastName 
  7. HAVING Count(*) > 5

Paging Operations (SKIP / TAKE / TOP / LIMIT)

  1. public SqlQuery Skip(int skipRows)
  3. public SqlQuery Take(int rowCount)

SqlQuery has paging methods similar to LINQ Take and Skip.

These are mapped to SQL keywords depending on 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.

  1. void Main()
  2. {
  3.     var query = new SqlQuery()
  4.         .From("Person")
  5.         .Select("Firstname")
  6.         .Select("Lastname")
  7.         .Select("Count(*)")
  8.         .OrderBy("PersonId")
  9.         .Skip(100)
  10.         .Take(50);
  12.     Console.WriteLine(query.ToString());
  13. }
  1. SELECT 
  2. Firstname,
  3. Lastname,
  4. Count(*) 
  5. FROM Person 
  6. 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 Sql Server 2012.

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

  1. public SqlQuery Dialect(ISqlDialect dialect)

As of writing, these are the list of dialect types supported:

  1. FirebirdDialect
  2. PostgresDialect
  3. SqliteDialect
  4. SqlServer2000Dialect
  5. SqlServer2005Dialect
  6. SqlServer2012Dialect

If we wanted to target our query to Sql Server 2005:

  1. void Main()
  2. {
  3.     var query = new SqlQuery()
  4.         .Dialect(SqlServer2005Dialect.Instance)
  5.         .From("Person")
  6.         .Select("Firstname")
  7.         .Select("Lastname")
  8.         .Select("Count(*)")
  9.         .OrderBy("PersonId")
  10.         .Skip(100)
  11.         .Take(50);
  13.     Console.WriteLine(query.ToString());
  14. }
  1. SELECT * FROM (
  2. SELECT TOP 150 
  3. Firstname,
  4. Lastname,
  5. Count(*), ROW_NUMBER() OVER (ORDER BY PersonId) AS __num__ 
  6. FROM Person) __results__ WHERE __num__ > 100

With SqliteDialect.Instance, output would be:

  1. SELECT 
  2. Firstname,
  3. Lastname,
  4. Count(*) 
  5. FROM Person 
  6. 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:

  1. SqlSettings.DefaultDialect = SqliteDialect.Instance;

Write code above in your application start method, e.g. global.asax.cs.