[API Proposal]: DbDataReader - span based column name APIs

[mgravell]

Background and motivation

Column names are an inherent part of DB reads. Currently, DbDataReader exposes multiple APIs for discussing column names, the most obvious being:

public abstract class DbDataReader
{
    public abstract string GetName(int ordinal);
    public abstract int GetOrdinal(string name);
}

While it is possible to ignore the column names and rely purely on ordinals, a lot of code - especially ORM code - uses the column names to assert the intent (think "select * from orders where region=@region" - what is the column order?)

API Proposal

Suggestion:

public abstract class DbDataReader
{
    public abstract string GetName(int ordinal);
    public abstract int GetOrdinal(string name);

+    public virtual ReadOnlySpan<char> GetNameSpan(int ordinal) => GetName(ordinal).AsSpan();
+    public virtual int GetOrdinal(ReadOnlySpan<char> name) => GetOrdinal(name.ToString());
}

This would allow implementations to use pooled char[] data for the names, only lazily materializing a string if the GetName API is used. Callers such as ORMs could update to use span-based testing, completely avoiding the string usage.

I would happily update Dapper as a prototype.

API Usage

This is an existing Dapper (ORM) usage, taken from AOT generator output:

 public override object? Tokenize(global::System.Data.Common.DbDataReader reader, global::System.Span<int> tokens, int columnOffset)
            {
                for (int i = 0; i < tokens.Length; i++)
                {
                    int token = -1;
                    var name = reader.GetName(columnOffset);
                    var type = reader.GetFieldType(columnOffset);
                    switch (NormalizedHash(name))
                    {
                        case 926444256U when NormalizedEquals(name, "id"):
                            token = type == typeof(int) ? 0 : 3; // two tokens for right-typed and type-flexible
                            break;
                        case 2369371622U when NormalizedEquals(name, "name"):
                            token = type == typeof(string) ? 1 : 4;
                            break;
                        case 4237030186U when NormalizedEquals(name, "birthdate"):
                            token = type == typeof(DateOnly) ? 2 : 5;
                            break;

                    }
                    tokens[i] = token;
                    columnOffset++;

                }
                return null;
            }

The complete change here would be to the line:

- var name = reader.GetName(columnOffset);
+ var name = reader.GetNameSpan(columnOffset);

et voila, zero strings; if the provider supports the usage! obviously this also requires

If in doubt over the .ToString() in the base method: I'll lose GetOrdinal in a heartbeat to get GetNameSpan !

Alternative Designs

No response

Risks

No response

[mgravell]

/cc @roji

[dotnet-policy-service]

Tagging subscribers to this area: @roji, @ajcvickers
See info in area-owners.md if you want to be subscribed.

[roji]

@mgravell thanks for the suggestion.

In general, we've viewed DbDataReader's name-based APIs as "low-perf" convenience APIs. In other words, a very-high-perf conscious consumer would be expected to just always use ordinals everywhere, and not deal with column names at all; after all, that user is also setting the SQL on the command which produced the reader, so they should be able to know the ordinals for reading out the results.

Is there a reason/scenario that a (high-perf) consumer of DbDataReader would have to deal with column names?

See this somewhat related recent conversation with @Wraith2

[mgravell]

@roji imagine the context of Dapper. Right now, in current versions of Dapper, it doesn't attempt to pre-process the SQL to understand context and check whether it can use pure ordinal fetch - it checks the column names (not least, because the query could involve *).

Now, if we parse the query fully, we can check that - and indeed that is planned for TSQL in AOT mode because we have a build-time TSQL parser already, that we can use. However:

• we don't have this capability for every SQL variant
• for runtime-only queries, we don't want to spend the time checking

So: having the ability to efficiently query the column names could save a considerable number of allocations.

[adamburgess]

Executing Stored Procedures would also benefit from this optimisation as the column orders are not directly controllable and must be looked up before reading the result.