Dapper: MSSQL Linux - ExecuteAsync does not work but Execute does

[jspinella]

I have some repositories in a .NET Standard 2.1 class library that were working fine with MySQL. Now that I have moved over to Microsoft SQL Server for Linux (hosted on Ubuntu 20.04) I am finding that SQL code sent via ExecuteAsync does not actually reach the MS SQL server. Other methods, like Dapper Plus's BulkInsert, and even SELECTs sent via QueryAsync work just fine.

To test this, I replaced the SQL code with "hello" and did not get an exception. I did get an exception trying to execute "hello" with Execute. Ergo, this seems to be a Dapper issue rather than a SQL Server issue. I have used Dapper plenty with SQL Server hosted on Windows and have never encountered this, so I suspect this is only reproducible with a Linux-hosted SQL Server (especially considering how new SQL Server for Linux is).

This does not throw an exception or get executed on the SQL Server:

public Task Reset(int id)  
{  
    var sql = @"DELETE FROM Customers WHERE ID= @id";  
    using (var connection = new SqlConnection(connString))  
        return connection.ExecuteAsync(sql, new { id});  
}

This does not throw an exception either:

public Task Reset(int id)
{
    var sql = @"hello!";
    using (var connection = new SqlConnection(connString))
        return connection.ExecuteAsync(sql, new { id});
}

This DOES work (and an exception is thrown if the sql string is "hello!":

public int Reset(int id)
{
    var sql = @"DELETE FROM Customers WHERE ID= @id";
    using (var connection = new SqlConnection(connString))
            return connection.Execute(sql, new { id });
}

[mgravell]

Interesting. What application type is this (not the library - the thing running it)? I'm wondering if this is a sync-context issue and a missing ConfigureAwait(false) - in which case, it should be simple to fix.

Other methods, like Dapper Plus's BulkInsert

Note: Dapper Plus is nothing to do with us at all; it is not affiliated, associated, related, etc.

[jspinella]

Not a single line of code is shared between Dapper and DapperPlus? I figured DapperPlus at least built on top of Dapper under the hood. I wonder why they used the name "Dapper". Then again I suppose it's better than something like "HtmlAgilityPack"- maybe it is best that they "borrow" product names 😁

The app that calls this is a .NET Core 3.1 Console App. Doesn't ConfigureAwait(false) prevent deadlocks? The particular Reset method I am executing should take several seconds at least (that's how long it takes in SSMS) but execution through the Reset method takes no time at all.. Seems like something like Wait() would make more sense.

I will point the application to SQL Server on Windows 10 this evening to see if it really is something with Linux/SQL Server for Linux.

[jspinella]

This behavior is being caused by the particular Reset method behind behind another Reset method for a different (but unrelated) table. They are both the exact same code-wise except for different tables (DELETE from tableB instead of Customers). Neither's SQL query is executed on SQL Server.

If I comment the first one out, the Reset in my initial post executes as expected. I did not have this issue with MySQL. It does seem that this might be a "gotcha" with SQL Server rather than a Dapper bug specific to SQL Server.

Same behavior on SQL Server on Windows so it is not an issue with SQL Server for Linux specifically, at least.

private void ResetDatabase(int countyId) { repoA.Reset(anotherId); repoB.Reset(id); }

I know this isn't "great" but it is dumbed down for testing purposes here.

There are actually six Reset methods each for a different table, and they were in a List that was Task.WhenAll'd on, but the results are the same either way- none are executed in SQL Server.

The only way I have been able to get ExecuteAsync to work is when there is just one being called... I guess within the same method? Not sure if there's some kind of "waiting period" where I can execute the second without issue.

[mgravell]

Not a single line of code is shared between Dapper and DapperPlus?

I believe that they take a package dependency.

maybe it is best that they "borrow" product names 😁

In this case I would disagree.

The app that calls this is a .NET Core 3.1 Console App.

OK, so no sync-context, so not what I thought

Doesn't ConfigureAwait(false) prevent deadlocks?

Yes and no. That isn't the intent, but if code is written badly (using "sync-over-async"), then it can make that bad code work. But it is still bad code.

The particular Reset method I am executing should take several seconds at least (that's how long it takes in SSMS) but execution through the Reset method takes no time at all.. Seems like something like Wait() would make more sense.

This is all.very curious and interesting. If you have a standalone repro, I'd be interested.

[jspinella]

Here's a test repo. Very possible I am doing something dumb causing this issue.

https://github.com/jspinella/DapperTest

[bgrainger]

var sql = @"DELETE FROM Customers WHERE ID= @id";
using (var connection = new SqlConnection(connString))
    return connection.ExecuteAsync(sql, new { id });

This code is buggy: it closes the connection (via the using statement) before ExecuteAsync has finished running. You need to await the results of ExecuteAsync before disposing the connection:

public **async** Task ResetAsync(int id)
{
    var sql = @"DELETE FROM Customers WHERE ID= @id";
    using (var connection = new SqlConnection(connString))
        return **await** connection.ExecuteAsync(sql, new { id }); }
}

[bgrainger]

I did not have this issue with MySQL.

MySql.Data's "async" methods are completely synchronous: https://bugs.mysql.com/bug.php?id=70111

You need to switch to an alternative library, such as MySqlConnector (disclosure: I'm a project maintainer), to get proper async I/O with MySQL.

[jspinella]

Fair enough. I will get you guys a better example tonight... the issue is not because I am using ExecuteAsync within a synchronous method.

That MySQL fun fact explains a lot.

[jspinella]

Apologies for the delay. I just pushed an update to the sample project that uses async-await. As before, ExecuteAsync is not executing SQL on SQL Server/seems to not be awaited.

[NickCraver]

@jspinella What is the sample project doing vs. expectations? Since code doesn't match issue any longer and all reset code is commented out, it's not clear what your intentions or expectations are - so clarification would help!

[jspinella]

I haven't really had the opportunity to look into this deeply. I think we close this for now because in all likelihood if this were an issue I wouldn't be the first or only person reporting it. It seems more likely that the issue @bgrainger mentioned about the official MySql connector library not actually supporting async is to blame.