pabrams pabrams - 4 years ago 295
C# Question

"The value passed for ConditionOperator.In is empty" with ConditionExpression using ConditionOperator.In against an empty array operand

I have some code as follows:

QueryExpression qxEmpty = new QueryExpression("systemuser");
object[] guids = (new Guid[] { }).Cast<object>().ToArray();
ConditionExpression c = new ConditionExpression("systemuserid", ConditionOperator.In, guids);
qxEmpty.Criteria.AddCondition(c);
EntityCollection ecEmpty = service.RetrieveMultiple(qxEmpty);


It fails on the last line with a FaultException:


The value passed for ConditionOperator.In is empty


If I initialize the
guids
variable to contain one or more valid guids existing in the target CRM, I get the expected result.

So why does RetrieveMultiple throw an exception when I have a condition that asks whether a value is a member of the empty set? The condition should evaluate to false and RetrieveMultiple should return an empty EntityCollection, not throw an exception, shouldn't it?

MSDN seems to suggest it's some problem with the method overloading on ConditionExpression constructor, and to make sure that the appropriate object type is used, but as far as I can tell I'm doing it properly, passing an array of objects:
https://msdn.microsoft.com/en-us/library/gg334419(v=crm.7).aspx

I'm using version 7.1.0.1085 of the Microsoft.Xrm.Sdk

Edit: I guess I'm not asking the right question. How do I pass an empty set to ConditionExpression for use with ConditionOperator.In?

Edit 2:
Okay, here's the SQL I'm trying to translate to QueryExpression.

SELECT *
FROM SystemUser
WHERE SystemUserId IN
(
SELECT SystemUserid
FROM SystemUser
WHERE X=Y
)


I've translated this roughly as follows:

QueryExpression qxEmpty = new QueryExpression("systemuser");
object[] guids = getGuids(); // calls a method that executes the subquery
ConditionExpression c = new ConditionExpression("systemuserid", ConditionOperator.In, guids);
qxEmpty.Criteria.AddCondition(c);
EntityCollection ecEmpty = service.RetrieveMultiple(qxEmpty);


The problem with the above is it fails when the subquery has no values. In SQL, it doesn't fail, it gives me an empty set, which is what I want as a result of the QueryExpression.

Answer Source

Adding to Matt's answer, if you look at the SDK source code, the collection of values is never initialized,not even with an empty DataCollection object when empty arguments are passed. So this is an intended behavior, where in, using an IN condition expects at least one parameter.

SDK source code:

public ConditionExpression(string entityName, string attributeName, ConditionOperator conditionOperator, params object[] values)
{
  this._entityName = entityName;
  this._attributeName = attributeName;
  this._conditionOperator = conditionOperator;
  if (values == null)
      return;
  this._values = new DataCollection<object>((IList<object>) values);
}

Update #1: based on comment SELECT * FROM systemuser where systemuserid IN ( select systemuserid from systemuser where 1=2)

Query expressions have limitations, not every SQL query can be translated directly. From a SQL perspective, using IN definitely has some performance advantages over using multiple WHERE conditions, but in this particular case, there is no option but to use Where - OR in conjunction.

Update #2: After all the comments:

object[] guids = getGuids(); // calls a method that executes the subquery
if (guids == null || !guids.Any()) return new EntityCollection(new List<Entity>());
ConditionExpression c = new ConditionExpression("systemuserid", ConditionOperator.In, guids); 
//rest of the query
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download