Fluent SQL

Select List Expressions

SELECT *

You might want to select all columns using SELECT *. In C# we can’t use * as a name, so Fluent SQL uses a class called Star with a static property that returns a SQLExpression.

var selectStatement = Select.All(Star.Value)
    .From("Customer");

SELECT *
FROM [Customer];

It’s a clunky construction that breaks the fluency of the interface a bit, but it’s there if you need it. However, your database administrator will thank you for not using it. ¹

SELECT COUNT

Aggregate functions like Count() can be chained onto select list expressions.

var selectStatement = Select.All("ID".Count())
    .From("Customer");

SELECT COUNT([ID])
FROM [Customer];

Distinct options are also available for all aggregate functions.

var selectStatement = Select.All("Name".CountDistinct())
    .From("Customer");

SELECT COUNT(DISTINCT [Name])
FROM [Customer];

Count() can be chained onto any select list expression.

var selectStatement = Select.All(Star.Value.Count())
    .From("Customer");

SELECT COUNT(*)
FROM [Customer];

MIN and MAX

All the aggregate functions you’d expect to see, like Max(), can be chained onto select list expressions.

var selectStatement = Select.All("LastModified".Max())
    .From("Customer");

SELECT MAX([LastModified])
FROM [Customer];

Be aware that the Min() and Max() string extension methods hide methods of the same name that come with System.Linq. A string can be thought of as an IEnumerable<char>, so in LINQ "Bazinga!".Max() will return z, the “maximum” character in that sequence. If you’re using Fluent SQL, our version is probably more useful.

You can also use Avg(), Sum(), Var() and many others. See T-SQL | Aggregate Functions.

Type Conversion

Converting a SQL expression from one data type to another can be done with the CastAs(), ConvertTo(), and Parse() functions and their Try... variants.

You can use conversion styles with ConvertTo(). You can either use the numeric codes if you’re familiar with them or use the values provided in the various data type style enums.

var selectStatement = Select.All("LastModified".ConvertTo(DataType.NVarChar(50), 103))
    .From("Customer");

var selectStatement = Select.All("LastModified".ConvertTo(DataType.NVarChar(50), DateTimeDataStyle.BritishFrench))
    .From("Customer");

SELECT CONVERT(NVARCHAR(50), [LastModified], 103)
FROM [Customer];

The various styles and their meanings are described here: [T-SQL | CAST and CONVERT].