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>

CRM 2011 – The easy way not to select all columns in queries

I had got into the bad habit of returning all columns when I was doing a query, well I would certainly do that when I was developing and then change it later.

I wanted a way to select only the columns I wanted but without creating lots of functions selecting different columns.

The solution I came up with was to pass a list into the function and the list held the columns I wanted to select.

So now I pass in a list of field names held in a list and it returns me those columns, this means I can use the same code and I am also not whacking the database by returning lots of columns I don’t need.

Below is the code I use to select Accounts

public Account getAccountDetails(IOrganizationService service, Guid guid, List<string> fields)
{

ConditionExpression condition1 = new ConditionExpression();
condition1.AttributeName = “accountid”;
condition1.Operator = ConditionOperator.Equal;
condition1.Values.Add(guid);

FilterExpression filter1 = new FilterExpression();
filter1.Conditions.Add(condition1);

QueryExpression query = new QueryExpression(Account.EntityLogicalName);

ColumnSet cols = new ColumnSet();
cols.AddColumns(fields.ToArray());
query.ColumnSet = cols;

query.Criteria.AddFilter(filter1);

EntityCollection result1 = service.RetrieveMultiple(query);

IEnumerable<Account> accounts = result1.Entities.Cast<Account>();
Account account = (Account)returnOne(accounts);

return account;

}

This is the code I use to call it

List<string> fields = new List<string>(){
“name”};
Account account = pluginQuery.getAccountDetails(service, accountGuid,fields);

 

The comment below from Georg Müller (@ggmueller) improves the code to make it much easier to use, so thanks for that.  I liked the comment so much I have bumped it into the main blog post.

If you change the from List to String[] then it can save you making a list

Account getAccountDetails(IOrganizationService service, Guid guid, params string[] fields)

as interface. This way you can use it as
Account account = pluginQuery.getAccountDetails(service, accountGuid, “name”, “emailaddress1″) for example.

CRM 2011 – Quick tip – using EntityLogicalName in query expressions

This is a tiny small tip for writing query expressions and using the EntityLogicalName value.

When you write a queryExpression you have to specify the name of the entity you want to query.

The name is a text string you can create a string and put the name of the entity

                QueryExpression query = new QueryExpression("account");

a better way which makes the code easier to read and will ensure you don’t mistype the query is to use the EntityLogicalName attribute of a CRM entity.  Below is an example of doing a query expression for the account

                QueryExpression query = new QueryExpression(Account.EntityLogicalName);

 

CRM 2011 – How to select an Id in a QueryExpression

As I have been converting all my LINQ queries into QueryExpressions so my plugins can work on CRM 2011 Online.  It was quite annoying to find LINQ wasn’t compatible with CRM 2011 online and the sandbox.  I don’t really understand why, I assume it’s something to do with the transactions.

Today I was trying to select the id of an entity and use an entity reference  object to make sure I had got the right one.

The basic scenario is I had an entity which had a Entity Reference to another object and I wanted to use that to return the whole entity.

I initially had a mind blank trying to remember what the ID field was (and it’s not just id) but then I worked it out that it’s the entity name with id added to end of it.  So I was looking for a hosk_candidate entity, I add the id to the end to get

hosk_candidateid

The other little gotcha was when you use the EntityReference to get the id you have to do

Id.id

 public hosk_candidate getCandidateQuery(IOrganizationService service, EntityReference id)
        {
            try
            {
                ConditionExpression condition1 = new ConditionExpression();

                condition1.AttributeName = "hosk_candidateid";
                condition1.Operator = ConditionOperator.Equal;
                condition1.Values.Add(id.Id);

                FilterExpression filter1 = new FilterExpression();
                filter1.Conditions.Add(condition1);

                QueryExpression query = new QueryExpression("hosk_candidate");
                query.ColumnSet = new ColumnSet(true);
                // query.ColumnSet = new AllColumns();
                query.Criteria.AddFilter(filter1);

                EntityCollection result1 = service.RetrieveMultiple(query);
                IEnumerable<hosk_candidate> candidates = result1.Entities.Cast<hosk_candidate>();
                hosk_candidate candidate = (hosk_candidate)returnOne(candidates);

                return candidate;

            }
            catch (FaultException<Microsoft.Xrm.Sdk.OrganizationServiceFault>)
            {
                // You can handle an exception here or pass it back to the calling method.
                throw;
            }
        }

CRM 2011 – How to do Like statement in QueryExpression

I was trying to do a like statement in a query expression and it took me a while to work out how to do it.
the first thing you do is use the Like condidationOperator and then you add the value.  Like an SQL query you need to use
the % wildcard and add this to the text string
            condition1.Operator = ConditionOperator.Like;
            //ConditionOperator.Like
            condition1.Values.Add(id.ToLower() + "%");

the other interesting aspect of the sample code below is I cast the results

 IEnumerable<hosk_candidate> newCandidates = result1.Entities.Cast<hosk_candidate>();

The reason I had to do this was because I was changing how I selected the data from CRM from LINQ queries to
QueryExpressions because LINQ statements cannot be used in CRM Online.

 private IEnumerable<hosk_candidate> selectCandidates(hosk_candidate candidate, string id)
        {

            ConditionExpression condition1 = new ConditionExpression();
            condition1.AttributeName = "hosk_candidatehoskid";
            condition1.Operator = ConditionOperator.Like;
            //ConditionOperator.Like
            condition1.Values.Add(id.ToLower() + "%");

            FilterExpression filter1 = new FilterExpression();
            filter1.Conditions.Add(condition1);

            QueryExpression query = new QueryExpression("hosk_candidate");
            query.ColumnSet.AddColumns("hosk_candidatehoskid");
            query.Criteria.AddFilter(filter1);

            EntityCollection result1 = service.RetrieveMultiple(query);
            IEnumerable<hosk_candidate> newCandidates = result1.Entities.Cast<hosk_candidate>();

            return newCandidates;
        }