Error when using an int array with Term=QueryTerm.Or

ServiceStack Auto Query
1

SS 6.8

Putting Term=QueryTerm.Or on a int[] causes the parameters to be passed as a string to the query, rather than as separate parameters

I have the following class

public class QueryActionItems : QueryDb<ActionItem>
{
    public int? Id { get; set; }
    [QueryDbField(Term=QueryTerm.Ensure)]
    public bool? IsCompleted { get; set; }
    [QueryDbField(Template = "{Field} BETWEEN {Value1} AND {Value2}", 
        Field="DateCreated", Term = QueryTerm.Ensure)]
    public DateTime[] DateCreatedBetween { get; set; }
    [QueryDbField(Term=QueryTerm.Or)]
    public int? CreatedBy { get; set; }
    [QueryDbField(Term=QueryTerm.Or)]
    public int? Owner { get; set; }
    [QueryDbField(Term=QueryTerm.Or)]
    public int[]? Owners { get; set; }
    [QueryDbField(Term=QueryTerm.Or)]
    public int[]? CreatedBys { get; set; }
}

That will generate the following broken query has seen from the profiler:

SELECT TOP 5 “Id”, “Action”, “IsCompleted”, “Notes”, “Resolution”, “Owner”, “DateCreated”, “CreatedBy”, “DateModified”, “ModifiedBy”, “DateDeleted”, “DeletedBy” FROM “ActionItem” WHERE (“ActionItem”.“IsCompleted” = @1) AND “ActionItem”.“DateDeleted” IS NULL AND ((“ActionItem”.“Owner” = @2) OR (“ActionItem”.“CreatedBy” = @3)) ORDER BY “DateCreated” DESC

With the following parameters:

{
“@0”: “null”,
“@1”: false,
“@2”: “[163,217,158]”,
“@3”: “[163,217,158]”
}

If I comment out both Or terms on the class I see the following:

SELECT COUNT() "COUNT()" FROM “ActionItem” WHERE (“ActionItem”.“IsCompleted” = @1) AND “ActionItem”.“DateDeleted” IS NULL AND (“ActionItem”.“Owner” IN (@2,@3,@4) AND “ActionItem”.“CreatedBy” IN (@5,@6,@7))

With the following parameters
{
“@0”: “null”,
“@1”: false,
“@2”: 163,
“@3”: 217,
“@4”: 158,
“@5”: 163,
“@6”: 217,
“@7”: 158
}

2

Can you give an example of the query from the URL being made to cause the issue you are seeing as well? This can impact the generated query, especially if there are use of null values being provided such as &Owner=&CreatedBy=. This will help me reproduce the issue you are seeing.

3

I am calling the code from a blazor razor page:

        api = await ApiAsync(new QueryActionItems() { CreatedBy = GetCreatedBy(), Owner = GetCreatedBy(), IsCompleted = _hideCompletedItems ? false : null, Skip = skip, Take = args.Request.PageSize, OrderByDesc = "DateCreated", DateCreatedBetween = new []{StartDate.Value, EndDate.Value}, CreatedBys = CreatedBys, Owners = CreatedBys});
4

Ok thanks, can I get the ActionItem POCO as well (assuming it matches your SQL schema) and I think that should be enough for me to create a reproduction.

5 
public class ActionItem:AuditBase
{
    [AutoIncrement]
    public int Id { get; set; }

    public string Action { get; set; }
    public bool IsCompleted { get; set; }
    [CustomField(OrmLiteVariables.MaxText)]

    public string Notes { get; set; }
    [CustomField(OrmLiteVariables.MaxText)]
    public string Resolution { get; set; }

    [References(typeof(OwnerUser))]
    public int? Owner { get; set; }
    
    [Reference]
    public OwnerUser? OwnerUser { get; set; }
}

CreatedBy is an int column from AuditBase

6

So the default behavior is there to handle more implicit use cases. You will want to alter your Request DTO query class to use the following.

public class QueryActionItems : QueryDb<ActionItem>
{
    public int? Id { get; set; }
    [QueryDbField(Term=QueryTerm.Ensure)]
    public bool? IsCompleted { get; set; }
    [QueryDbField(Template = "{Field} BETWEEN {Value1} AND {Value2}", 
        Field="DateCreated", Term = QueryTerm.Ensure)]
    public DateTime[] DateCreatedBetween { get; set; }
    [QueryDbField(Term=QueryTerm.Or)]
    public int? CreatedBy { get; set; }
    [QueryDbField(Term=QueryTerm.Or)]
    public int? Owner { get; set; }
    [QueryDbField(Term=QueryTerm.Or, Template = "Owner IN ({Values})", ValueStyle = ValueStyle.List)]
    public int[]? Owners { get; set; }
    [QueryDbField(Term=QueryTerm.Or, Template = "CreatedBy IN ({Values})", ValueStyle = ValueStyle.List)]
    public int[]? CreatedBys { get; set; }
}

The use Values (plural) will be detected correctly as a list and your template will be used, forcing the SQL In query. Let me know if you are still seeing issues when using this approach.

1 Like
7

That solved the problem. Thanks for taking a look. I didn’t know that ValueStyle was a parameter and couldn’t find anything about it in the documentation.

1 Like