Need to tackle a Linq-2-SQL heavy duty response in a Background Worker

Question

I'm in a bit of a dilemma here. I have this BackgroundWorker enabled setup:

    #region Locum List
    private Object DoWorkMain_LocumList() {
        #region Query
        var locums = from locum in DbContext.Locums
                                 where
                                    locum.IsActive == true &&
                                    locum.IsAdminMarkedComplete == true &&
                                    locum.IsLocumsExciteBan == false &&
                                    locum.IsGPHCBan == false &&
                                    locum.LocumWorkingPreferenceID == 1
                                 select new {
                                     LocumID = locum.OID,
                                     LocumName = locum.FirstName + " " + locum.LastName,
                                     locum.MobileNumber,
                                     locum.Email,
                                     Gender = locum.Gender ? "Male" : "Female",
                                     locum.DateofBirth,
                                     LocumType = locum.LocumType.Name,
                                     Distance = DbContext.GetDistanceFromCache(TextAddressPostCode.Text.Trim(), locum.AddressInfo.Postcode),
                                     Address = String.Format("{0} {1} {2} {3}",
                                                            locum.AddressInfo.House.Length == 0 ? String.Empty : locum.AddressInfo.House + ", ",
                                                            locum.AddressInfo.Street.Length == 0 ? String.Empty : locum.AddressInfo.Street + ", ",
                                                            locum.AddressInfo.Area.Length == 0 ? String.Empty : locum.AddressInfo.Area + ", ",
                                                            locum.AddressInfo.Postcode ?? String.Empty),
                                     Postcode = locum.AddressInfo.Postcode,
                                     City = locum.AddressInfo.City.Name,
                                     County = locum.AddressInfo.City.County.Name,
                                     locum.SystemUserID,
                                     Status = DbContext.GetJobPermanentProcessLatestStatus(VaccanyID, locum.OID)
                                 };
        #endregion

        DataTable LocumListX = new DataTable("LocumList");
        DataColumn PrimaryColumn = LocumListX.Columns.Add("LocumID", typeof(Int64));
        LocumListX.Columns.Add("LocumName", typeof(String));
        LocumListX.Columns.Add("MobileNumber", typeof(String));
        LocumListX.Columns.Add("Email", typeof(String));
        LocumListX.Columns.Add("Gender", typeof(String));
        LocumListX.Columns.Add("DateofBirth", typeof(DateTime));
        LocumListX.Columns.Add("LocumType", typeof(String));
        LocumListX.Columns.Add("Distance", typeof(Decimal));
        LocumListX.Columns.Add("Address", typeof(String));
        LocumListX.Columns.Add("Postcode", typeof(String));
        LocumListX.Columns.Add("City", typeof(String));
        LocumListX.Columns.Add("County", typeof(String));
        LocumListX.Columns.Add("SystemUserID", typeof(Int64));
        LocumListX.Columns.Add("Status", typeof(String));

        LocumListX.PrimaryKey = new DataColumn[] { PrimaryColumn };

        int iCurrentRowIndex = 0;

        #region DataTable
        int LocumListXRowsCount = locums.Count();

        foreach (var item in locums) {
            DataRow newRow = LocumListX.NewRow();

            newRow["LocumID"] = item.LocumID;
            newRow["LocumName"] = item.LocumName;
            newRow["MobileNumber"] = item.MobileNumber;
            newRow["Email"] = item.Email;
            newRow["Gender"] = item.Gender;
            newRow["DateofBirth"] = item.DateofBirth;
            newRow["LocumType"] = item.LocumType;
            newRow["Distance"] = item.Distance;
            newRow["Address"] = item.Address;
            newRow["Postcode"] = item.Postcode;
            newRow["City"] = item.City;
            newRow["County"] = item.County;
            newRow["SystemUserID"] = item.SystemUserID;
            newRow["Status"] = item.Status;

            LocumListX.Rows.Add(newRow);

            iCurrentRowIndex++;
            BackgroundWorkerLocumList.ReportProgress((int)(iCurrentRowIndex * 100F / (LocumListXRowsCount - 1)));
        }
        #endregion

        LocumListXRowsCount = LocumListX.Rows.Count;
        iCurrentRowIndex = 0;

        foreach (DataRow Row in LocumListX.Rows) {
            if (Convert.ToDecimal(Row["Distance"]) >= 0) {
                iCurrentRowIndex++;
                continue;
            }

            Row["Distance"] = GetDistanceBetween(Row);

            iCurrentRowIndex++;
            BackgroundWorkerLocumList.ReportProgress((int)(iCurrentRowIndex * 100F / (LocumListXRowsCount - 1)));

            if (BackgroundWorkerLocumList.CancellationPending) {
                return null;
            }
        }

        return LocumListX;
    }

    private void BackgroundWorkerLocumList_DoWork(object sender, DoWorkEventArgs e) {
        BackgroundWorker backgroundWorkerLocumList = sender as BackgroundWorker;

        try {
            if (backgroundWorkerLocumList != null) {
                backgroundWorkerLocumList.ReportProgress(0);

                if (backgroundWorkerLocumList.CancellationPending) {
                    e.Cancel = true;
                    return;
                }

                e.Result = DoWorkMain_LocumList();
            }
        }
        catch (Exception ex) {
            e.Result = ex;
        }
    }

    private void BackgroundWorkerLocumList_ProgressChanged(object sender, ProgressChangedEventArgs e) {
        ProgressBarLocumList.EditValue = e.ProgressPercentage;
    }

    private void BackgroundWorkerLocumList_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e) {
        LoadLocumList_ResetProgress();

        if (e.Cancelled) {
            XtraMessageBox.Show("The task has been canceled");
        }
        else if (e.Error != null) {
            FormHelpers.ShowErrorMessageBox("Error while looking up distances.", Text, e.Error);
        }
        else {
            #region Grid
            GridLocumList.DataSource = e.Result as DataTable;
            LoadLocumListGridSetup();
            #endregion
        }

        ButtonRefreshLocumList.Enabled = true;
    }
    #endregion

