Async/Await and Entity Framework SQL Procedure UI Thread Locking

asynchronous c# entity-framework-6 multithreading tsql

Question

I've been transitioning myself from BackGroundWorker to Async/Await programming. For the most part, it's been pretty simple. However, today I'm working with Entity Framework 6 and it's not playing nice. What I have here is a Procedure that I'm executing over a whole lot of entries. I want feedback to a progress bar since this is a lengthy process made up a somewhat lengthy processes.

This is the background worker; that doesn't thread lock the UI, I was using;

private void ButtonMRCUpdateAll(object sender, EventArgs e) {
    cmdMRCUpdateAllExcelSheets.Enabled = false;
    cmdMRCUpdateSingleClient.Enabled = false;
    tsStatusBar.Value = 0;
    tsStatusBar.Visible = true;
    var bw = new BackgroundWorker();
    bw.WorkerReportsProgress = true;
    bw.DoWork += delegate {
        DateTime YearAndMonth = new DateTime(dtpMRC.Value.Year, dtpMRC.Value.Month, 1);
        List<string> List = new List<string>();
        using (wotcDB DB = new wotcDB()) {
            var r = DB.client_main.
                Where(t => t.Active == true).
                OrderBy(t => t.CLIENTCODE).
                Select(t => t.CLIENTCODE);
            List.AddRange(r.ToArray());
        }
        bw.ReportProgress(0, List.Count);
        int PercentComplete = 0;
        foreach (var Client in List) {
            using (wotcDB DB = new wotcDB()) {
                DB.system_MRC(Client, YearAndMonth);
            }
            PercentComplete++;
            bw.ReportProgress(PercentComplete);
        }
    };
    bw.ProgressChanged += delegate (object s, ProgressChangedEventArgs ex) {
        if (ex.UserState != null) { tsStatusBar.Maximum = (int)ex.UserState; }
        tsStatusBar.Value = ex.ProgressPercentage;
    };
    bw.RunWorkerCompleted += delegate {
        MessageBox.Show("Monthly Results and Changes - Task Complete");
        cmdMRCUpdateAllExcelSheets.Enabled = true;
        cmdMRCUpdateSingleClient.Enabled = true;
        tsStatusBar.Visible = false;
        tsStatusBar.Value = 0;
    };
    bw.RunWorkerAsync();
}

This is the Async/Await that I'm trying to use. This thread locks the UI and I'm not sure why. I think it's the Entity Framework call since I've used foreach with other non EF functions and it's worked just fine.

private async void ButtonMRCUpdateAllExcelSheetsClick(object sender, EventArgs e) {
    await ProcessEntries();
}

private async Task ProcessEntries() {
    cmdMRCUpdateAllExcelSheets.Enabled = false;
    cmdMRCUpdateSingleClient.Enabled = false;
    tsStatusBar.Value = 0;
    tsStatusBar.Visible = true;

    DateTime YearAndMonth = new DateTime(dtpMRC.Value.Year, dtpMRC.Value.Month, 1);
    List<string> List = new List<string>();
    using (wotcDB DB = new wotcDB()) {
        var r = DB.client_main.
            Where(t => t.Active == true).
            OrderBy(t => t.CLIENTCODE).
            Select(t => t.CLIENTCODE);
        List.AddRange(await r.ToArrayAsync());
    }
    tsStatusBar.Maximum = List.Count;

    foreach (var Client in List) {
        await AsyncProcessEntry(Client, YearAndMonth);
        tsStatusBar.Value += 1;
    }

    cmdMRCUpdateAllExcelSheets.Enabled = true;
    cmdMRCUpdateSingleClient.Enabled = true;
    tsStatusBar.Visible = false;
    tsStatusBar.Value = 0;
    MessageBox.Show("Monthly Results and Changes - Task Complete");
}

private Task<string> AsyncProcessEntry(string Client, DateTime? YearAndMonth) {
    var tcs = new TaskCompletionSource<string>();
    using (wotcDB DB = new wotcDB()) {
       DB.system_MRC(Client, YearAndMonth);
    }
    tcs.SetResult("");
    return tcs.Task;
}

What am I doing incorrectly in the Async/Await that is causing a UI thread lock?

EDIT (system_MRC);

ALTER PROCEDURE [dbo].[system_MRC]
    -- Add the parameters for the stored procedure here
    @CLIENTCODE AS VARCHAR(16),
    @Date AS DATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    ~~ A whole lot of math stuff, then some inserts/updates based on if the entries exist already.

END
1
0
8/1/2018 1:41:09 PM

Popular Answer

I got it working. Since I'm using a Procedure I couldn't use .ConfigureAwait(false)

I did incorporate the suggestions from the comments to make the reader async. This is a small query so it doesn't benefit greatly from this, however I have other queries that will and I'm going to make this a part of standard operations going forward for all queries.

What I had to do to get it working correctly was wrap the procedure in an await Task.Run() and use it that way. This saved a lot of code and I managed to get back to a single function.

private async void ButtonMRCUpdateAllExcelSheetsClick(object sender, EventArgs e) {
    cmdMRCUpdateAllExcelSheets.Enabled = false;
    cmdMRCUpdateSingleClient.Enabled = false;
    tsStatusBar.Value = 0;
    tsStatusBar.Visible = true;
    DateTime YearAndMonth = new DateTime(dtpMRC.Value.Year, dtpMRC.Value.Month, 1);
    List<string> List = new List<string>();
    using (wotcDB DB = new wotcDB()) {
        var r = DB.client_main.
            Where(t => t.Active == true).
            OrderBy(t => t.CLIENTCODE).
            Select(t => t.CLIENTCODE);
        List.AddRange(await r.ToArrayAsync());
        tsStatusBar.Maximum = List.Count;
        foreach (var Client in List) {
            await Task.Run(() => {
                DB.system_MRC(Client, YearAndMonth);
            });
            tsStatusBar.Value++;
        }
    }
    cmdMRCUpdateAllExcelSheets.Enabled = true;
    cmdMRCUpdateSingleClient.Enabled = true;
    tsStatusBar.Visible = false;
    tsStatusBar.Value = 0;
    MessageBox.Show("Monthly Results and Changes - Task Complete");
}

Whether this is the correct way isn't clear. When using readers it definitely isn't.

0
8/1/2018 7:46:06 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow