Mise SQL Interpolation

SQL interpolation

Mise SQL methods accept FormattableString. Ordinary interpolation holes become database parameters. The surrounding text describes SQL syntax, while :raw inserts a compile-time constant SQL fragment.

Bind values

var title = "O'Reilly's Biology";
var minimumCapacity = 20;

var query = new PostgreSqlQueryBuilder()
    .Select($"{CourseTbl.CourseIdCol:raw}")
    .From($"{CourseTbl.Table:raw}")
    .Where($"{CourseTbl.TitleCol:raw} = {title}")
    .Where($"{CourseTbl.CapacityCol:raw} >= {minimumCapacity}");

Mise emits parameter placeholders such as @p0 and @p1. The apostrophe in title stays in the parameter value and does not become SQL syntax. Do not add SQL quotes around an interpolation hole.

Pass the interpolation directly to the builder or store it as FormattableString:

FormattableString predicate = $"{CourseTbl.CapacityCol:raw} >= {minimumCapacity}";
query.Where(predicate);

Storing an interpolated expression in an ordinary string evaluates it before Mise can bind its values.

Insert SQL references

Generated table, column, and join members are constant strings. Use :raw when inserting them into SQL syntax. Without it, a column constant becomes a bound string value rather than a column reference.

The engine analyzer reports MISE012 when :raw receives a nonconstant string. Keep user input in normal interpolation holes. For a selectable sort order, branch between queries containing known column constants; see Paging and ordering.

Literal SQL in a fragment is also syntax. Put application values into holes rather than constructing the fragment with concatenation. :raw accepts no special escaping or validation of its SQL contents.

Null predicates

A null parameter is passed to the database as SQL NULL. SQL equality to null still follows SQL's three-valued logic. Use IS NULL when testing for missing values:

query.Where($"{CourseTbl.CampusIdCol:raw} IS NULL");

For an optional filter, decide whether to omit the predicate or use an explicit null check. Passing null to column = {value} does not turn it into column IS NULL.

Type hints

Use SqlParameterSpec when a parameter needs an explicit provider-neutral DbType:

var titleParameter = new SqlParameterSpec("title", title, DbType.String);
query.Where($"{CourseTbl.TitleCol:raw} = {titleParameter}");

DbType is in System.Data. Interpolation takes the specification's value and type hint, then assigns a parameter name in the statement's binding order. The supplied Name does not choose the interpolated placeholder.

Inspect compiled SQL

var compiled = query.Compile();
Console.WriteLine(compiled.Text);

foreach (var parameter in compiled.Parameters)
{
    Console.WriteLine($"{parameter.Name}: {parameter.DbType}");
}

Compile creates a fresh snapshot of SQL text, ordered parameters, command type, timeout, and reader behavior. It does not open a connection or execute SQL. Child queries share the statement's parameter numbering when compiled into a parent.

See Custom SQL and procedures for full statements and Queries for fluent composition.