Common Table Expressions

A common table expression defines a result set that can be used later. In the scope of a SELECT statement, it’s a reusable subquery.

In Fluent SQL, introduce a common table expression (CTE) with CTE.With(). You can introduce as many CTEs as you need by chaining With() methods. When all your CTEs are defined, chain a Select() method call to build a query using them.

var customer = Table.Create("dbo", "Customer").As("cust");
var country = Table.Create("dbo", "Country").As("ctry");

var selectStatement = CTE.With("cust_ctry")
    .As(
        Select
            .All(customer["ID"].Count().As("CustomerCount"), country["Name"])
            .From(
                customer.InnerJoin(country)
                    .On(customer["Country"].IsEqualTo(country["Alpha2Code"]))
            )
    )
    .Select(
        Select.All("CustomerCount", "Name".As("Country"))
        .From("cust_ctry")
    );

WITH [cust_ctry]
AS
(
        SELECT COUNT([cust].[ID]) AS [CustomerCount], [ctry].[Name]
        FROM [dbo].[Customer] AS [cust]
                INNER JOIN [dbo].[Country] AS [ctry]
                        ON [cust].[Country] = [ctry].[Alpha2Code]
)
SELECT  [CustomerCount], [Name] AS [Country]
FROM [cust_ctry];