We can use the following methods to retrieve data , whil integratingCRM with the help of CRM SDK.
The
querying methods are
- using QueryExpression
- using Fetch XML
QueryExpression
:
We
use this type of querying method when we using direct selection . For
example, if we need to get all data from account entity, then we use
simple QueryExpression as below
QueryExpression query
= new QueryExpression
{
EntityName
= "account",
ColumnSet
= new ColumnSet(true)
};
EntityCollection entities
= service.RetrieveMultiple(query);
This
is pretty direct method , also you can link different entities
using LinkEntity() class
Syntax:
LinkEntity(string linkFromEntityName, string linkToEntityName, string linkFromAttributeName, string linkToAttributeName, JoinOperator joinOperator)
Example
:
QueryExpression query
= new QueryExpression
{
EntityName
= "account",
ColumnSet
= new ColumnSet(true)
};
var accountLinkEntity
= new LinkEntity("account", "systemuser", "ownerid", "systemuserid", JoinOperator.Inner);
accountLinkEntity.Columns.AddColumns("firstname");
query.LinkEntities.Add(accountLinkEntity);
EntityCollection entities
= service.RetrieveMultiple(query);
Fetch
XML
The
Link entity and query expression is suitable only in direct join
operations .
But
in case of Aggregate result , those query expression is very
difficult to implement.
Note : The aggregate function SUM,AVG,MIN and MAX do not work for date field in CRM 2011 . From CRM 2013 onward MIN and MAX will works for Date field.
Let us look into an example ,
Let us look into an example ,
Suppose
we need to get the sum of all sale amount for different products in
sales history entity. Then, we need product wise aggregate . This is
impossible using query expression. We have an alternative for
this , that is fetch xml.
Case : 1
string groupby
= @"
<fetch
distinct='false' mapping='logical' aggregate='true' >
<entity
name='[Entity_Name]'>
<attribute
name='[Aggregate_Attribute_Name]' alias='[alias_Name]'
aggregate='sum' />
<attribute
name='[Groupby_Attribute_Name]' alias='[alias_Name]' groupby='true'
/>
</entity>
</fetch>
";
EntityCollection groupby_result
= service.RetrieveMultiple(new FetchExpression(groupby));
In
the Above example,
- Entity_Name -> Name of the entity
- Aggregate_Attribute_name -> Its is the name of the attribute which's aggregate to be calculated , also we need to specify the type of operation aggregate='sum'.
- Groupby_Attribute_Name -> Name of the attribute whose sum grouped based on this attribute groupby='true'.
Case
: 2
If
we need to add a filtration in that fetch XML . Add filter Tag
in that fetch XML.Please check the below XML
<fetch
distinct='false' mapping='logical' aggregate='true' >
<entity
name='[Entity_Name]'>
<attribute
name='[Aggregate_Attribute_Name]' alias='[alias_Name]'
aggregate='sum' />
<attribute
name='[Groupby_Attribute_Name]' alias='[alias_Name]' groupby='true'
/>
<filter
type='and'>
<condition
attribute='[Filter_Attribute_Name]' operator='eq'
value='[Filter_Value]' />
</filter>
</entity>
</fetch>
Case
: 3
If
we need to group based on an attribute which is in another entity and
it is linked to our aggregate Entity. Add link entity tag .Please
check the below XML
<fetch
distinct='false' mapping='logical' aggregate='true' >
<entity
name='[Entity_Name]'>
<attribute
name='[Aggregate_Attribute_Name]' alias='[alias_Name]'
aggregate='sum' />
<link-entity
name='[LinkEnity_Name]' from='[Mapping_LinkEnity_Attribute]'
to='[Mapping_Enity_Attribute]'>
<attribute
name='[Groupby_Attribute_Name]' alias='Country' groupby='true' />
</link-entity>
<filter
type='and'>
<condition
attribute='[Filter_Attribute_Name]' operator='eq'
value='[Filter_Value]' />
</filter>
</entity>
</fetch>
Case
: 4
If
we need to filter the data with in a particular date rage (fiscal
period) .Please check the below XML
<fetch
distinct='false' mapping='logical' aggregate='true' >
<entity
name='[Entity_Name]'>
<attribute
name='[Aggregate_Attribute_Name]' alias='[alias_Name]'
aggregate='sum' />
<link-entity
name='[LinkEnity_Name]' from='[Mapping_LinkEnity_Attribute]'
to='[Mapping_Enity_Attribute]'>
<attribute
name='[Groupby_Attribute_Name]' alias='Country' groupby='true' />
</link-entity>
<filter
type='and'>
<condition
attribute='[Filter_Attribute_Name]' operator='eq'
value='[Filter_Value]' />
<condition
attribute = '[Date_Attiribute_Name]' operator='between'>
<value>" +
FromDate + @"</value>
<value>" +
Todate + @"</value>
</condition>
</filter>
</entity>
</fetch>
Case
: 5
If
we need to filter the data with in a certain data (like 'in'
condition in SQL operation ) .Please check the below XML
<entity
name='[Entity_Name]'>
<attribute
name='[Aggregate_Attribute_Name]' alias='[alias_Name]'
aggregate='sum' />
<link-entity
name='[LinkEnity_Name]' from='[Mapping_LinkEnity_Attribute]'
to='[Mapping_Enity_Attribute]'>
<attribute
name='[Groupby_Attribute_Name]' alias='Country' groupby='true' />
</link-entity>
<filter
type='and'>
<condition
attribute='[Filter_Attribute_Name]' operator='eq'
value='[Filter_Value]' />
<condition
attribute = '[Filter_Attiribute_Name]' operator='in'>
<value>A</value>
<value>B</value>
.
.
.
</condition>
</filter>
</entity>
</fetch>
Case
: 6
If
we need to Group-by using year field in a date attribute .Please
check the below XML
<fetch
distinct='false' mapping='logical' aggregate='true' >
<entity
name='[Entity_Name]'>
<attribute
name='[Aggregate_Attribute_Name]' alias='[alias_Name]'
aggregate='sum' />
<attribute
name='[Date_Attribute_Name]' alias='Year' groupby='true'
dategrouping='year' />
<filter
type='and'>
<condition
attribute='[Filter_Attribute_Name]' operator='eq'
value='[Filter_Value]' />
</filter>
</entity>
</fetch>
Case
: 7
If
we need to Add Multiple Group-by .Please check the below XML
<fetch
distinct='false' mapping='logical' aggregate='true' >
<entity
name='[Entity_Name]'>
<attribute
name='[Aggregate_Attribute_Name]' alias='[alias_Name]'
aggregate='sum' />
<attribute
name='[Attribute_Name1]' alias='Name1' groupby='true'
dategrouping='year' />
<attribute
name='[Attribute_Name2]' alias='Name2' groupby='true' />
<filter
type='and'>
<condition
attribute='[Filter_Attribute_Name]' operator='eq'
value='[Filter_Value]' />
</filter>
</entity>
</fetch>
If we want to group by month then use dategrouping='month'
If we want to filter two years value then, use operator ='between' because 'in-fiscal-year' will take only one year values.
Case : 8
If we need to average value .Please check the below XMLIf we want to filter two years value then, use operator ='between' because 'in-fiscal-year' will take only one year values.
Case : 8
<fetch
distinct='false' mapping='logical' aggregate='true'>
<entity
name='[Entity_Name]'>
<attribute
name='[Avrage_Attribute_Name]' alias='[alias_Name]' aggregate='avg'
/>
</entity>
</fetch>
Case : 9
<fetch distinct='false' mapping='logical' aggregate='true'>
<entity name='[Entity_Name]'>
<attribute name='[Count_Attribute_Name]' alias='[alias_Name]' aggregate='count'/>
</entity>
</fetch>
nice article sir ..
ReplyDeleteThank you for your valuable feed back
Delete