Entity Framework Core + SQlite. Async requests are actually synchronous

Question

I have WPF program and I am trying to use EF Core with SQLite there and I found strange behaviour. Even if I call async method like ToArrayAsync() or SaveChangesAsync() it returns already completed task. So it means that operation was actually done synchronously.

It seems that there should be some flag in EF or SQLite connection which control sync/async execution but I didn't find it.

I used this code for tests:

using (var context = new TestDbContext())
{
    //I have about 10000 records here.
    var task = context.Users.ToListAsync();
    if (task.IsCompleted && task.Result != null)
    {
        // It is always comes here.
    }
    await task;
}

Answer 1

That's because SQLite implementations of ADO.NET classes (DbConnection, DbCommand) are synchronous. Parent classes provide Async methods that are really synchronous, and it's a job of provider to provide better implementation. For example, here is implementatation of DbConnection.OpenAsync:

public virtual Task OpenAsync(CancellationToken cancellationToken)
{
  TaskCompletionSource<object> completionSource = new TaskCompletionSource<object>();
  if (cancellationToken.IsCancellationRequested)
  {
    completionSource.SetCanceled();
  }
  else
  {
    try
    {
      this.Open();
      completionSource.SetResult((object) null);
    }
    catch (Exception ex)
    {
      completionSource.SetException(ex);
    }
  }
  return (Task) completionSource.Task;
}

As you see, there is nothing asynchronous whatsover, and returned task is always completed.

The same goes for all default Async implementations in DbCommand: they all either use TaskCompletionSource or directly Task.FromResult.

SQLiteCommand does not override that behavior, and when it does - it says explicitly in comments to the methods that asynchronous execution is not supported. For example, here is implementation (overriden) of ExecuteReaderAsync:

/// <summary>
/// Executes the <see cref="P:Microsoft.Data.Sqlite.SqliteCommand.CommandText" /> asynchronously against the database and returns a data reader.
/// </summary>
/// <param name="behavior">A description of query's results and its effect on the database.</param>
/// <param name="cancellationToken">The token to monitor for cancellation requests.</param>
/// <returns>A task representing the asynchronous operation.</returns>
/// <remarks>
/// SQLite does not support asynchronous execution. Use write-ahead logging instead.
/// </remarks>
/// <seealso href="http://sqlite.org/wal.html">Write-Ahead Logging</seealso>
public virtual Task<SqliteDataReader> ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
{
  cancellationToken.ThrowIfCancellationRequested();
  return Task.FromResult<SqliteDataReader>(this.ExecuteReader(behavior));
}

By contrast - SqlConnection and SqlCommand classes do override default (synchornous) behavior and provide really asynchronous implementations of methods like OpenAsync or ExecuteReaderAsync, so with sql server provider you should not have the behavior you observe.

So the behavior you observe is expected and not buggy when using SQLite.

Since you are using this in WPF application - that would mean that despite using async\await you UI thread will be blocked for the duration of the whole opration. So best thing to do in this case is not not use async versions at all and dispatch whole thing to the background thread via Task.Run or similar construct.