CRM 2011 – QueryExpressions where a field is null

I had to write some code today which retrieved some entities which didn’t have a value in a field, so basically I had to write a query to find all the entities which had a null value.

I finally found some sample code on the Microsoft SDK

FilterExpression null_filter = new FilterExpression(LogicalOperator.And);
null_filter.FilterOperator = LogicalOperator.And;
null_filter.AddCondition("leadid", ConditionOperator.Null);

below is my full code.  I am selecting my custom charges entity where the invoice lookup value is null

</pre>
public IEnumerable<meta_charge> getCharges(IOrganizationService service)
{
try
{

ConditionExpression condition1 = new ConditionExpression();

condition1.AttributeName = "new_invoice";
condition1.Operator = ConditionOperator.Null;
FilterExpression filter1 = new FilterExpression();
filter1.Conditions.Add(condition1);

QueryExpression query = new QueryExpression(new_charge.EntityLogicalName);
query.ColumnSet = new ColumnSet(true);

query.Criteria.AddFilter(filter1);

EntityCollection result1 = service.RetrieveMultiple(query);

IEnumerable<new_charge> charges = result1.Entities.Cast<new_charge>();
return charges;

}
catch (Exception)
{
// You can handle an exception here or pass it back to the calling method.
return null;
}
}
<pre>

2 thoughts on “CRM 2011 – QueryExpressions where a field is null

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s