Fluent SQL

Specifying Table Names

Names as Strings

So far in this tutorial, we’ve just specified table names as strings.

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

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

For simple situations this is fine.

Qualified Names

If you want to reuse the table information, or qualify the name with schema or database information, you can create a table object. You can create the object with just the table name, or with the schema and database name

var customer = Table.Create("Customer");

var customer = Table.Create("dbo", "Customer");

var customer = Table.Create("MACHINE123", "dbo", "Customer");

If you want to provide a database name and still use the implicit schema, pass an empty string for the schema name.

var customer = Table.Create("MACHINE123", "", "Customer");

Use the new table object in a select statement in exactly the same way as you’d use the raw name.

var customer = Table.Create("dbo", "Customer");

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

The generated SQL will use the qualified name.

SELECT [Name]
FROM [dbo].[Customer];

Aliases

Add an alias to a table name by calling As() on the table. To use the alias in the select list, use the table’s indexer to refer to the column. Any column names passed in as strings will not be qualified.

var customer = Table.Create("dbo", "Customer").As("c");

var selectStatement =
    Select.All(customer["ID"], "Name")
        .From(customer);

SELECT  [c].[ID], [Name]
FROM [dbo].[Customer] AS [c];

Table Hints

Add table hints by calling With() on a table in a query.

var customer = Table.Create("dbo", "Customer");

var selectStatement =
    Select.All("ID", "Name")
        .From(customer.With(Hint.NoLock));

SELECT [ID], [Name]
FROM [dbo].[Customer] WITH (NOLOCK);

Note that it’s probably best to trust the query optimiser. Use hints only as a last resort.