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.

Advertisements

3 thoughts on “CRM 2011 – The easy way not to select all columns in queries

  1. Georg Müller (@ggmueller) December 13, 2011 / 1:39 pm

    Depending on your usage, it might be also a good idea to use
    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.

    Like

    • Hosk December 13, 2011 / 1:46 pm

      That is an excellent suggestion.

      It cleans the code up nicely.

      Cheers

      Like

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