Function Custom Code - This platform does not support distributed transactions

I am writing a function to automatically clock out employees after x number of hours clocked in. In the function I have a custom code block that loops through header records where ActiveTrans = true, then within each, loop through detail records where ActiveTrans = true. I call the EndActivity then Update methods for each activity, the same way that MES would.

For each method call I am using the CallService() method.

The problem I am having is that the first use of CallService works fine, but if I use the method a 2nd time, I get the following error: System.Data.Entity.Core.EntityException: The underlying provider failed on EnlistTransaction. —> System.PlatformNotSupportedException: This platform does not support distributed transactions.

That is an Entity Framework/SQL error that I don’t know how to get around. I’m surprised to see this since I am not performing any Add/Update/Delete transactions directly using the ‘Db’ database context.

Any ideas on how I can avoid this error?

Thanks

Code:

// Get 'Active' labor header records
var activeLaborHeaders = Db.LaborHed.Where(x => x.Company == callContextClient.CurrentCompany && x.ActiveTrans);

// Loop through each active labor header
foreach (var activeLaborHeader in activeLaborHeaders)
{
  // Get basic information to be used/re-used later
  var company = activeLaborHeader.Company;
  var employeeNum = activeLaborHeader.EmployeeNum;
  var payrollDate = activeLaborHeader.PayrollDate;
  var laborHedSeq = activeLaborHeader.LaborHedSeq;
  
  // Get the labor dataset for this active header record
  Erp.Tablesets.LaborTableset laborTableset = null;
  this.CallService<Erp.Contracts.LaborSvcContract>(laborSvc => { laborTableset = laborSvc.GetByID(laborHedSeq); });
  
  // Get the employee
  var employee = Db.EmpBasic.FirstOrDefault(emp => emp.Company == company && emp.EmpID == employeeNum);
  
  // Get all labor headers for this employee where the payroll date is the same as the active header record
  var laborHeaders = Db.LaborHed.Where(header => header.Company == company && header.EmployeeNum == employeeNum && header.PayrollDate == payrollDate).ToList();
  
  // Get all labor detail records for the active labor header
  var laborDetails = Db.LaborDtl.Where(detail => detail.LaborHedSeq == laborHedSeq).ToList();
  
  // Get 'Active' labor detail records
  var activeDetails = laborDetails.Where(detail => detail.ActiveTrans).ToList();
  
  // Get the Clock In time and calculate 'Active' and 'Total' hours
  var clockInDateTime = ((DateTime)activeLaborHeader.ClockInDate).AddHours((double)activeLaborHeader.ClockInTime);
  var currentDateTime = Ice.Lib.CompanyTime.Now(company);
  var activeHours = ((decimal)(currentDateTime - clockInDateTime).TotalHours);
  var totalHours = activeHours + laborHeaders.Sum(x => x.PayHours);
  
  if (totalHours >= 16)
  { 
    // Loop through each 'Active' detail
    foreach(var activeDetail in activeDetails)
    {
      var laborDtlSeq = activeDetail.LaborDtlSeq;
    
      // Get the labor detail record from the dataset retrieved earlier
      var laborDtl = laborTableset.LaborDtl.FirstOrDefault(x => x.LaborHedSeq == laborHedSeq && x.LaborDtlSeq == laborDtlSeq);
    
      // Mark the labor detail record as 'Updated' in order for the EndActivity method to know which record to process
      laborDtl.RowMod = "U";
    
      // End the activity
      this.CallService<Erp.Contracts.LaborSvcContract>(laborSvc => { laborSvc.EndActivity(ref laborTableset); });
      
      // Save the labor dataset
      /*** Error occurs here! ***/
      this.CallService<Erp.Contracts.LaborSvcContract>(laborSvc => { laborSvc.Update(ref laborTableset); });
    }
    
    // Clock Out the employee
    /*** Error occurs here if I remove/comment the Update method above! ***/
    this.CallService<Erp.Contracts.EmpBasicSvcContract>(empBasicSvc => { empBasicSvc.ClockOut(ref employeeNum); });
  }
}

