c# - Efficient and fast way to process database records in thread or parallel

Question

I'm currently searching for an efficient and fast way to process some records from a SQL Server database table in parallel/thread. Currently, I'm creating a console app that process the following:

• Update some records in the database (roughly thousands)
• Generate an excel file based on the updated records in the database
• Send an email and attached the generated excel file

Below is my trim code:

public static void Main(string[] args)
{
    // Users here might be thousands of records
    var users = UnitOfWork.UserRepository.GetAll().Where(p => p.Status == "Active").ToList();

    Parallel.ForEach(requestsToUpdate, (user) =>
    {
        UpdateRecordsBaseOnProductId(user.Id);
    });
}

private void UpdateRecordsBaseOnProductId(int userId)
{
    // User requests also might be thousands of records
    var userRequests = UnitOfWork.UserRequestRepository
                                    .GetAll()
                                    .Where(userRequest => userRequest.Id == userId);

    foreach (var item in userRequests)
    {
        item.Status = "Denied";
        item.DateUpdated = DateTime.Now;

        UnitOfWork.UserRequestRepository.Update(item);
        UnitOfWork.Save();
    }

    // Generate excel file
    // Send email with excel attachedment
}

Basically, what I want is to process all the Users that I retrieved from the database in parallel obviously to finish the processing in a short period of time.

So far in my existing code using Parallel.Foreach, I'm receiving the following errors in random.

• A second operation started on this context before a previous asynchronous operation completed. Use 'await' to ensure that any asynchronous operations have completed before calling another method on this context. Any instance members are not guaranteed to be thread safe

• requires an open and available connection. The connection's current state is connecting

• Object reference not set to an instance of an object.

These errors occur in the UpdateRecordsBaseOnProductId method. Where I'm retrieving the user history.

Any recommendations and suggestions will highly appreciated.

Additionally, I'm using a Repository Pattern and Entity Framework 6 to query, update, and save records in the database.

Thank you!upda

Answer 1

等待大神答复