Invalid SQL Server query created using WhereIn() and empty arrays

[gwheeloc]

Env: SQLServer 2016, C# 10 sqlKata version 2.3.7

I'm developing this query: IEnumerable<dynamic> results = (IEnumerable<dynamic>)db.Query(dbTable).Select(dbSelectColumns).WhereIn("Customer", criteria.Customers).WhereIn("Territory", criteria.Territories).WhereIn("ProfitCenter", criteria.ProfitCenters).WhereIn("JobNumber", criteria.JobNumbers).WhereIn("ProjectName", criteria.Projects).WhereIn("AuditorId", criteria.Auditors).WhereIn("SubjectId", criteria.Subjects).WhereIn("Status", criteria.Statuses).WhereRaw(ratingCondition).Get<dynamic>();

The properties of the criteria object are of type List<string. If I submit a query with each property have at least one string value in the list, the query execution is fine.

When one of the Lists is empty, the portion of the query representing that List property looks like this: 1 = 0 /* IN [empty list] */ If I change the 0 to a 1 the query works as expected, otherwise no results are returned from the query. Here is a complete example when the customer list has one entry in it and the rest are empty.

exec sp_executesql N'SELECT [AuditName], [DateOfAudit], [DateOfCreation], [Subject], [AuditorName], [Customer], [Territory], [ProfitCenter], [ProjectName], [JobNumber], [Model], [Rating], [MSARating], [Status], [Target] FROM [DRAudit] WHERE [Customer] IN (@p0) AND 1 = 0 / IN [empty list] / AND 1 = 0 / IN [empty list] / AND 1 = 0 / IN [empty list] / AND 1 = 0 / IN [empty list] / AND 1 = 0 / IN [empty list] / AND 1 = 0 / IN [empty list] / AND 1 = 0 / IN [empty list] /',N'@p0 nvarchar(4000)',@p0=N'Holy & Co.'

Shouldn't the 0 be a 1 in this case when using WhereIn() with Lists/Arrays of strings?

[ahmad-moussawi]

No this is the expected behavior, since the list is empty so the Where Statements is falsy (represented by 1 = 0) if you want to get results even if one if them is empty you have to use orWhere instead

[gwheeloc]

Ok, I see. I really didn't want to roll my own Where statement, as the user of the query can pick any of the properties and expects them to be and'd together and ignored if property list is not present.

[ahmad-moussawi]

So you can do check by yourself, by using the When or an explicit if condition. something like

new Query("Table").Select(dbSelectColumns)
.When(criteria.Customers.Any(), q => q.WhereIn("Customer", criteria.Customers))

[gwheeloc]

Thank you, this is exactly what I needed.