Support Memory<byte>/ReadOnlyMemory<byte> as SqlParameter.Value for binary/varbinary/image parameters
- Dominant language
- C#
- Stars
- 989
- Forks
- 340
- Avg merge
- 4d 19h
- Merged PRs (30d)
- 72
Description
### Is your feature request related to a problem? Please describe.
`SqlParameter.Value` only accepts a handful of hardcoded CLR representations for
`Binary`/`VarBinary`/`Image` parameters. Tracing `SqlParameter.CoerceValue` (decompiled from
`Microsoft.Data.SqlClient` 6.0.2): when the destination `MetaType.ClassType` is `typeof(byte[])`,
the method special-cases exactly `byte[]`, `Stream` (via `StreamDataFeed`), `SqlBytes`, and
`SqlBinary`. Anything else falls through to the generic fallback:
```csharp
value = Convert.ChangeType(value, destinationType.ClassType, null);
```
`Memory` and `ReadOnlyMemory` don't implement `IConvertible`, so this throws
`InvalidCastException("Object must implement IConvertible.")`, which `ADP.ParameterConversionFailed`
re-wraps as:
```
InvalidCastException: Failed to convert parameter value from a ReadOnlyMemory`1 to a Byte[].
```
This forces any caller already holding binary data as `Memory`/`ReadOnlyMemory` — e.g.
a slice of a larger buffer, or a rented `ArrayPool` segment — to call `.ToArray()` first,
which is exactly the extra allocation + copy that `Memory` exists to avoid. There's effectively
no way to bind a byte-slice parameter today without either allocating a full new array or holding
the whole surrounding buffer as a full-size `byte[]` from the start.
The read side has the same shape of gap: `SqlDataReader` always materializes and returns binary
columns as a freshly allocated `byte[]` (via `GetValue`/`GetFieldValue`); there's no way to
read a `varbinary`/`binary`/`image` column directly into a caller-owned buffer without an extra copy.
### Describe the solution you'd like
**Write side:** recognize `Memory` and `ReadOnlyMemory` in `SqlParameter.CoerceValue`
(and the `MetaType`/`GetMetaTypeFromValue` dispatch) as additional carriers for
`Binary`/`VarBinary`/`Image`, alongside the existing `byte[]`/`Stream`/`SqlBytes`/`SqlBinary`
special-cases — mirroring how `Stream` is already handled instead of going through
`Convert.ChangeType`. (Note: `Span` is a `ref struct` and can never be boxed into an
`object`-typed property, so it structurally cannot participate in `SqlParameter.Value` — this
request is scoped to `Memory`/`ReadOnlyMemory` for the write side.)
**Read side:** add a `Span`-based fill API for binary columns (a `ref struct` parameter is
fine here, since it's a method argument, not a stored property) — something like:
```csharp
int SqlDataReader.GetBytes(int ordinal, long dataIndex, Span buffer);
```
analogous to `Stream.Read(Span)`, so a caller can read directly into a pooled/stack buffer
without forcing a fresh `byte[]` allocation per row.
### Describe alternatives you've considered
- **Calling `.ToArray()`/`.Span.ToArray()` before assigning `SqlParameter.Value`.** Works today,
but is exactly the per-call allocation + copy that holding data as `Memory` (from an
`ArrayPool` rental or a slice of a larger buffer) was meant to avoid.
- **Using `Stream` instead** (already supported via `StreamDataFeed`). Reasonable for genuinely
large values, but is heavyweight and async-oriented for small, already-in-memory buffers that
just happen to be `Memory` rather than `byte[]`.
- **Passing the underlying `byte[]` plus a separate offset/length pair.** Defeats the point of a
self-describing `Memory` slice and reintroduces manual bookkeeping the `Memory` API
exists to eliminate.
### Additional context
- A closely related request already exists for the SQLite provider:
[dotnet/efcore#37484](https://github.com/dotnet/efcore/issues/37484) — "Support Memory and
ReadOnlyMemory parameter binding in Microsoft.Data.Sqlite," motivated by the same
array-pool/slicing scenario ("what if the desired blob to bind is not at the beginning of the
array... you want to slice a byte array into multiple columns"). Parity across ADO.NET providers
would help code that targets more than one backend.
- Minimal repro of the current failure:
```csharp
byte[] backing = [1, 2, 3, 4, 5];
ReadOnlyMemory slice = backing.AsMemory(1, 3);
using var command = connection.CreateCommand();
command.CommandText = "INSERT INTO T (Col) VALUES (@p)";
command.Parameters.Add(new SqlParameter("@p", SqlDbType.VarBinary) { Value = slice });
command.ExecuteNonQuery(); // InvalidCastException: Failed to convert parameter value from a ReadOnlyMemory`1 to a Byte[].
```
- Even just the write-side change (`Memory`/`ReadOnlyMemory` as `SqlParameter.Value`)
would already remove the most common pain point; the read-side `Span` fill API is a
separate, larger ask and could be tracked independently if that's preferred.
Contributor guide
Research direction
Start by reading SqlParameter.CoerceValue and the MetaType/GetMetaTypeFromValue dispatch for the existing byte[]/Stream handling, then inspect SqlDataReader's GetBytes and GetFieldValue behavior. Done means the write-side Memory/ReadOnlyMemory request is supported without the reported conversion failure and the separate Span-based read API is evaluated or split into its own issue.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, sql
- Domain
- database
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 58/100