Acumatica Fluent BQL selects hang

Question

In a customization, I have overridden the behavior of a SO Line inventory ID RowUpdating event, so that the Alternate ID is set to a different value for a certain condition. To do so, I created a new attribute class that inherits from AlternativeItemAttribute and use the new class for an AlternateID instead. The new attribute class overrides the RowUpdating method to do so.

In the overridden method I perform some queries with Fluent BQL, and they just appear to hang. Example:

InventoryItemMaint itemGraph = PXGraph.CreateInstance<InventoryItemMaint>();

InventoryItem item = itemGraph.Item.Select<InventoryItem>()
    .Where(i => i.InventoryID == newRow.InventoryID)
    .FirstOrDefault();

I ended up replacing it with regular BQL to resolve it (see below), but I'd rather use F-BQL, plus I'd like to know if I'm doing something wrong.

InventoryItem item = new PXSelectReadonly<InventoryItem,
    Where<InventoryItem.inventoryID, Equal<Required<InventoryItem.inventoryID>>>>(itemGraph)
    .SelectSingle(newRow.InventoryID);

Have you tried using the Acumatica request profiler or the SQL profiler to see what’s going on? This is a relatively straightforward select, shouldn’t hang like that. — Gabriel, Sep 12 '20 at 23:11

score 1 · Answer 1 · answered Sep 13 '20 at 19:24

The code presented is just BQL to to retrieve data from the Item view of the InventoryItemMaint graph. The fancy part of it is LINQ. Personally, I tend to use Search to find records of the primary view and Select to retrieve the data of a child view. The 2nd sample code is using BQL to select a record directly without use of a view. Depending on what you are doing, you actually can select from your current (or base) graph using this or Base as the graph name. No need to initialize a different graph just to retrieve a record unless the graph has some logic to populate fields of the DAC, which is not terribly common except when creating records.

I believe your Select in the 1st example literally asks the database to retrieve ALL items and then use LINQ to narrow the result to a single item. That's likely the cause of the "hang".

Here is a sample of FBQL to replicate your 2nd example which specifically asks the database for only the specified item.

InventoryItem item = SelectFrom<InventoryItem>
    .Where<InventoryItem.inventoryID.IsEqual<@P.AsInt>>
    .View.Select(itemGraph, newRow.InventoryID);

As noted above, itemGraph in the FBQL could be replaced with just this (or Base if a graph extension) and drop the CreateInstance of the other graph unless you really need that graph for other code.

If you are new to FBQL, note that you may need to add a new Reference to the project for PX.Data.BQL.Fluent and also include as a using statement.

Ah, yes, I think I was confusing Linq with Linq-to-Sql in Entity Framework, and was assuming the conditions would be added to the db query and not the result. I am not new to Linq, but am new to the BQL version. I'll give your suggestion a try. — Tony Lanzer, Sep 13 '20 at 20:47

Acumatica Fluent BQL selects hang

1 Answers1