How to use QueryDbField "%IsNull" operand?

ServiceStack Auto Query
1

I have a service that queries a db table:

public class QueryPayment : QueryDb<Payment>, IGet
{
    public Guid? BankAccountId { get; init; }

    [QueryDbField(Operand = "%IsNull")]              // how to do this?
    public DateOnly? ReconciledDate { get; init; }
}

I am struggling with the syntax for the QueryDbField on the ReconciledDate, because I do not care about the value of the date, I only want results when the ReconciledDate is NULL, NOT NULL or both combined (i.e. similar to specifying a bool query property).

I checked the documentation for Customizable Operands, but, I could not find an example using the “%IsNull” operand.

Anybody got any pointers?

2

The property just needs to exist on the queryString, so you should be able to create a conventional property like:

public bool? ReconciledDateIsNull { get; init; }
3

That did what I wanted, thank you.

Spoke to soon; it is doing something, but not correctly. Below is the generated SQL for ReconciledDateIsNull is false and then true. In both case, I do not see how the @0 param is being used in the select statements.

https://localhost:7402/api/ledger/payment/summary/flat?ReconciledDateIsNull=false

SQL: SELECT TOP 5000 "ReconciledDate"
FROM "Ledger"."Payment"
WHERE "Ledger"."Payment"."ReconciledDate" IS NULL
PARAMS: @0=False
SQL: SELECT COUNT(*) "COUNT(*)"
FROM "Ledger"."Payment"
WHERE "Ledger"."Payment"."ReconciledDate" IS NULL
PARAMS: @0=False


https://localhost:7402/api/ledger/payment/summary/flat?ReconciledDateIsNull=true

SQL: SELECT TOP 5000 "ReconciledDate"
FROM "Ledger"."Payment"
WHERE "Ledger"."Payment"."ReconciledDate" IS NULL
PARAMS: @0=True
SQL: SELECT COUNT(*) "COUNT(*)"
FROM "Ledger"."Payment"
WHERE "Ledger"."Payment"."ReconciledDate" IS NULL
PARAMS: @0=True

Any ideas?

1 Like
4

Does it not return the expected results? What results is it returning?

5

With ReconciledDateIsNull = null, I get all the values from the table.Correct.

With ReconciledDateIsNull = true, I get only the NULL values. Correct.

With ReconciledDateIsNull = false, I get only the NULL values. Wrong.

The data result is identical with ReconciledDateIsNull set to false or true.

6

The existence of the query param is what adds the condition, the reason for using a nullable bool? is so it’s only set when needed, you can send an empty string instead if you don’t want to use bool?.

If you want to check for not null you’d need to use %IsNotNull

7

Aha! In that case, I will add a second property for the NOT NULL case. I did not understand that from the documentation. Thank you.

1 Like