What happens is that the original LINQ is suppose to return 1000 to 1500 records. So, when at a later stage, I do a For Each to convert the LINQ to a DataTable (foreach (var item in locums)), I face serious pauses and soon, the thread just dies without any kind of warning. I'm manually converting the the LINQ to DataTable because i used the MoreLinq extension method before but that also took very long with absolutly no progress reporting and my client dont likes.

I have two ideas here and the third thing is a severe headache.

One: If I can enumerate the result with a where clause. the unique column is LocumID. If I can create a List of all the LocumIDs form the 1000-1500 results first, and then use For Each on the List LocumIDs so that I get to deal with one LINQ row at a time to build my DataTable.

Two: Implement .Skip() and .Take() to deal with the data in the form of chunks of 50.

Any suggestions?

Regards.

Answer 1

Because multithreaded applications should not be taken lightly, I will just give an outline of how I would think about this problem and avoid using code when concurrency issues surface.

Basically, I would start with a collection (an ObservableCollection would be handy) to hold the actual collection of objects that your grid is going to hold.

Then, in a worker thread, I would start your loop to load data. Using .Skip()/.Take() with an indexer to keep track of your current page:

...

const int PageSize = 50;  // Or whatever you find works best, of course
int CurrentPage = 0;
int ReturnedCount = 0;

do
{
    var ReturnedData = GetNextSetOfData (CurrentPage, PageSize);
    ReturnedCount = ReturnedData.Count ();

    StoreReturnedData (ReturnedData);

    ++CurrentPage;
}
while (ReturnedCount > 0)

...

object GetNextSetOfData (int Page, int PageSize)
{
    var MyQuery = // Place your LINQ query here and add
                  // .Skip (Page * PageSize).Take (PageSize)
                  // and return the result;

    return MyQuery;
}

...

void StoreReturnedData (object Data)
{
    // Append your data to the end of the ObservableCollection (or whatever you're using)
    // here.  Be sure to use locks where you need to and all the tools to communicate
    // concurrently with your program
}

If you go the observable route, you can just have an even handle the update, grab the objects, create rows out of them, and then add them to the DataTable, and then remove them from the ObservableCollection. You can make the DataTable readonly while you're loading the data so the user doesn't start messing with the data while you're adding rows, of course.

The problem with this, though, is you're dealing with concurrency which, as you know, can be easily messed up and I'm no expert on the subject matter so I will avoid any sort of attempt to talk about it.

Since your query is pretty hefty and being called so much, I would also look at making a compiled query to help speed it up just a little bit more.