Might want to use a second function to pass your arguments into that perform the action

2 Likes

I think that since you are calling a new service for each call you are running into problems. Wrap all of your code in the loop to call the service once, and use it to to all of the transactions each time through the loop.

I tried wrapping the code inside the ‘CallService’ method call, but still got the same errors.

I enabled the ‘Requires Transaction’ checkbox on the Function and that fixed it. I had always skimmed past that checkbox before, never looked into what it was for. Shame on me.

I found this post and was enlightened:

1 Like

I like splitting these up too, I find it helps me understand where the problem is when there is a problem.

1 Like

I enabled the “Requires Transaction” checkbox on the Function but getting the below error

Code:

outMsg = "";
var context = (Erp.ErpContext)Ice.Services.ContextFactory.CreateContext();
var erpContext = new Erp.Internal.Lib.CCredChk(context); //Add Reference in Assemblies 

using(var txScope = Ice.IceContext.CreateDefaultTransactionScope())
{
  var vDbLaborDtlExist = (from xRow in erpContext.Db.LaborDtl
                        where xRow.Company == Session.CompanyID
                        && xRow.ActiveTrans == true
                        select xRow).FirstOrDefault();

  if(vDbLaborDtlExist != null)
  {
      outMsg += "Emp: " + vDbLaborDtlExist.EmployeeNum + ", LaborHed: " + vDbLaborDtlExist.LaborHedSeq + ", LaborDtl: " + vDbLaborDtlExist.LaborDtlSeq + Environment.NewLine;
    
      // CALL Labor SERVICE -- Getting Error in the below Line
      this.CallService<Erp.Contracts.LaborSvcContract>(lsc => {
      });
  }
  
  txScope.Complete();
}


I have found that when enabling the ‘Transaction Required’ checkbox, you should not also use the TransactionScope in the code. Try removing the using(var txScope… statement from your C# code and try the function again.

It’s superfluous at best to use both. The checkbox just wraps your whole function within a TxScope.

You use the TXScope code block if you want the system to rollback some but not all changes if you run into an error. For example, I have a routine that updates certain jobs to the newest part rev. It loops through each job and runs a number of method calls. The TxScope is within the loop, so if (for example) someone transacted something on that job, the update on that job will throw an error and the system will rollback my method calls. The loop then iterates again and the process begins fresh on the next job.

If I used the checkbox, the system would roll back changes made to every job in that function up to the point of the error and then stop trying to update jobs afterwards.

If you’re not looping like that, or you want to roll back everything, then use the checkbox.

1 Like

Removed the Transaction Scope from Code. But still getting the error when i use CallService in the code. Is there any other way to call the methods

outMsg = "";
var context = (Erp.ErpContext)Ice.Services.ContextFactory.CreateContext();
var erpContext = new Erp.Internal.Lib.CCredChk(context); //Add Reference in Assemblies 

//using(var txScope = Ice.IceContext.CreateDefaultTransactionScope())
{
  var vDbLaborDtlExist = (from xRow in erpContext.Db.LaborDtl
                        where xRow.Company == Session.CompanyID
                        && xRow.ActiveTrans == true
                        select xRow).FirstOrDefault();

  if(vDbLaborDtlExist != null)
  {
      outMsg += "Emp: " + vDbLaborDtlExist.EmployeeNum + ", LaborHed: " + vDbLaborDtlExist.LaborHedSeq + ", LaborDtl: " + vDbLaborDtlExist.LaborDtlSeq + Environment.NewLine;
    
      // CALL Labor SERVICE --- Error in this line
      this.CallService<Erp.Contracts.LaborSvcContract>(lsc => {
      });
  }
  
  //txScope.Complete();
}

Is there any other suggestions