0

I am trying to write a QueryExpression in C# using the CRM SDK. Where I have a main query and some entities to join. My issue is that 1 of the entities that I join to has a field that I need to filter and cannot be null also.

var mainQuery = new QueryExpression([entity1])
{
    ColumnSet = new ColumnSet("XXXX"),
};
var entity1Join = mainQuery.AddLink([fields], JoinOperator.Inner);

...[other joins here]

var entity2Join = entity1Join.AddLink("entity2", "fieldid", "fieldid", JoinOperator.Inner);
contactJoin.LinkCriteria = new FilterExpression()
{
    FilterOperator = LogicalOperator.And,
    Conditions = { 
            new ConditionExpression([field1], ConditionOperator.NotNull), 
            new ConditionExpression([field1], ConditionOperator.Equal, [value])
        }   
};
...[other joins here]


var result = this.CRMClient.OrganizationServiceProxy.RetrieveMultiple(mainQuery).Entities;

I get this error:

Condition for attribute 'entity2.field1': null is not a valid value for an attribute. Use 'Null' or 'NotNull' conditions instead.

Please help, thanks.

the.herbert
  • 289
  • 1
  • 5
  • 14

1 Answers1

0

From your 2nd entity filter operation remove 1st condition for null because anyhow you are giving some specific value and also what is your value coming. Does your value itself is null or it does contain data.

Before giving value for 2nd condition better check if it does contain data or not.

AnkUser
  • 5,421
  • 2
  • 9
  • 25
  • Thanks. I checked the value, it’s not null. But I checked the entity2.field1, has null values. I was trying to do something similar to SQL query: ‘where entity2.field1 is not null and entity2.field1 = value’ – the.herbert Jun 22 '19 at 22:05
  • Yes but will first condition not be obsolete as you are giving specific value in 2nd condition and your query will return data matching 2nd condition. I would suggest try using hardcoded value for 2nd condition so that you can test if your query is correct and then move one step next from there on. – AnkUser Jun 24 '19 at 05:13
  • Thanks again. You're right probably. What I also did just for good measure was that I pre-filtered the main query to make sure I have a smaller dataset to query out. – the.herbert Jul 11 '19 at 22:46