Concatenate binary data using || by virzak · Pull Request #2200

Concatenate binary data using || by virzak · Pull Request #2200 · npgsql/efcore.pg

Ultimately, the goal is to have an easy way to implement the last non null puzzle. lastval is the one that needs to be generated.

id     col1     lastval
----   ------   -----------
2      NULL     NULL
3      10       10
5      -1       -1
7      NULL     -1
11     NULL     -1
13     -12      -12
17     NULL     -12
19     NULL     -12
23     1759     1759

This SQL is the most efficient for SQL server.

SELECT id, col1,
  CAST(
    SUBSTRING(
  MAX( CAST(id AS BINARY(4)) + CAST(col1 AS BINARY(4)) )
    OVER( ORDER BY id
      ROWS UNBOUNDED PRECEDING ),
  5, 4)
    AS INT) AS lastval
FROM dbo.T1;

The walkthrough is here (unfortunately behind a registration gate) and also mentioned here

So I'm writing an extension that handles:

Window functions
Binary manipulations

I just got it to work with SQL Server, but not with SQLite or PostgreSQL (never used it before).

The LINQ looks as follows:

var query = dbContext.LastNonNulls
.Select(r => new
{
    LastNonNull =
    EF.Functions.ToInt32(
    EF.Functions.Substring(
        EF.Functions.MaxOver(
            EF.Functions.Concatenate(
                EF.Functions.GetBytes(r.Id), EF.Functions.GetBytes(r.Col1)
            ), EF.Functions.CreateOrdering(r.Id))
        , 5, 4
    )
    )
});

The innermost sql for posgres shoud (I think) look as follows:

SELECT l."Id"::bit(32) || l."Col1"::bit(32)
FROM "LastNonNulls" AS l

One way to generate that would be to override FindBaseMapping and invoke this code:

	if (clrType == typeof(BitArray))
	{
	mapping = mappingInfo.IsFixedLength ?? false ? _bit : _varbit;
	return mapping.Clone($"{mapping.StoreType}({mappingInfo.Size})", mappingInfo.Size);
	}

That's how the concatenation operands end up with BitArray type.

The current workaround is this:

public class BinaryNpgsqlQuerySqlGenerator : NpgsqlQuerySqlGenerator
{
    public BinaryNpgsqlQuerySqlGenerator(QuerySqlGeneratorDependencies dependencies, bool reverseNullOrderingEnabled, Version postgresVersion) : base(dependencies, reverseNullOrderingEnabled, postgresVersion)
    {
    }

    protected override string GetOperator(SqlBinaryExpression e)
        => e.OperatorType switch
        {
            ExpressionType.Add when
                e.Type == typeof(BitArray) || e.Left.TypeMapping?.ClrType == typeof(BitArray) || e.Right.TypeMapping?.ClrType == typeof(BitArray)
                => " || ",
            _ => base.GetOperator(e)
        };
}

I'll be publishing the entire source code within the next few days.