Add a custom InterpolatedStringHandler to handle allocation free sql and parameter collection

[TwentyFourMinutes]

Definition

The purpose of this handler should be to replace the old usage of the FormattableString, which worked and required way less code, however the memory allocation was quite immense.

With the new custom string interpolation we got with .NET 6 and C# 10 we can reduce the amount allocation of heap allocations to exactly 0. Not only are we avoiding the boxing of value type parameters, but also the allocations of the FormattableString object itself and the Expression Trees, which were required to allow for maintainable SQL.

Design

As we enforce that a query through the fluent API has to be inline and the content of the lambda has to be an interpolated string, we can use a ThreadStatic variable. Which acts as shared data pool between the query method and the SqlInterpolationHandler.

The data we provide to the string handler will contain the ParameterIndecies array which will contain all the locations of the parameters (0 based). This will be required once we add the following syntax table => "select * from {table}", another array will be required which contains the actual string that will replace the fake data. However this syntax will most likely be implemented in v2.1.0.

Additionally we will be able to give the exact length of the string that the CommandBuilder will produce as long as it won't contain any collection parameters.

Proposal

[InterpolatedStringHandler]
public struct SqlInterpolationHandler
{
    internal class ScopeData
    {
        [ThreadStatic]
        internal static ScopeData Current;

        internal StringBuilder CommandBuilder;
        internal NpgsqlParameterCollection Parameters;
        internal short[] ParameterIndecies;
    }

    private int _interpolationIndex;
    private short _parameterIndex;
    private short _absolutParameterIndex;
    private short _nextParameterIndex;

    private readonly StringBuilder _commandBuilder;
    private readonly short[] _parameterIndecies;
    private readonly NpgsqlParameterCollection _parameters;

    public SqlInterpolationHandler(int literalLength, int formattedCount)
    {
        _interpolationIndex = 0;
        _parameterIndex = 0;
        _absolutParameterIndex = 0;

        var current = ScopeData.Current;

        _commandBuilder = current.CommandBuilder;
        _parameterIndecies = current.ParameterIndecies;
        _parameters = current.Parameters;
        _nextParameterIndex = _parameterIndecies.Length > 0 ? _parameterIndecies[0] : (short)-1;
    }

    public void AppendLiteral(string value)
    {
        _commandBuilder.Append(value);
    }

    public void AppendFormatted<T>(T value)
    {
        BaseAppendFormatted(value);
    }

    public void AppendFormatted<T>(T value, string format) => AppendFormatted<T>(value);

    private void BaseAppendFormatted<T>(T value)
    {
        if (_interpolationIndex++ != _nextParameterIndex)
        {
            return;
        }

        if (++_parameterIndex < _parameterIndecies.Length)
            _nextParameterIndex = _parameterIndecies[_parameterIndex];

        var parameterName = "@p" + _absolutParameterIndex++;

        _parameters.Add(new NpgsqlParameter<T>(parameterName, value));

        _commandBuilder.Append(parameterName);
    }
}

Usage

Func<SqlInterpolationHandler> lambda = () => $"select * from table where id = {0}";

var command = new NpgsqlCommand();
command.Connection = conn;

var commandBuilder = new StringBuilder(34);

var scope = new SqlInterpolationHandler.ScopeData
{
    ParameterIndecies = new short[ ] { 0 },
    Parameters = command.Parameters,
    CommandBuilder = commandBuilder
};

SqlInterpolationHandler.ScopeData.Current = scope;

query.Invoke();

command.CommandText = commandBuilder.ToString();

[TwentyFourMinutes]

The syntax which was mentioned in the design e.g. table => "select * from {table}" will already be shipped in v2.0.0.