Error while trying to update inside a foreach

Question

I have this foreach

private void UpdateAzureDiscountGroupReseller(IQueryable<Reseller> model)
        {
            Reseller resellerObj = new Reseller();

            foreach (var item in model)
            {
                resellerObj.AzureDiscountGroupId = null;
                resellerObj.AcceptContractCustomer = item.AcceptContractCustomer;
                resellerObj.Id = item.Id;
                resellerObj.Name = item.Name;
                resellerObj.Alias = item.Alias;
                resellerObj.Enabled = item.Enabled;
                resellerObj.ResellerMpnId = item.ResellerMpnId;
                resellerObj.ServiceApiUser = item.ServiceApiUser;
                resellerObj.ServiceApiPassword = item.ServiceApiPassword;
                resellerObj.HomeTemplateId = item.HomeTemplateId;
                resellerObj.CategoriesIds = item.CategoriesIds;
                resellerObj.AcceptContractCustomerId = item.AcceptContractCustomerId;
                resellerObj.AcceptContractDate = item.AcceptContractDate;
                resellerObj.PathServiceContractAzure = item.PathServiceContractAzure;
                resellerObj.PathPartnershipContractAzure = item.PathPartnershipContractAzure;

                _resellerService.Update(resellerObj);
            }

If I put the call to Update out of foreach, it works. However, I can have more than one Seller with the same FK and what I want is to delete the Group. I can’t give a cascadedelete in the table. Then the solution, to avoid violation of integrity, was, before the Delete in the group, I give an update on FK on Reseller, step to null and then delete. Okay, for a single record that has the FK works fine, but for two or more Reseller’s with the same FK, I need a foreach and in that case gives this error:

New transaction is not allowed because there are other threads running in the Session.

In the Update method it looks like this:

public void Update(Reseller reseller)
        {
            var existingResellerWithSameAlias = GetByAlias(reseller.Alias);
            if (existingResellerWithSameAlias != null && existingResellerWithSameAlias.Id != reseller.Id)
                throw new KeyDuplicatedException("There is a reseller with the same ID or Alias on Database");

            _resellerRepository.Update(reseller);

            cache.Update(reseller.Alias, reseller);

            _logTracker.Register(LogTrackType.Update, reseller);
        }

And when it gets here it is that the error explodes:

_logTracker.Register(LogTrackType.Update, reseller);

How do I update multiple records at once?

Answer 1

Apparently there are two problems:

The first is that as you get one IQueryable, you may be with a representation of your collection of items rather than the collection itself. This happens because Iqueryable is called only at the time it is used, for example:

var resultado = query.Where(x => x.Id < 10); // Aqui a consulta SQL é definida mas ainda não é executada

foreach (var item in resultado) // Aqui a consulta é efetivamente executada e os resultados retornados
{
    Console.WriteLine(item.Name);
}

For this not to happen, it is necessary to use the method .ToList(), which converts into a common list, unlinked from the database records.

The other problem is that you are creating the object outside of the foreach and when you assign the values to it and save it, it is repopulated with the references (such as related objects, for example) and these references may not be updated in the next iteration of the foreach, causing loss of data consistency. To solve this, simply create the object within the loop.

Your code would look like this:

private void UpdateAzureDiscountGroupReseller(IQueryable<Reseller> model)
{
    foreach (var item in model.ToList())
    {
        Reseller resellerObj = new Reseller();
        resellerObj.AzureDiscountGroupId = null;
        resellerObj.AcceptContractCustomer = item.AcceptContractCustomer;
        resellerObj.Id = item.Id;
        resellerObj.Name = item.Name;
        resellerObj.Alias = item.Alias;
        resellerObj.Enabled = item.Enabled;
        resellerObj.ResellerMpnId = item.ResellerMpnId;
        resellerObj.ServiceApiUser = item.ServiceApiUser;
        resellerObj.ServiceApiPassword = item.ServiceApiPassword;
        resellerObj.HomeTemplateId = item.HomeTemplateId;
        resellerObj.CategoriesIds = item.CategoriesIds;
        resellerObj.AcceptContractCustomerId = item.AcceptContractCustomerId;
        resellerObj.AcceptContractDate = item.AcceptContractDate;
        resellerObj.PathServiceContractAzure = item.PathServiceContractAzure;
        resellerObj.PathPartnershipContractAzure = item.PathPartnershipContractAzure;

        _resellerService.Update(resellerObj);
    }
}