Fluent SQL

WHERE Clause

You can filter your SELECT queries with a WHERE clause. In Fluent SQL, the Where() method can be chained from a select statement.

var selectStatement =
    Select.All("ID", "Name")
        .From("Customer")
        .Where("Country".IsEqualTo("NZ".Literal()));

SELECT [ID], [Name]
FROM [Customer]
WHERE [Country] = 'NZ';

There are a couple of things to note here. We can’t always write exactly what we want to write. The Fluent SQL interface is as close to spoken English as we can make it but we can’t make it the same as written SQL. We can’t write .Where("Country" = "NZ") for two reasons.

First, it’s not legal C#. To the C# compiler, that looks like an assignment and the left-hand side would need to be a variable. If we used ==, that would be a legal C# predicate, but would always evaluate to false at compile-time, which is not what we want. So we have the IsEqualTo() extension method.

Second, we need to differentiate between strings that represent column names and strings that represent string literals. For a reminder, see the String Literals section on the SELECT Statement page.

Comparison Operators

There are extensions methods for all the other comparison operators: IsNotEqualTo(), IsGreaterThan(), IsGreaterThanOrEqualTo(), IsLessThan(), IsLessThanOrEqualTo(), and Like().

Optional Filters

Perhaps filtering is optional. Sometimes the query should be filtered; sometimes not.

Fluent SQL uses the C# Functional Extensions library, which includes the Maybe type. Normally, the Where() method takes a Predicate as its argument. (You don’t often need to know this - type inference handles the details.) There’s an overload of Where() that takes a Maybe<Predicate>, an optional predicate. The argument may or may not exist. It allows you to optionally add a WHERE clause without breaking the fluency of the code.

In the C# code below, filter ia a Maybe<Predicate> that has a value if useFilter is true and has no value if it’s false.

var filter = useFilter ?
    "Country".IsEqualTo("NZ".Literal()) :
    Maybe<Predicate>.None;

var selectStatement =
    Select.All("ID", "Name")
        .From("Customer")
        .Where(filter);

When useFilter is true, we get a query with a WHEREclause.

SELECT [ID], [Name]
FROM [Customer]
WHERE [Country] = 'NZ';

When useFilter is false, there is no WHEREclause.

SELECT [ID], [Name]
FROM [Customer];

There’s no branching in your Fluent SQL code and you can chain other methods, like GroupBy() and OrderBy() as normal.

Logical Operators

Predicates can be combined with logical operators. These can be arbitrarily complex. If the predicates are nested, Fluent SQL will respect the nesting and add parentheses where they’re needed.

For example, to filter a query to customers in either New Zealand or Australia whose names begin with ‘B’:

var selectStatement =
    Select.All("ID", "Name")
        .From("Customer")
        .Where(
            ("Country".IsEqualTo("NZ".Literal())
                .Or("Country".IsEqualTo("AU".Literal())))
            .And("Name".Like("B%"))
        );

SELECT [ID], [Name]
FROM [Customer]
WHERE ([Country] = 'NZ' OR [Country] = 'AU') AND [Name] LIKE 'B%';

Other Predicates

As well as comparison and logical expressions, other predicates are available. Fluent SQL has methods IsNull(), IsNotNull(), Between(), NotBetween(), In(), and NotIn().

IS NULL

IsNull() and IsNotNull() take no arguments.

var selectStatement =
    Select.All("ID", "Name")
        .From("Customer")
        .Where("Name".IsNull());

SELECT [ID], [Name]
FROM [Customer]
WHERE [Name] IS NULL;

BETWEEN

Between() and NotBetween() take two arguments, a begin expresison and an end expresison.

var selectStatement =
    Select.All("ID", "Name")
        .From("Customer")
        .Where("CreditLimit".Between(1000, 10_000));

SELECT [ID], [Name]
FROM [Customer]
WHERE [CreditLimit] BETWEEN 1000 AND 10000;

IN

In() and NotIn() can take a sequence of expressions, either as a comma-separated list or an array.

var selectStatement =
    Select.All("ID", "Name")
        .From("Customer")
        .Where("Country".In("NZ".Literal(), "AU".Literal(), "FJ".Literal()));

var countries = new[] {"NZ", "AU", "FJ"}.Select(c => c.Literal()).ToArray();

var selectStatement =
    Select.All("ID", "Name")
        .From("Customer")
        .Where("Country".In(countries));

SELECT [ID], [Name]
FROM [Customer]
WHERE [Country] IN ('NZ', 'AU', 'FJ');

In() and NotIn() can also take a subquery, i.e. another select statement.

var selectStatement =
    Select.All("ID", "Name")
        .From("Customer")
        .Where("Country".In(
            Select.All("Alpha2Code")
                .From("Country")
                .Where("Region".IsEqualTo("Oceania".Literal()))
        ));

SELECT [ID], [Name]
FROM [Customer]
WHERE [Country] IN (
        SELECT [Alpha2Code]
        FROM [Country]
        WHERE [Region] = 'Oceania'
);

You’d be better this subquery as a table join, but we’ll get to that.