Fluent SQL

SELECT Statement

When you want to select some data from your database, the first thing you do in SQL is type SELECT. Fluent SQL is the same.

In your IDE, type Select and . and code completion will give you the options All, Distinct, and Top. Choose All. ¹ Pass in a list of column names as arguments to the All method.

Chain a From clause onto the query and give it a table name.

var selectStatement = 
    Select.All("ID", "Name", "Description", "LastUpdated")
        .From("Customer");

Generate the SQL and write it to the console.

Console.WriteLine(customerQuery.ToSQLOrError());

You’ll get a SQL SELECT statement that looks as you’d expect.

SELECT [ID], [Name], [Description], [LastUpdated]
FROM [Customer]

Select list arguments

The Select.All() method takes an open-ended seqeunce of select list expressions as its arguments. A string argument like "ID" or "Name" is interpreted as a column name and is escaped as shown above. You can also pass in boolean, numeric, DateTime, and Guid arguments and these will be interpreted as literal values.

var selectStatement = Select.All(1, false, 3.14, new DateTime(2024, 9, 1), Guid.NewGuid());

SELECT 1, 0, 3.14, '2024-09-01 00:00:00', '781eafbe-4eaa-4a91-ba56-ba98d57f56ec';

Error handling

If you create a SELECT statement with no arguments, you’ll get an error.

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

-- SQL Error: Select expression list cannot be empty.

The error is rendered as a SQL comment, so that if it gets passed to SQL Server to execute it will be ignored.

Aside: Fluent SQL uses the C# Functional Extensions library, which includes the Result type. The return type of the C# expression shown above is IResult<SelectStatement>. The statement is pre-validated and the result is flagged as a success or a failure. Results can be chained so that execution can be shortcut when an error occurs, without branching in the client code.

Aliases

To alias a column name, chain the As() method onto any select list expression you want to rename.

var selectStatement = Select.All("CUSTNO".As("CustomerNumber"))
    .From("Customer");

String literals

String literals need a little extra care. String arguments are interpreted as column names, as this is the most common requirement, so you need to explicitly tell Fluent SQL when a string is to be taken literally. Use the Literal() extension method on string to turn it into a string literal SQL expression. The SQL renderer will automatically escape quotes.

var selectStatement = Select.All("Alice".Literal(), "Bob's".Literal());

SELECT  'Alice', 'Bob''s';

All of the aggregate, type conversion, and string manipulation functions you’d expect to see are available as well. We’ll introduce an example next.