QueryAsync<T> Does Not Ever Return If Zero Results

[houseofcat]

It works great when rows are returned but if no data was found... ...scope never comes back from await.

Did a SQL Profile to a localdb, verifying storedproc hits, validate the call works, returns 0 results instantly manually executed, but Dapper never comes back from QueryAsync. No SQLClient, SQLType, SQL Other Exceptions occur.

Framework 4.6.2 Azure SQL (T-SQL) Dapper Nuget 1.50.2

Sample Call That Works:

using( var connection = new SqlConnection( ConnectionString ) )
{
    var result = connection.Query<T>(
        $"[{SchemaString}].[{ObjectType}_{sprocSuffix}]",
        TrimObject( value ),
        commandType: CommandType.StoredProcedure );

    return result.SingleOrDefault();
}

Sample Call That Does Not Work:

using( var connection = new SqlConnection( ConnectionString ) )
{
    var result = await connection.QueryAsync<TId>(
        $"[{SchemaString}].[{ObjectType}_{sprocSuffix}]",
        TrimObject( obj ),
        commandType: CommandType.StoredProcedure );

    return result.SingleOrDefault();
}

[celalit]

I faced the same issue, but in my case I was calling QueryAsync<> from a syncronised Asp.Net MVC controller. Once, I added 'async' keyword to the Asp.Net MVC controller and 'await' (awaited) the dapper call, it worked fine.

[houseofcat]

@celalit Appreciate the response but that isn't what's happening here. In your situation, you were exiting the scope of your MVC controller before the call had a chance to return. In this particular instance, I run into a situation of code frozen forever at an await condition.

To maintain our internal async-await practice we have just wrapped the Dappery.Query in an an awaitable task pending the proper solution of being able to utilize QueryAsync.

        var t = await Task.Run(() =>
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                var result = connection.Query<TId>(
                    $"[{SchemaString}].[{ObjectType}_{sprocSuffix}]",
                    TrimObject(obj),
                    commandType: CommandType.StoredProcedure);

                return result.SingleOrDefault();
            }
        });

        return t;

[celalit]

I see.

[houseofcat]

@NickCraver Any news?

[eXon]

This is a huge problem for us as well. The thread is freezing when a QueryAsync has no row.

By the way, awesome library I love it =)

[007imash]

does anyone know when the solution to this issue will be released? I am still experiencing this issue even on the latest pre-release version.

[0xfeeddeadbeef]

I canʼt reproduce this bug on .NET 4.7.1 (Dapper 1.50.2, SQL Server LocalDB 2016).

[houseofcat]

Just performed some functional tests locally at new job/position.

Unable to reproduce this bug on NetCore 2.x, Dapper 1.50.2 SQL Server 2008 (Non-Azure, Remote Server, DB Compatibility 100)

SET NOCOUNT ON was set in the Stored Procedure. Then it was tested with it off.

Task.Run(() => AsyncTimeout_Tests.Test_AsyncTimeout_NoResults(configuration.GetValue<string>("ConnectionStrings:TCS")));
Task.Run(() => AsyncTimeout_Tests.Test_AsyncTimeout_NoResults_WithMapping<Control>(configuration.GetValue<string>("ConnectionStrings:TCS")));

Test 1:

public static async Task<dynamic> Test_AsyncTimeout_NoResults(string connectionString) {
    using (var connection = new SqlConnection(connectionString))
    {
       Console.WriteLine("Starting Test_AsyncTimeOut_NoResults");
       var result = await connection.QueryAsync("[dbo].[SELECT]", new { id = -1 }, commandType: CommandType.StoredProcedure);
       Console.WriteLine(result.Count());
       Console.WriteLine("Done");
       return result.FirstOrDefault();
    }
}

Test 2:

public static async Task<T> Test_AsyncTimeout_NoResults_Mapping<T>(string connectionString) {
    using (var connection = new SqlConnection(connectionString))
    {
       Console.WriteLine("Starting Test_AsyncTimeOut_NoResults_Mapping");
       var result = await connection.QueryAsync<T>("[dbo].[SELECT]", new { id = -1 }, commandType: CommandType.StoredProcedure);
       Console.WriteLine(result);
       Console.WriteLine("Done");
       return result.FirstOrDefault();
    }
}

[NickCraver]

This is fixed on MyGet, but we need to push a NuGet update. I'll try to make it happen this week - we've just been slammed with the launch of Teams and GDPR at Stack Overflow. Hopefully a lot of items off the TODO lists in the coming weeks.

[NickCraver]

NuGet is now updated (and I've added MyGet feed data to the Dapper README). Thanks for the help here!

[HBOlsen]

@NickCraver I'm having similar problems as described in this issue. What version of Dapper has this fix?

[vdarshan82]

On MyGet it shows the version 2.0.51. And its for the .Net Standard Version as per dependencies shown. Does it mean it not implemented for .Net Core 3.0.1. Can you confirm whether it will work ?

Also, Can we expect updated version 2.0.51 to be available on nuget soon as it still has v2.0.35 which is old.

[HasibAhmed577]

Using Dapper 2.1.35 from .Net Framework 4.8.1, with Fsharp.Core 8.0.400. This issue is still present. The Query function also fails when no data is returned.