Silent Exception in QueryFirstOrDefaultAsync<T> / QueryFirstOrDefault (MS SQL)

[Scrittl]

We encountered an issue where an SQL error within a stored procedure did not result in an exception being thrown in C#. (MSSQL Server 2022)

I have condensed it into this query:

public void ExceptionTest()
{
    string sql = @"
		-- MOVE the 'SELECT 7;'-Statement here, to increase the required while(read()) calls by one.

                BEGIN TRANSACTION 
                BEGIN TRY
                    SELECT 7; --SELECT any value INSIDE TRANSACTION
                    
                    --FORCE Error
                    DECLARE @intvar INT
                    SET @intvar = 'A'
                COMMIT TRANSACTION
                END TRY
                BEGIN CATCH 
                    ROLLBACK TRANSACTION
                    ;THROW 50000, 'ERROR in Transaction', 1;
                END CATCH
                ";
    try
    {
        using (IDbConnection connection = new SqlConnection(connectionString))
        {
            var task = connection.QueryFirstOrDefaultAsync<int>(sql);
            task?.Wait();
            var result = task?.Result;
        }

        Console.WriteLine("Finished");
    }
    catch (Exception ex)
    {
        Console.WriteLine("Exception: " + ex);
    }
}

I would expect an exception to be thrown due to the SQL error in the script, which occurs after attempting to assign an incorrect datatype: SET @intvar = 'A'.
Instead, the selected value is returned without any errors or exceptions.

By modifying the method async Task<T> QueryRowAsync<T>(this IDbConnection cnn, Row row, Type effectiveType, CommandDefinition command) in SqlMapper.Async.cs, I successfully triggered the SQL exception.

Dapper/Dapper/SqlMapper.Async.cs

Line 505 in 2fa046c

while (await reader.NextResultAsync(cancel).ConfigureAwait(false)) { /* ignore result sets after the first */ }

//Add multiple lines of ReadAsync(..) in front of while (await reader.NextResultAsync(
while (await reader.ReadAsync(cancel).ConfigureAwait(false)) { /*  */ }
while (await reader.ReadAsync(cancel).ConfigureAwait(false)) { /* Exception thrown */ }
while (await reader.ReadAsync(cancel).ConfigureAwait(false)) { /* Exception thrown, when moving the single select in front of the transaction */ }

while (await reader.NextResultAsync(cancel).ConfigureAwait(false)) { /* ignore result sets after the first */ }

Of course, spamming read-calls is not an adequate fix, but it could serve as a starting point for a possible solution.

[mgravell]

That's very interesting. I thought we'd handled all these nuances, hence the loop, but if we need an inner loop too: I'm fine with it. I'll investigate, thanks for the report.

[dvdgutzmann]

#1834 seems to be related

[mgravell]

Findings:

• this only occurs with interesting CommandBehavior; .Default is not affected; .SingleResult, .SingleRow, or .SingleResult | .SingleRow all manifest identically
• no number of NextResult[Async]() helps - only Read[Async]()
• the number needed is query dependent

Running an arbitrary number of additional read operations is not viable - we'd have no way of picking the number. It seems likely that the only way to make this predictable would be to disable CommandBehavior optimizations, which is ... undesirable. I'm going to throw it up to the SqlClient folks to see what they think (this would only impact Microsoft.Data.SqlClient)

[mgravell]

I'm going to stay active with this on the SqlClient side, but this seems like a provider bug rather than a Dapper bug. The conversation is getting quite lively:)

Will reopen if that conversation fizzles without a conclusion.