Link Entity, Query Expression and FetchXml Wizard


Let say we need to make use of CrmService to give us the name of the opportunity where owner’s fullname=’someName’;

Name is an attribute of Opportunity Entity.
FullName is an attribute of SystemUser Entity.

So we need to make use of LinkEntity class over here to link from opportunity entity to systemuser entity.

Now let’s use queryExpression for the same.

 

But plzzzz don’t write it yourself, use FetchXmlBuilder to write it for you.
Plzz understand it and download it from this link

http://www.stunnware.com/crm2/topic.aspx?id=FindingData6
(The best site for all the CrmDevelopers)

This is how the queryExpression code will look like

 

QueryExpression query = new QueryExpression();

query.EntityName = “opportunity”;

ColumnSet columns = new ColumnSet();
columns.Attributes = new string[] { “name” };
query.ColumnSet = columns;

LinkEntity linkEntity1 = new LinkEntity();
linkEntity1.JoinOperator = JoinOperator.Natural;
linkEntity1.LinkFromEntityName = “opportunity”;
linkEntity1.LinkFromAttributeName = “owninguser”;
linkEntity1.LinkToEntityName = “systemuser”;
linkEntity1.LinkToAttributeName = “systemuserid”;

linkEntity1.LinkCriteria = new FilterExpression();
linkEntity1.LinkCriteria.FilterOperator = LogicalOperator.And;

ConditionExpression condition1 = new ConditionExpression();
condition1.AttributeName = “fullname”;
condition1.Operator = ConditionOperator.Equal;
condition1.Values = new object[] { “Nishant Rana” };

linkEntity1.LinkCriteria.Conditions = new ConditionExpression[] { condition1 };

query.LinkEntities = new LinkEntity[] { linkEntity1 };

Now say we have a requirement where we need

The name of all the opportunity along with the fullname of the owner.

Let’s try doing it with a queryExpression class and using the FetchXmlBuilder.

This is the code which we’ll get

………….

LinkEntity linkEntity1 = new LinkEntity();

linkEntity1.JoinOperator = JoinOperator.Natural;

linkEntity1.LinkFromEntityName = “opportunity”;

linkEntity1.LinkFromAttributeName = “owninguser”;

linkEntity1.LinkToEntityName = “systemuser”;

linkEntity1.LinkToAttributeName = “systemuserid”;

//You have specified columns for this link-entity. This is not supported in query expressions

 

This means it’s not possible using queryExpression

So in this case we need to make use of FetchXml to fetch use the fullname of the owning user.

The fetch xml looks like this

string fetchXml = @”
<fetch mapping=””logical”” count=””50″”>
<entity name=””opportunity””>
<attribute name=””name”” />
<link-entity name=””systemuser”” from=””systemuserid”” to=””owninguser””>
<attribute name=””fullname”” />
</link-entity>
</entity>
</fetch>”;

// Finally the code

CrmService service = new CrmService();
service.Credentials = System.Net.CredentialCache.DefaultCredentials;
string fetchXml = @”
<fetch mapping=””logical”” count=””50″”>
<entity name=””opportunity””>
<attribute name=””name”” />
<link-entity name=””systemuser”” from=””systemuserid”” to=””owninguser””>
<attribute name=””fullname”” />
</link-entity>
</entity>
</fetch>”;
String result = service.Fetch(fetchXml);

 

Now the only problem is we need to parse the xml on our own.

We can use something like this

if this is the result we are receiving

<resultset morerecords=”0″><result><new_id>GP0677</new_id></result></resultset>

to fetch the new_id we can do this

XmlDocument doc = new XmlDocument();
doc.LoadXml(result);
XmlNodeList xnodlist=doc.GetElementsByTagName(“
new_id”);
XmlNode xnodRoot=xnodlist.Item(0);
string val=xnodRoot.InnerText;

 

 

Bye.

 

Author: Nishant Rana

