Why does this async action hang?

Yep, that's a deadlock all right. And a common mistake with the TPL, so don't feel bad.

When you write await foo, the runtime, by default, schedules the continuation of the function on the same SynchronizationContext that the method started on. In English, let's say you called your ExecuteAsync from the UI thread. Your query runs on the threadpool thread (because you called Task.Run), but you then await the result. This means that the runtime will schedule your "return result;" line to run back on the UI thread, rather than scheduling it back to the threadpool.

So how does this deadlock? Imagine you just have this code:

var task = dataSource.ExecuteAsync(_ => 42);
var result = task.Result;

So the first line kicks off the asynchronous work. The second line then blocks the UI thread. So when the runtime wants to run the "return result" line back on the UI thread, it can't do that until the Result completes. But of course, the Result can't be given until the return happens. Deadlock.

This illustrates a key rule of using the TPL: when you use .Result on a UI thread (or some other fancy sync context), you must be careful to ensure that nothing that Task is dependent upon is scheduled to the UI thread. Or else evilness happens.

So what do you do? Option #1 is use await everywhere, but as you said that's already not an option. Second option which is available for you is to simply stop using await. You can rewrite your two functions to:

public static Task<T> ExecuteAsync<T>(this OurDBConn dataSource, Func<OurDBConn, T> function)
{
    string connectionString = dataSource.ConnectionString;

    // Start the SQL and pass back to the caller until finished
    return Task.Run(
        () =>
        {
            // Copy the SQL connection so that we don't get two commands running at the same time on the same open connection
            using (var ds = new OurDBConn(connectionString))
            {
                return function(ds);
            }
        });
}

public static Task<ResultClass> GetTotalAsync( ... )
{
    return this.DBConnection.ExecuteAsync<ResultClass>(
        ds => ds.Execute("select slow running data into result"));
}

What's the difference? There's now no awaiting anywhere, so nothing being implicitly scheduled to the UI thread. For simple methods like these that have a single return, there's no point in doing an "var result = await...; return result" pattern; just remove the async modifier and pass the task object around directly. It's less overhead, if nothing else.

Option #3 is to specify that you don't want your awaits to schedule back to the UI thread, but just schedule to the thread pool. You do this with the ConfigureAwait method, like so:

public static async Task<ResultClass> GetTotalAsync( ... )
{
    var resultTask = this.DBConnection.ExecuteAsync<ResultClass>(
        ds => return ds.Execute("select slow running data into result");

    return await resultTask.ConfigureAwait(false);
}

Awaiting a task normally would schedule to the UI thread if you're on it; awaiting the result of ContinueAwait will ignore whatever context you are on, and always schedule to the threadpool. The downside of this is you have to sprinkle this everywhere in all functions your .Result depends on, because any missed .ConfigureAwait might be the cause of another deadlock.

This is the classic mixed-async deadlock scenario, as I describe on my blog. Jason described it well: by default, a "context" is saved at every await and used to continue the async method. This "context" is the current SynchronizationContext unless it it null, in which case it is the current TaskScheduler. When the async method attempts to continue, it first re-enters the captured "context" (in this case, an ASP.NET SynchronizationContext). The ASP.NET SynchronizationContext only permits one thread in the context at a time, and there is already a thread in the context - the thread blocked on Task.Result.

There are two guidelines that will avoid this deadlock:

1. Use async all the way down. You mention that you "can't" do this, but I'm not sure why not. ASP.NET MVC on .NET 4.5 can certainly support async actions, and it's not a difficult change to make.
2. Use ConfigureAwait(continueOnCapturedContext: false) as much as possible. This overrides the default behavior of resuming on the captured context.

I was in the same deadlock situation but in my case calling an async method from a sync method, what works for me was:

private static SiteMetadataCacheItem GetCachedItem()
{
      TenantService TS = new TenantService(); // my service datacontext
      var CachedItem = Task.Run(async ()=> 
               await TS.GetTenantDataAsync(TenantIdValue)
      ).Result; // dont deadlock anymore
}

is this a good approach, any idea?