Handling NULL Values in BQL Fluent Expressions in Acumatica

Working with NULL values in database queries often introduces unexpected behavior, especially when using frameworks that abstract SQL logic like Acumatica's BQL Fluent syntax. One common scenario is attempting to retrieve records based on a nullable key field, such as SubItemID in the POVendorInventory table. If the field contains NULL in the database and the query passes a NULL value, the record is often not returned—even though it exists.

This behavior can be puzzling, especially if a raw SQL query correctly returns the record. So, why does Acumatica's BQL Fluent fail to match it?

The Problem

Let’s look at a simplified version of the original BQL Fluent statement:

In this case, reg.SubItemID is NULL. The expectation is that this should match a record in the table where SubItemID is also NULL. However, the result is null, meaning no match was found—even though the database clearly contains such a record.

This happens because .IsEqual<@P> with a NULL parameter results in SQL like subItemID = NULL, which is invalid logic in SQL and always evaluates to false. In SQL, NULL must be compared using IS NULL.

The Correct Approach

To account for both scenarios—when SubItemID is NULL and when it has a value—you need to use a conditional expression in BQL Fluent that explicitly handles NULLs.

Here’s the correct way to structure the query:

Why This Works

The expression uses a Brackets clause to evaluate two possible cases:

1. POVendorInventory.subItemID IS NULL and the parameter is also null.
2. POVendorInventory.subItemID = @P when the parameter has a non-null value.

By explicitly checking for nulls using .IsNull on both the field and the parameter, and combining the logic with .OrElse, the BQL query mirrors SQL behavior and retrieves the record as expected.

Practical Considerations

• This technique is crucial when working with fields that are part of unique keys or used for deduplication logic.
• If you're enforcing uniqueness before inserting new records, make sure your logic handles NULL comparisons correctly to avoid accidentally creating duplicates.
• The same approach applies to any other nullable field, not just SubItemID.

Final Thoughts

Acumatica’s BQL Fluent API is powerful but has nuances—especially when dealing with NULL values. Understanding how NULL is handled in expressions can save hours of confusion during debugging. If a field in your table allows NULLs and is part of your search criteria, always account for the possibility of a NULL comparison failing silently unless explicitly handled.

By using Brackets, .IsNull, and .OrElse, you can write BQL queries that are accurate, flexible, and match SQL behavior perfectly—ensuring your custom logic behaves as expected across all edge cases.