I love working in and sharing everything about Microsoft.NET technology !

3 thoughts on “Link Entity, Query Expression and FetchXml Wizard”

  1. Hi,
    I am not able to retrieve multiple columns of opportunity which are related to particular CompetiorID.
    There is a N:N relationship between competitor and opportunity. Something is missing in my code.
    Public Function getOpportunityByCompetitorID(ByVal CompetitorID As String, ByVal ConnectionProperties As ServiceConnector) As ArrayList

    Dim objCrmService As New CrmService()
    Dim objServiceManager As New ServiceManager
    Dim arrlstOpportunity As New ArrayList
    Dim objopportunty As opportunity

    ‘ Create the ConditionExpression.
    Dim cndOpportunity As New ConditionExpression()

    ‘ Create the FilterExpression.
    Dim flterExp As New FilterExpression()

    ‘ Create the QueryExpression object.
    Dim queryExp As New QueryExpression()

    ‘ Create the request object.
    Dim retrieveMultiReq As New RetrieveMultipleRequest

    ‘ Create Response Object
    Dim retrieveMultiRes As RetrieveMultipleResponse

    ‘Create Link entities for N:N relationship
    Dim linkEntity1 As New LinkEntity()
    Dim linkEntity2 As New LinkEntity()
    Dim filter As New FilterExpression()

    Try
    objCrmService = objServiceManager.getCrmService(ConnectionProperties)

    ‘ Set the properties of the QueryExpression object.
    queryExp.EntityName = EntityName.opportunity.ToString()
    ‘ Get All column
    queryExp.ColumnSet = New AllColumns()

    ‘Create the link entity from opportuntiycompetiorsto competitor.

    linkEntity1.LinkFromEntityName = “competitor”
    linkEntity1.LinkFromAttributeName = “competitorid”
    linkEntity1.LinkToEntityName = “opportunitycompetitors”
    linkEntity1.LinkToAttributeName = “competitorid”
    linkEntity2.JoinOperator = JoinOperator.Inner

    linkEntity2.LinkFromEntityName = “opportunitycompetitors”
    linkEntity2.LinkFromAttributeName = “opportunityid”
    linkEntity2.LinkToEntityName = “opportunity”
    linkEntity2.LinkToAttributeName = “opportunityid”

    cndOpportunity.AttributeName = “Competitorid”
    cndOpportunity.Operator = ConditionOperator.Equal
    cndOpportunity.Values = New Object() {CompetitorID}

    ‘Add the condition to the link
    linkEntity2.LinkCriteria = New FilterExpression()
    linkEntity2.LinkCriteria.Conditions = New ConditionExpression() {cndOpportunity}

    ‘Add the links to the query
    linkEntity1.LinkEntities = New LinkEntity() {linkEntity2}
    queryExp.LinkEntities = New LinkEntity() {linkEntity1}

    ‘ Set the properties of the request object.
    retrieveMultiReq.Query = queryExp

    ‘ Execute the request.
    retrieveMultiRes = CType(objCrmService.Execute(retrieveMultiReq), RetrieveMultipleResponse)

    For Each objOpportunityBE As BusinessEntity In retrieveMultiRes.BusinessEntityCollection.BusinessEntities

    objopportunty = New opportunity
    objopportunty = CType(objOpportunityBE, opportunity)

    arrlstOpportunity.Add(objopportunty)

    objopportunty = Nothing

    Next

    Return arrlstOpportunity

    Catch ex As Exception
    Throw New Exception(ex.ToString)
    Finally
    arrlstOpportunity = Nothing
    retrieveMultiRes = Nothing
    retrieveMultiReq = Nothing
    queryExp = Nothing
    flterExp = Nothing
    cndOpportunity = Nothing
    objServiceManager = Nothing
    objCrmService = Nothing
    End Try

    End Function

    please help me out.
    Bhavika.2711@gmail.com

    Like

Please share your thoughts

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.