SQL Server sql_variant mapping regression in 2.1.86: SERVERPROPERTY Int32 to bool throws InvalidCastException

Check your library version, and try updating

Dapper 2.1.86 reproduces the issue.

Dapper 2.1.79 does not reproduce the issue with the same code, SQL Server, and ADO.NET provider.

Describe the bug

When using Dapper 2.1.86 with Microsoft.Data.SqlClient, mapping a value returned by SQL Server's SERVERPROPERTY() function to a bool property throws an InvalidCastException.

SERVERPROPERTY() returns sql_variant. In this case the underlying value is an Int32 (0 or 1). The same mapping works correctly with Dapper 2.1.79.

Explicitly casting the SERVERPROPERTY() result to either int or bit also works with Dapper 2.1.86.

To Reproduce

Model:

public sealed class TestRow { public bool IsLocalDB { get; set; } }

Using Microsoft.Data.SqlClient:

using Dapper;
using Microsoft.Data.SqlClient;

using var connection = new SqlConnection(connectionString);

// Works in Dapper 2.1.86
var result1 = connection.QuerySingle<TestRow>(
    "SELECT CAST(0 AS int) AS IsLocalDB");

// Fails in Dapper 2.1.86
var result2 = connection.QuerySingle<TestRow>(
    "SELECT SERVERPROPERTY('IsLocalDB') AS IsLocalDB");

// Works in Dapper 2.1.86
var result3 = connection.QuerySingle<TestRow>(
    "SELECT CAST(SERVERPROPERTY('IsLocalDB') AS int) AS IsLocalDB");

// Works in Dapper 2.1.86
var result4 = connection.QuerySingle<TestRow>(
    "SELECT CAST(SERVERPROPERTY('IsLocalDB') AS bit) AS IsLocalDB");

The second query fails with:

System.Data.DataException:
Error parsing column 0 (IsLocalDB=0 - Int32)

Inner exception:
System.InvalidCastException:
Specified cast is not valid.

Changing only the Dapper version from 2.1.86 to 2.1.79 causes the same SERVERPROPERTY() query to work correctly.

Expected and actual behavior

Expected:

The result of SERVERPROPERTY('IsLocalDB') should map to the bool IsLocalDB property as it did in Dapper 2.1.79.

Actual:

Dapper 2.1.86 throws:

Error parsing column 0 (IsLocalDB=0 - Int32)
Specified cast is not valid.

Explicitly casting the sql_variant result first avoids the exception:

CAST(SERVERPROPERTY('IsLocalDB') AS int)

-- or

CAST(SERVERPROPERTY('IsLocalDB') AS bit)

Additional context

Dapper 2.1.79: works
Dapper 2.1.86: fails
Database: Microsoft SQL Server 2022, version 16.0.1200.5
ADO.NET provider: Microsoft.Data.SqlClient

The failure appears specific to the raw sql_variant returned by SERVERPROPERTY(), rather than general Int32-to-bool conversion.

Possibly related to #2231, which reports another mapping regression between 2.1.79 and 2.1.86, although that issue involves PostgreSQL/Npgsql and a different data type.

Start by reproducing the failure through QuerySingle with the raw SERVERPROPERTY('IsLocalDB') query, then compare the behavior with Dapper 2.1.79 and 2.1.86. Trace the mapping path for sql_variant values returned by Microsoft.Data.SqlClient and add coverage for this case. Done means the raw query maps Int32 0 or 1 to bool without an exception while the existing explicit casts continue to work.