Fill GridViews with Entity Framework Asynchronously

Question

I have a form containing 4 gridviews. I would like to fill these gridviews with objects I query thanks to entity Framework. The problem is that the UI gets stuck while they load. I thus tried to make the query asynchronous but it isn't working. I have tried to do it several ways but my current solution looks like this

        private async void SwiftCheck_Load(object sender, EventArgs e)
        {
            radGridViewLast7Days.DataSource = await MonitoringToolCore.SwiftQueries.ReturnLast7DaysAsync();
            radGridViewAmount.DataSource = await MonitoringToolCore.SwiftQueries.CompareAmountsAsync();
            radGridViewAmountBySwift.DataSource = await MonitoringToolCore.SwiftQueries.CompareAmountsByTypeAsync();
            radGridViewAmountBySender.DataSource = await MonitoringToolCore.SwiftQueries.CompareAmountsBySenderAsync();

            //Code to adapt the gridviews layout after data has been bound
            this.radGridViewAmountBySender.BestFitColumns();

        }

I bind the data in the load event because from my understanding I cannot use await in the constructor. I then use await on each task because Entity Framework isn't thread safe. So if I understood properly, I can't launch each task at the same time and then await them all ?

This is one of the codes that returns the list of objects

        public static async Task<List<MsMessage>> ReturnLast7DaysAsync()
        {
            DateTime currentDate = DateTime.UtcNow.Date.AddDays(-7);

            using (var db = new SveulumeContext())
            {

                var query = db.MsMessages
                    .Where(u => u.Created >= currentDate);

                return await query.ToListAsync();               

            }
        }

The code works and binds the gridViews properly but the UI still blocks during the whole process. What am I doing wrong ?

@iSpain17 I don't care that my users interact with the grids but the application does other things and I don't want it to block while the querying is going on. I tried using background workers but then I got the error "Control "" accessed from a thread other than the thread it was created on". So I guess the best solution would be to launch a background thread that does the querying, await its results and then bind the data in the main thread ? I guess my LINQ queries don't need to be Async anymore if I do that ? — Sacha Dieryck, Jun 19 '19 at 12:50

score 1 · Accepted Answer · answered Jun 19 '19 at 13:33

According to this answer, Oracle does not support asynchronous queries in its .NET library. So, the way you're doing asynchronous LINQ is correct; it's just that Oracle doesn't support it. This is why you're seeing the queries run synchronously rather than asynchronously.

To get the code off the UI thread, you can use Task.Run. I recommend also adding a comment to the code, since it looks wrong:

// Task.Run is necessary because Oracle does not support async: https://stackoverflow.com/questions/29016698/can-the-oracle-managed-driver-use-async-wait-properly/29034291#29034291
radGridViewLast7Days.DataSource = await Task.Run(() => MonitoringToolCore.SwiftQueries.ReturnLast7DaysAsync());

On a side note, EF does support multiple simultaneous queries if they each use their own db context. Since your methods do use their own db context, you can load all these queries simultaneously:

private async void SwiftCheck_Load(object sender, EventArgs e)
{
  // Task.Run is necessary because Oracle does not support async: https://stackoverflow.com/questions/29016698/can-the-oracle-managed-driver-use-async-

  var last7DaysTask = Task.Run(() => MonitoringToolCore.SwiftQueries.ReturnLast7DaysAsync());
  var compareAmountsTask = Task.Run(() => MonitoringToolCore.SwiftQueries.CompareAmountsAsync());
  var compareAmountsByTypeTask = Task.Run(() => MonitoringToolCore.SwiftQueries.CompareAmountsByTypeAsync());
  var compareAmountsBySenderTask = Task.Run(() => MonitoringToolCore.SwiftQueries.CompareAmountsBySenderAsync());
  await Task.WhenAll(last7DaysTask, compareAmountsTask, compareAmountsByTypeTask, compareAmountsBySenderTask);
  radGridViewLast7Days.DataSource = await last7DaysTask;
  radGridViewAmount.DataSource = await compareAmountsTask;
  radGridViewAmountBySwift.DataSource = await compareAmountsByTypeTask;
  radGridViewAmountBySender.DataSource = await compareAmountsBySenderTask;

  //Code to adapt the gridviews layout after data has been bound
  this.radGridViewAmountBySender.BestFitColumns();
}

The explanation is clear and I just tried the code and it works, thank you ! — Sacha Dieryck, Jun 19 '19 at 13:46

Fill GridViews with Entity Framework Asynchronously

1 Answers1