c# - Blocking behaviour with Entity Framework Async methods and SQL Server Compact

Question

I have an MVVM app that calls a data service to get some data to bind. The data service accesses a SQL Server Compact (v4.0) database through Entity Framework 6.

The data (currently) takes a few seconds to be loaded and when called synchronously this (not surprisingly) blocks the GUI thread. Most of this I had assumed to be IO bound so I added an equivalent Async method to execute the data load in an asynchronous fashion to allow the GUI to remain responsive. However when I make use of the EF Async methods, it doesn't appear to make any difference, the GUI thread still blocks, for roughly the same amount of time.

I understand that using the Async calls will not move the work to a different thread, however I had assumed the majority of this activity to be IO bound. Hence using the EF Async methods should allowing the calling (GUI) thread to continue doing other work, but it is not making any difference.

For example if I write the LoadData method to use the EF synchronous Load method, then the GUI blocks until the data is loaded:

public ObservableCollection<Data> GetData()
{
    //Do IO bound activity...
    Context.DataTable1.Include(...).Load();

    //for demo purposes, just return some data
    return Context.DataTable1.Local; //(ObservableCollection<Data>)
}

The Async method to load the data looks like this:

public async Task<ObservableCollection<Data>> GetDataAsync()
{
    //Do IO bound activity...
    var task = Context.DataTable1.Include(...).LoadAsync();
    await task;

    //for demo purposes, just return some data
    return Context.DataTable1.Local; //(ObservableCollection<Data>)
}

Surprisingly (for me) I get the same result and it blocks the calling thread for roughly the same length of time (I put a stopwatch on it).

I started thinking that in addition to the database IO bound work, there may be some minimum amount of CPU bound activity that is causing the blocking. So I finally tried executing the work on a background thread by using Task.Run():

public async Task<ObservableCollection<Data>> GetDataAsync()
{
    //Do IO bound activity...
    Task<ObservableCollection<Competition>> task = Task.Run(async () => 
    { 
        //Do IO bound activity...
        var task = Context.DataTable1.Include(...).LoadAsync();
        await task;

        //for demo purposes, just return some data
        return Context.DataTable1.Local; //(ObservableCollection<Data>)
    });
    var result = await task;
    return result;
}

With this, the GUI obviously doesn't block and is responsive the entire time.

I've read many articles all around this, including posts here and here and blog posts by Stephen Cleary about when not to (here) and when to (here) use Task.Run. I understand that my last example above is still blocking, it is just blocking a threadpool thread. What I don't understand really is, why when accessing the EF async methods does it not appear to be providing any benefit?

Could it be that whilst there is IO activity going on, there is also sufficient CPU bound work to cause the blocking? It is certainly taking significantly longer than 50ms to respond, closer to 2 or 3 seconds to run all the queries, so this type of activity can start justifying the use of the Task.Run?

Or, have I written the Async method incorrectly, hence why it is still blocking?

I also wondered if perhaps the Async methods for the SQL Compact provider for EF were for some reason falling back to the standard synchronous calls?

Answer 1

The SQL Server Compact ADO.NET provider does not provide any Async APIs. But loading data should never take a fewcseconds, unless you are fetching 1000s of rows. Initialize a dbContext.connection object when you start your app, and leave empty and unused for the lifetime of your app. There is also a cost of init of dbContext first time.