SQL formatter and format specifiers

The library exposes various methods for constructing raw SQL statements and clauses by using your database mapped entities.

FstCrud's format specifiers

In the context of an entity type, the following formatter specifiers can be used in a formattable string:

• P general SQL parameter

  • Argument type: string
  • Usage example: {nameof(params.EmployeeName):P} results in @EmployeeName under SQL Server.

• I general delimited identifier

  • Argument type: string
  • Usage example: {"identifier":I} results in [identifier] under SQL Server.

• T delimited table name or alias of an entity

  • Argument type: string|Type
  • Usage example: {typeof(Workstation):T} or {nameof(Workstation):T} may result in
    • [Workstations] as the table mapped to the type Workstation, if the type is that of the current entity or any other un-aliased entity already known in the statement through a JOIN statement.
    • [ws] if that's how the entity type is known in the statement (either as an aliased main entity or joined entity)

• C a column in the scope of the entity being used

  • argument type: string
  • usage example: {nameof(Building.Name}:C results in [BuildingName] under SQL Server, if that is the column name mapped to the Name property.
  • This notation can be used in simple scenarios in WHERE clauses, when running a query under a single main entity.

• TC a fully qualified column, which is a combination of T and C described earlier, with limited usage

  • Argument type: string
  • Usage example: {nameof(Building.Name):TC} may result in
    • [Building].[Name] if the type is that of the un-aliased current entity.
  • This notation can only be used to target the main entity so it becomes fairly useless when multiple entities join the query.

• of <alias|entity>:TC or the shorthand of <alias|entity> a fully qualified column in the context of multiple entities

  • Argument type: string
  • Usage example: {nameof(Building.Name):of bldg} results in [bldg].[Name] where bldg is the alias of Building in the statement.
  • This notation ensures that no clashes can occur between the entities present in a statement and is the recommended way of referencing columns. Always remember to alias all your entities in the statement with calls to statementOptions => statementOptions.WithAlias for the main entity and join=>join.WithAlias for all the other entities participating in the statement. You can then reference these aliases in all the SQL clauses.

var queryParams = new {
  EmployeeFirstName = "Jane",
  EmplopyeeLastName = "Doe",
  WorkstationMinInventoryIndex = 5
}

dbConnection.Find<Workstation>(statement => statement  
      .WithAlias("ws")
      .Include<Employee>(join => join.InnerJoin().WithAlias("em")
      .Where($@"
             {nameof(Employee.FirstName):of em} = {nameof(queryParams.EmployeeFirstName):P}
             AND {nameof(Employee.LastName):of em} = {nameof(queryParams.EmployeeLastName):P}
             AND {nameof(Workstation.InventoryIndex):of ws} = {nameof(queryParams.WorkstationMinInventoryIndex):P}
       ")
       .WithParameters(queryParams)
       .OrderBy($"{nameof(Employee.LastName):of em}"));

Standalone formatter

In more advanced scenarios, we will need a bit more control over the SQL that gets produced. To help us out, we can work with the FastCrud Formatter that can be accessed via Sql.Format<Entity>:

var rawSqlQuery = Sql.Format<Person>(
        $@" SELECT {nameof(Person.FirstName):C}
            FROM {nameof(Person):T}
            WHERE {nameof(Person.LastName):C} = {nameof(params.lastNameSearchParam):P}");

Through the generic argument Person in the example above, we have set up the context of the main entity, allowing for the relative column specifier C to resolve correctly.

There is no need to use delimiters and you can stop worrying about mismatched table or column names.

When it comes to formattable strings (the ones generated via $""), always keep in mind that they only stay in that form if directly assigned to the FormattableString type. They have to be passed in this form to the FastCrud's formatter, however nothing is stopping us from combining them, may that be for a very complex WHERE clause or a subquery.

// do not use 'var' as that will resolve the formattable strings
FormattableString firstNameQuery = $"{nameof(Person.FirstName):C} = {nameof(params.firstNameSearchParam):P}"; 
FormattableString lastNameQuery = $"{nameof(Person.LastName):C} = {nameof(params.lastNameSearchParam):P}"; 

dbConnection.Find<Person>(statement => statement.Where($"{firstNameQuery} AND {lastNameQuery}"));
or
Sql.Format<Person>($"{firstNameQuery} AND {lastNameQuery}");

SQL Standalone Formattables

As a last resort, a number of standalone "formattables" are also provided. These can be used when you want to create complex queries or database agnostic queries.

• Sql.Parameter(parameterName)
  • When used with a FastCrud formatter, it defaults to the "P" specifier (e.g. @Param).
  • When used with any other formatter, it defaults to the raw parameter name, but don't forget that the "P" specifier is still available in this mode as well.
• Sql.Identifier(identifierName)
  • When used with a FastCrud formatter, it defaults to the "I" specifier (e.g. [Identifier] in Sql Server).
  • When used with any other formatter, it defaults to the raw identifier but the "I" specifier is still available in this mode as well.
  • Do not use this method for table or column names.
• Sql.Entity<TEntity>(optionalAlias)
  • When used with a FastCrud's formatter, it responds to the "T" specifier for table or alias (e.g. "{Sql.Entity<Employee>():T}" will output [EmployeeTable] in Sql Server), however you don't have to add the specifier if you have provided an alias (e.g. "{Sql.Entity<Employee>("em")}" will output [em].[EmployeeTable] in Sql Server)
  • When used with any other formatter, it defaults to the raw alias (if provided) or the table name associated with the entity but the "T" specifier is still available in this mode as well.
• Sql.Entity<TEntity>(entity => entity.prop, optionalAlias)
  • When used with a FastCrud's formatter, it responds to
    • the "T" specifier for table or alias or
    • the "C" specifier for the single column name or
    • the "TC" specifier for a fully qualified SQL column, using either the alias (if provided in the constructor) or the table name
    • defaults to "TC" if the optional alias is provided.
  • When used with any other formatter, it defaults to the raw column name associated with the provided property but the "C", "T" and "TC" specifiers still work in this mode as well.

As a reminder, these formattables can be used either with:

1. the FactCrud formatter (either standalone with Sql.Format<TEntity> or in subqueries) or
2. outside FastCrud, to create your own custom standalone queries that can further be used with any other library you want to access your data source with. They are indeed verbose, however you'll still benefit from the table mappings, column mappings and database conventions.

Let's take a look at a couple of examples.

First, we'll start by creating a manual join using the FastCrud's standalone formatter.

OrmConfiguration.DefaultDialect = SqlDialect.MsSql|PostgreSql;
[...]
var searchParams = new
	{
		FullName = fullNameSearchArgument
	};
// Note that when using the FastCrud's formatter with an alias, the format specifier TC is redundant.
// We attach the formatter to EmployeeDbEntity but honestly, in this case, it doesn't matter since we're working with multiple entities at the same time.
var manualQuery = Sql.Format<EmployeeDbEntity>($@"
	SELECT
		{Sql.Entity<EmployeeDbEntity>(em => em.HireDate, "em")},
		{Sql.Entity<EmployeeDbEntity>(em => em.ShiftStartingTime, "em")},
		{Sql.Entity<WorkstationDbEntity>(ws => ws.WorkstationId, "ws")}
	FROM
		{Sql.Entity<EmployeeDbEntity>():T} AS {Sql.Identifier("em")}
		INNER JOIN {Sql.Entity<WorkstationDbEntity>():T} AS {Sql.Identifier("ws")}
			ON {Sql.Entity<WorkstationDbEntity>(ws => ws.WorkstationId, "ws")} = {Sql.Entity<EmployeeDbEntity>(em => em.WorkstationId, "em")}
	WHERE
		{Sql.Entity<EmployeeDbEntity>(em => em.FullName, "em")} = {Sql.Parameter(nameof(searchParams.FullName))}
");

The output for the MsSql dialect will be:

SELECT
	[em].[HiringDate],
	[em].[ShiftStartingTime],
	[ws].[WorkstationId]
FROM
	[Employee] AS [em]
	INNER JOIN [Workstations] AS [ws]
		ON [ws].[WorkstationId] = [em].[WorkstationId]
WHERE
	[em].[FullName] = @FullName

And the output for PostgreSQL will be:

SELECT
	"em"."HiringDate",
	"em"."ShiftStartingTime",
	"ws"."WorkstationId"
FROM
	"Employee" AS "em"
	INNER JOIN "Workstations" AS "ws"
		ON "ws"."WorkstationId" = "em"."WorkstationId"
WHERE
	"em"."FullName" = @FullName

On the other hand, let's now use the standard .NET formatter:

var searchParams = new
	{
		FullName = fullNameSearchArgument
	};
var manualQuery = FormattableString.Invariant($@"
	SELECT
		em.{Sql.Entity<EmployeeDbEntity>(em => em.HireDate)},
		em.{Sql.Entity<EmployeeDbEntity>(em => em.ShiftStartingTime)},
		ws.{Sql.Entity<WorkstationDbEntity>(ws => ws.WorkstationId)}
	FROM
		{Sql.Entity<EmployeeDbEntity>():T} AS em
		INNER JOIN {Sql.Entity<WorkstationDbEntity>()} AS ws
			ON ws.{Sql.Entity<WorkstationDbEntity>(ws => ws.WorkstationId)} = em.{Sql.Entity<EmployeeDbEntity>(em => em.WorkstationId)}
	WHERE
		em.{Sql.Entity<EmployeeDbEntity>(em => em.FullName)} = @{Sql.Parameter(nameof(searchParams.FullName))}
");

When running under the standard .NET formatter, the formattables will still give you access to the resolved names of tables and columns, but will completely ignore the database conventions. However, notice that even under the standard formatter, you can bring back the full functionality of a formattable by using the FastCrud format specifiers (see the output of {Sql.Entity<EmployeeDbEntity>():T} below).

SELECT
	em.HiringDate,
	em.ShiftStartingTime,
	ws.WorkstationId
FROM
	[Employee] AS em
	INNER JOIN Workstations AS ws
		ON ws.WorkstationId = em.WorkstationId
WHERE
	em.FullName = @FullName