Using AddQueryOption to write oData query in Silverlight (CRM 2011)

Hi,

The easiest way to write oData query is to make use of this wonderful tool developed by Rhett Clinton:-

http://crm2011odatatool.codeplex.com/

The query generated by tool can be used in Jscript. They can also be used to write DataServiceQuery in Silverlight project using AddQueryOption.

Here are few examples: –

• The query to get the subject and city of all the lead records

http://servername/orgName/xrmservices/2011/OrganizationData.svc/LeadSet?

$select=Address1_City,Subject

Now while writing DataServiceQuery simple set the first parameter of AddQueryOption as $select and the value part of it as the second parameter.

var queryLead = context.LeadSet.AddQueryOption("$select", "Address1_City,Subject") as DataServiceQuery<Lead>;

• The query to get the subject and city of all the lead records where subject contains string ‘test’

http://sname/orgname/xrmservices/2011/OrganizationData.svc/LeadSet?

$select=Address1_City,FullName&$filter=substringof(‘test’,Subject)

Here we will add two query option one for select and other for filter criteria

var queryLead = context.LeadSet
.AddQueryOption("$select", "Address1_City,Subject")
.AddQueryOption("$filter", "substringof('test',Subject)")
as DataServiceQuery<Lead>;

• The query to get the subject of all the lead records along with owner’s Business Unit Name (i.e. related entity)

http://sname/orgname/xrmservices/2011/OrganizationData.svc/LeadSet?

$select=Subject,lead_owning_user/BusinessUnitId&$expand=lead_owning_user

Here we are making use of expand to get the data from the related entity

var queryLead = context.LeadSet
.AddQueryOption("$select", "Subject,lead_owning_user/BusinessUnitId")
.AddQueryOption("$expand", "lead_owning_user")
as DataServiceQuery<Lead>;

To run the query and loop through the records returned we can make use of following code:-

DataServiceCollection<Lead> leads = new DataServiceCollection<Lead>();
leads.LoadCompleted += (s, ea) =>
{

foreach (Lead lead in leads)
{
MessageBox.Show(lead.Subject);
MessageBox.Show(lead.lead_owning_user.BusinessUnitId.Name);
}

};

leads.LoadAsync(queryLead);

Hope this helps.