Autoquery and IN CLAUSE

ServiceStack Auto Query
1

Service Stack 4.5.0
Autoquery input dto
…
public List PersonId { get; set; } = new List();
…

When the PersonId is an empty list it generates the following

… FindPersonBinding".“PersonId” IN ()

that is an invalid SQL statement …

2

Don’t populate the AutoQuery Request DTO with an empty collection.

3

if I do something like this :
if (query.PersonId != null && query.PersonId.Length== 0)
query.PersonId = null;

What I get is :

ErrorCode=FormatException ServerStackTrace=System.FormatException: Input string was not in a correct format.
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
at ServiceStack.Text.Common.DeserializeBuiltin1.<>c.<GetParseFn>b__4_5(String value) at ServiceStack.Text.Common.JsReader1.<>c__DisplayClass2_01.<GetCoreParseFn>b__2(String value) at ServiceStack.Text.TypeSerializer.DeserializeFromString(String value, Type type) at ServiceStack.AutoMappingUtils.ChangeTo(String strValue, Type type) at ServiceStack.TypedQuery2.AppendUntypedQueries(SqlExpression1 q, Dictionary2 dynamicParams, String defaultTerm, IAutoQueryOptions options, Dictionary2 aliases) at ServiceStack.TypedQuery2.CreateQuery(IDbConnection db, IQueryDb dto, Dictionary2 dynamicParams, IAutoQueryOptions options) at ServiceStack.AutoQuery.CreateQuery[From](IQueryDb1 dto, Dictionary2 dynamicParams, IRequest req) at ServiceStack.AutoQueryExtensions.CreateQuery[From](IAutoQueryDb autoQuery, IQueryDb1 model, IRequest request)
at

4

Can you provide a stand-alone example I can run to see this issue?

5

I think the main problem boils down to the fact that the sstack client
turns an empty arrray into
http://localhost:7465/persons/16/0?personId=[]
that is http://localhost:7465/persons/16/0?personId=[]
instead of
that is http://localhost:7465/persons/16/0

on the server side AppendTypedQueries does not remove dto.personId since is null
var value = entry.Value(dto);
if (value == null)
continue;

so in dynamicParams the personId entry is not removed
and later on AppendUntypedQueries blows since tries to convert [] to an integer

I think there should be a fix on the client side so that empty arrays do not go as querystringparameter=[]

additionaly maybe

var value = entry.Value(dto);
if (value == null)
continue;
dynamicParams.Remove(entry.Key);
AddCondition(q, defaultTerm, quotedColumn, value, implicitQuery);

should become

var value = entry.Value(dto);
dynamicParams.Remove(entry.Key);
if (value == null)
continue;
AddCondition(q, defaultTerm, quotedColumn, value, implicitQuery);

6

If your DTO contains an empty array, the ServiceStack Client needs to send it. Services may or may not interpret an empty collection to be significant.

But why are you sending an empty array in a filter? What query are you trying to run?

The solution is not to send empty collections in AutoQuery DTOs.

7

FYI Add ServiceStack Reference purposely excludes AutoQuery DTOs collections from being initialized for this reason. You also shouldn’t be initializing collections in AutoQuery DTOs for the same reason.

8

I agree with you about the ServiceStack Client

However what you propose is more workaround than a solution:
When it comes to autoquery it should treat nulls and empty arrays the same way & emit the same sql query
I turned off EnableUntypedQueries and now it works

thank you
regards
enrico