`SqlDataReader.GetFieldValue<byte[]>()` cannot be used to read a native SQL Server `JSON` column.

[KyMback]

Describe the bug

SqlDataReader.GetFieldValue<byte[]>() cannot be used to read a native SQL Server JSON column.

The call throws:

Unhandled exception. System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Byte[]'.
   at Microsoft.Data.SqlClient.SqlDataReader.GetFieldValueFromSqlBufferInternal[T](SqlBuffer data, _SqlMetaData metaData, Boolean isAsync)
   at Microsoft.Data.SqlClient.SqlDataReader.GetFieldValueInternal[T](Int32 i, Boolean isAsync)
   at Microsoft.Data.SqlClient.SqlDataReader.GetFieldValue[T](Int32 i)

To reproduce

1. Create a SQL Server 2025 database with a table containing:

   • Id (UNIQUEIDENTIFIER)
   • JsonCol (JSON)

2. Insert a row containing valid JSON.

3. Execute the following code:

#:package Microsoft.Data.SqlClient@7.1.0
#:property PublishAot=false
#:property PublishTrimmed=false

using Microsoft.Data.SqlClient;

var connectionString = "";

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

await using var cmd = connection.CreateCommand();
cmd.CommandText = "SELECT Id, JsonCol FROM Test1";

await using var reader = await cmd.ExecuteReaderAsync();

await reader.ReadAsync();

var id = reader.GetGuid(0);
var json = reader.GetFieldValue<byte[]>(1);

The call to GetFieldValue<byte[]>(1) throws InvalidCastException, because SqlClient appears to have materialized the JSON value as System.String.

Expected behavior

SqlDataReader.GetFieldValue<byte[]>() should support native SQL Server JSON columns and return the JSON payload as UTF-8 bytes.

This appears to be the intended API behavior described in the SqlClient JSON datatype design:

#2622

The design explicitly lists:

SqlDataReader.GetFieldValue<byte[]>(int colOrdinal)

and states that the returned byte[] should be usable by Utf8JsonReader as the high-performance scenario in .NET.

See:

https://learn.microsoft.com/en-us/sql/t-sql/data-types/json-data-type?view=sql-server-ver17
https://learn.microsoft.com/en-us/openspecs/windows_protocols/ms-tds/afc7c81b-f931-4ef2-bfeb-d3761072fc4c
https://www.rfc-editor.org/info/rfc8259/

Therefore, an application should be able to consume the JSON payload as UTF-8 bytes without first materializing it as a UTF-16 System.String.

Further technical details

• Microsoft.Data.SqlClient: 7.1.0
• .NET SDK/runtime: 10.0.112
• SQL Server: 2025
• OS: Windows 11

[github-actions]

🔍 Triage Summary

Check	Result
Issue type	Bug
Environment	All required environment details provided for investigation
Area	Area\Json
Duplicates	None found (related but not duplicate: #1208 "UTF8 Support via GetBytes or GetFieldValue", and the closed design tracking issue #2622 which specifies this exact API behavior as intended)
Regression	Not indicated (JSON support is new in 7.x; this is a missing-feature gap rather than a regression)

Analysis

SqlDataReader.GetFieldValue<T>() cannot materialize a native SQL Server JSON column as byte[]. Code inspection of GetFieldValueFromSqlBufferInternal<T> in SqlDataReader.cs confirms a special case exists for typeof(T) == typeof(JsonDocument) (checking metaType.SqlDbType != SqlDbTypeExtensions.Json), but there is no equivalent case for typeof(T) == typeof(byte[]). As a result, a byte[] request falls through to the generic conversion path, which attempts to cast the internally-stored System.String JSON payload directly to byte[] and throws InvalidCastException. This directly contradicts the documented design intent in #2622, which explicitly states GetFieldValue<byte[]> should return UTF-8 bytes usable by Utf8JsonReader. Severity: P2 (missing documented API surface for a released feature; workaround exists via GetString/GetSqlJson plus manual UTF-8 encoding, but blocks the intended high-performance JSON consumption path).

Next Steps

• Assign this issue to Copilot coding agent to investigate SqlDataReader.GetFieldValueFromSqlBufferInternal<T> (around line 2842 in SqlDataReader.cs) and add a typeof(T) == typeof(byte[]) branch that checks metaType.SqlDbType == SqlDbTypeExtensions.Json and returns the UTF-8 encoded bytes of the JSON payload, mirroring the existing JsonDocument case. The corresponding async path (GetFieldValueAsync) should be checked for parity per the async/sync coverage guidance in the repo's testing instructions.
• Add unit/functional tests validating GetFieldValue<byte[]> and GetFieldValueAsync<byte[]> against SqlDbType.Json columns, plus a manual (integration) test against SQL Server 2025.
• Review JSON datatype support in SqlClient #2622's original API design list to confirm no other listed JSON APIs (e.g. GetStream() for JSON) have similar gaps.

Note: This triage summary is auto-generated by an AI agent. The analysis and suggestions above have not been verified by a human maintainer. Please treat as preliminary guidance only.

Generated by SqlClient Issue Auto-Triage for #4758 · copilot · auto · 41.5 AIC · ⌖ 12.6 AIC · ⊞ 11.8K · ◷

[KyMback]

My pain point is that 40% of all memory allocations in my use cases are these strings. The GC spends an enormous amount of CPU time cleaning them up, so I want to reduce this number as much as possible.

I investigated EF Core and SqlClient but couldn't find any other way to avoid these allocations. If you can suggest anything (apart from redesigning the database), it would be really helpful.

Thank you