Linq and SvcContract routines will not update database

Hi Folks. I’m having trouble trying to write updated records to the UD105A table. I don’t see the problem, as I have written to other tables in this fashion numerous times without incident. I tried to do it using Linq, and then tried to update using SvcContract. The two code snippets are below. If anyone sees something that I’m missing and would point it out to me, I would be appreciative. Thanks.

Version 1:

foreach (var _UD105a_row in (from u5 in Db.UD105
                            join u5a in Db.UD105A
                            on new { u5.Company, u5.Key1, u5.Key2, u5.Key3, u5.Key4, u5.Key5 }
                            equals new { u5a.Company, u5a.Key1, u5a.Key2, u5a.Key3, u5a.Key4, u5a.Key5 }
                            where u5.Company == Session.CompanyID
                            && u5.Key5 == strShipmentID
                            && (u5.ShortChar05 == strShipVia2 || strShipVia2 == "" || strShipVia2 == "ALL")
                            && (u5a.ShortChar07 == "" || strOption.ToUpper() == "FORCE")
                            select u5a))
{
    _UD105a = _UD105a_row;
    fMsg("fixUD105TrackingNum ... {" + _UD105a.SysRowID.ToString().ToUpper() + "} changed from \"" + _UD105a.Character07 + "\" to \"" +
        strTrackingNum + "\".");
    _UD105a.Character07 = strTrackingNum;
}

Version 2:

using (Ice.Contracts.UD105SvcContract _hUD105 =
    Ice.Assemblies.ServiceRenderer.GetService<Ice.Contracts.UD105SvcContract>(Db))
{
    Ice.Tablesets.UD105Tableset dsUD = new Ice.Tablesets.UD105Tableset();
    bool _boolMore;
    string _whereClauseUD105 = "Key5 = '" + strShipmentID + "' " +
        "AND (ShortChar05 = '" + strShipVia2 + "' OR '" + strShipVia2 + "' = '' OR '" + strShipVia2 + "' = 'ALL')";
    string _whereClauseUD105A = "Key5 = '" + strShipmentID + "' " +
        "AND (ShortChar07 = '' OR '" + strOption.ToUpper() + "' = 'FORCE')";
    try { dsUD = _hUD105.GetRows(_whereClauseUD105, "", _whereClauseUD105A, "", 0, 1, out _boolMore); } catch { }
    if (dsUD.UD105A.Count > 0)
    {
        foreach (Ice.Tablesets.UD105ARow _ud105A in (from u5a in dsUD.UD105A
                                                        select u5a))
        {
            PnyStats.viRead++;
            fMsg("fixUD105TrackingNum ... {" + _ud105A.SysRowID.ToString().ToUpper() + "} changed from \"" + _ud105A.Character07 + "\" to \"" +
                strTrackingNum + "\".");
            _ud105A.Character07 = strTrackingNum;
            _ud105A.RowMod = "U";
            _hUD105.Update(ref dsUD);
            PnyStats.viChg++;
        }
    }
    dsUD = null;
}

In Version 1, you want to wrap your Db hard updates like the following (and only use them as a last resort where the services don’t provide a mechanism to do what you want):

using (var txn = IceContext.CreateDefaultTransactionScope())
{
   // your code here
   Db.Validate();
   txn.Complete();
}

For Version 2, I only glanced for about 10 seconds… have you tried removing your suppressed catch to see if you’re actually successfully getting data there?

In both instances, I am getting data. It’s just not writing the updates to the table. I’ve used both versions in other scenarios and they update without incident.

I also tried Db.Validate() in Version 1. Looks like it runs, but same non-results when looking at the table contents.

This works as long as data comes back. What field name are you passing into strOption? Seems odd to take a UD column to upper case or a regular column name. Something like this below works

    string strOption = "Character01";
    string _whereClauseUD105A = "Key1 = '1' and Character01 = 'Test12' OR " + strOption + " = 'Test123' ";

You say you are getting data back, so I assume you have a log or message (fMsg) that is firing inside of the loop to verify this. If this is the case, are you confident that strTrackingNum actually has a non null value?

v1 should work fine, depending on where you are calling it… If its in a POST then you might want to do like @josephmoeller said use a TransactionScope - also you might have to refresh the DataSet, if you are expecting the UI to change after a POST.

Also Epicor might by default do a LockHint.NoLock (readonly) so ask for a Lock

foreach (var _UD105a_row in (from u5 in Db.UD105.With(LockHint.UpdLock)
                            join u5a in Db.UD105A.With(LockHint.UpdLock)
                            on new { u5.Company, u5.Key1, u5.Key2, u5.Key3, u5.Key4, u5.Key5 }
                            equals new { u5a.Company, u5a.Key1, u5a.Key2, u5a.Key3, u5a.Key4, u5a.Key5 }
                            where u5.Company == Session.CompanyID
                            && u5.Key5 == strShipmentID
                            && (u5.ShortChar05 == strShipVia2 || strShipVia2 == "" || strShipVia2 == "ALL")
                            && (u5a.ShortChar07 == "" || strOption.ToUpper() == "FORCE")
                            select u5a))

Example:

using (var txScope = IceDataContext.CreateDefaultTransactionScope())
{
	var PartPlants =
		from pp in Db.PartPlant.With(LockHint.UpdLock)
		where
			pp.Company == Session.CompanyID
			&& pp.Number01 == 0
		select pp;

	foreach (var partPlantRow in PartPlants)
	{
		partPlantRow.Number01 = 1;
	}

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

One of the things we see a lot is locks blocking folks at scale. One of the patterns we use a lot internally is:

Collection read with no lock
Make decisions on whether to update elements in the collection
Iterate through the trimmed collection
Read with lock //minimizing lock scope
Update
Commit

It’s amazing how performance and lock contention (e.g. - scale) is improved with this pattern. Especially when that area of should I do something may call out into many libs and their dependent libs.

3 Likes

@Bart_Elia would one ever use Db.IsLocked in a BPM? Never found a use case for it.

// If the record exists and is locked by someone else, the function returns true.  
// In all other cases it returns false, but will lock the record for you in the process.
if (Db.IsLocked(POHeader)) {
  // do work
}

You probably have never written a BPM with 13K lines in it :wink:
(Upgrade Services (aka Cirrus) allows for some interesting analysis of BPMs in the wild.)

In general you probably don’t need it. If you start creating an entire service in a BPM though and get some intense biz logic, it’s nice to have in the toolbox.

To all, thanks for the responses. To answer the subsequent querries:

  • Yes, I have a log and have verified that the dataset has the content I expect to write back
  • Yes, the strOption.ToUpper() works and its purpose is to flag the routine to update all records and not just the ones with an empty Character07 field
  • Yes, the strTrackingNum variable has content
  • the BPM is called from an In-Transaction Data Directive for UD40.Update
  • I will try the transaction block and see if that makes a difference.

Tried the using txn = IceContext snippet, didn’t work, records still not updating and no error rendered.

Adding With(LockHint.UpdLock) to both tables didn’t work.

Adding .ToList() at the end of the LINQ statement didn’t work.

I found the problem. It wasn’t in this routine, it was in a section further down that was throwing an exception message instead of an information message.

1 Like