Method Directive LINQ Update UD Fields

I have tried this code in a Method Directive in Quantity Adjust as well as a Data Directive in SerialNo. It compiles, and based on the messages I am displaying seems to be touching the correct records I intended to update. However none of the records were actually updated. Is this something simple I missed?

 Erp.Tables.SNTran SNTran;
 
     using (var txScope = IceContext.CreateDefaultTransactionScope())
       {
       
       foreach (var  ttSerialNo_xRow in ttSerialNo)
         {
       
               foreach(var SNTran_xRow in (from SNTran_Row in Db.SNTran.With(LockHint.UpdLock) where
               ttSerialNo_xRow.Company == SNTran_Row.Company &&
               ttSerialNo_xRow.SerialNumber == SNTran_Row.SerialNumber &&
               SNTran_Row.ATFPart_c == "0"
             select SNTran_Row).ToList())
                 {
                   SNTran_xRow.ATFPart_c= PartNum;
                   CallContext.Current.ExceptionManager.AddBLException("SN:" + ttSerialNo_xRow.SerialNumber.ToString() + "  SNPart:" + SNTran_xRow.ATFPart_c.ToString() + "SNTranRow:" + SNTran_xRow.TranNum.ToString() );
                 }
 
 
        }
       
             Db.Validate();
           //  Db.SaveChanges();
            txScope.Complete();
     }

wondering if it is the “.ToList()” that is causing your problem? I think (not totally sure) that when you select it to a list, it is no longer connected to the data table.

Try something this instead: (totally untested):
(What I did:

  1. changed the database query to execute all at once, putting the data into memory
  2. THEN looped through the data to update it.
using (var txScope = IceContext.CreateDefaultTransactionScope())  {
  foreach (var  ttSerialNo_xRow in ttSerialNo) {
        var dbSNTran = Db.SNTran.With(LockHint.UpdLock).Where(x=>
          x.Company == ttSerialNo_xRow.Company &&
          x.SerialNumber == ttSerialNo_xRow.SerialNumber &&
          x.ATFPart_c == "0")

          foreach(var SNTran_xRow in dbSNTran {
              SNTran_xRow.ATFPart_c= PartNum;
              CallContext.Current.ExceptionManager.AddBLException("SN:" + ttSerialNo_xRow.SerialNumber.ToString() + "  SNPart:" + SNTran_xRow.ATFPart_c.ToString() + "SNTranRow:" + SNTran_xRow.TranNum.ToString() );
            }
            
  }
  Db.Validate();
  txScope.Complete();
}
2 Likes

Also make sure you remove this. You don’t want to try and transact on uncommitted rows.

2 Likes

I concur, this won’t be the same object.

So I moved the suggested code back to a Method Directive in Quantity Adjust and tried it out. Seems to be responding the same as the List(). I’m not sure if calling from a Data Directive would be any different but about to go back and try it there again. I tried with and without the locking but didn’t see to make a difference.

Erp.Tables.SNTran SNTran;
using (var txScope = IceContext.CreateDefaultTransactionScope())  
{
    foreach (var  ttSelectedSerialNumbers_xRow in ttSelectedSerialNumbers)
    {
        var dbSNTran = Db.SNTran.With(LockHint.UpdLock).Where(x=>
          x.Company == ttSelectedSerialNumbers_xRow.Company &&
          x.SerialNumber == ttSelectedSerialNumbers_xRow.SerialNumber &&
          x.ATFPart_c == "0");

          foreach(var SNTran_xRow in dbSNTran )
            {
              SNTran_xRow.ATFPart_c= PartNum;
              CallContext.Current.ExceptionManager.AddBLException("SN:" + ttSelectedSerialNumbers_xRow .SerialNumber.ToString() + "  SNPart:" + SNTran_xRow.ATFPart_c.ToString() + "SNTranRow:" + SNTran_xRow.TranNum.ToString() );
             Db.Validate();
             Db.SaveChanges();
            }
     }     
  Db.Validate();
  Db.SaveChanges();
  txScope.Complete();
}

@AlexanderDelarge I formatted you code so we can read it easier. Does that exception get thrown?

I am thinking that would rollback the save.

No exception was thrown, it just doesn’t update the fields.

Y’all could also just use UpdateExt instead of going straight to DB.

Just sayin.

2 Likes

Wondering… since you are doing this as part of a method directive: if you are doing it as a PRE process… the records may not exist yet (becuase it is BEFORE they are created). You may need to try this as a POST process so that the records exist before you try to update them.

1 Like

Are you throwing an exception? (I’ve never seen it thrown that way, but I just see “Exception”) That’s going to stop everything, and roll back your transaction. If you get rid of that line, does it work?

I think that just puts it in the queue for the UI process to show it, but I am unsure.

Run this and see what it says.

int rowCount = 0;

using (var txScope = IceContext.CreateDefaultTransactionScope())  
{
    foreach (var  ttSelectedSerialNumbers_xRow in ttSelectedSerialNumbers)
    {
        var dbSNTran = Db.SNTran.With(LockHint.UpdLock).Where(x=>
          x.Company == ttSelectedSerialNumbers_xRow.Company &&
          x.SerialNumber == ttSelectedSerialNumbers_xRow.SerialNumber &&
          x.ATFPart_c == "0");

          foreach(var SNTran_xRow in dbSNTran )
          {
              SNTran_xRow.ATFPart_c= PartNum;
              rowCount++;
          }
     }     

     Db.Validate();
     txScope.Complete();
}

if(rowCount > 0) InfoMessage.Publish(rowCount.ToString());

Commenting that out did not get the ATFPart to update …but it did appear to allow process of the “message” widget I had after the C# widget.

Gives me 10
This is the number of records I would expect to get updated.

Just making sure before I open my big mouth and send you down the wrong path.

I assume they did not update?

No…not yet
I was thinking of trying to join the SNTran_UD and see if it made a difference.

Isn’t the validate out of scope with the DB Lookup? Don’t you need it to validate within the {}?

int rowCount = 0;

using (var txScope = IceContext.CreateDefaultTransactionScope())  
{
    foreach (var  ttSelectedSerialNumbers_xRow in ttSelectedSerialNumbers)
    {
        var dbSNTran = Db.SNTran.With(LockHint.UpdLock).Where(x=>
          x.Company == ttSelectedSerialNumbers_xRow.Company &&
          x.SerialNumber == ttSelectedSerialNumbers_xRow.SerialNumber &&
          x.ATFPart_c == "0");

          foreach(var SNTran_xRow in dbSNTran )
          {
              SNTran_xRow.ATFPart_c= PartNum;
              rowCount++;
          }
         Db.Validate();
     }     


     txScope.Complete();
}

if(rowCount > 0) InfoMessage.Publish(rowCount.ToString());

You don’t need to do that.

Sure is.

Didn’t seem to make a difference.

Just to clarify, you tried Brandon’s version of the code with the slight mod?