QueryExpressions: What's your preference?

(0) Share

Report

Posted on by Community Member

Hi,

Our software developers are starting to use CRM SDK a lot.

As I am reviewing code, I notice that some developers like to write QueryExpressions by constructor and others prefer an object initializer.

By Constructor:

QueryExpression qe = new QueryExpression("account");
qe.ColumnSet = new ColumnSet("accountid", "name");

By Object Initializer:

QueryExpression qe = new QueryExpression()
{
      EntityName = "account",
      ColumnSet = new ColumnSet("accountid", "name")
};

Advanced By Constructor Query:

private QueryExpression QueryByConstructor()
{
    QueryExpression qe = new QueryExpression("contact");
    qe.Distinct = true;
    qe.ColumnSet = new ColumnSet(_contactColumnSet);
    qe.Criteria.Conditions.Add(new ConditionExpression("CFIWCPointsTotal", ConditionOperator.GreaterThan, 0));
    qe.Criteria.Conditions.Add(new ConditionExpression("numberofchildren", ConditionOperator.NotNull));

    LinkEntity leSystemUser = new LinkEntity();
    leSystemUser.EntityAlias = "su";
    leSystemUser.Columns = new ColumnSet(_systemUserColumnSet);
    leSystemUser.LinkFromEntityName = "contact";
    leSystemUser.LinkFromAttributeName = "ownerid";
    leSystemUser.LinkToEntityName = "systemuser";
    leSystemUser.LinkToAttributeName = "systemuserid";
    leSystemUser.JoinOperator = JoinOperator.Inner;

    LinkEntity leOpportunity = new LinkEntity();
    leOpportunity.EntityAlias = "o";
    leOpportunity.Columns = new ColumnSet(_opportunityColumnSet);
    leOpportunity.LinkFromEntityName = "new_opportunityterm";
    leOpportunity.LinkFromAttributeName = "new_opportunityid";
    leOpportunity.LinkToEntityName = "opportunity";
    leOpportunity.LinkToAttributeName = "opportunityid";
    leOpportunity.JoinOperator = JoinOperator.Inner;
    leOpportunity.LinkCriteria.AddCondition(new ConditionExpression("statecode", ConditionOperator.In, new int[] { 0, 1 }));
    leOpportunity.LinkCriteria.AddCondition(new ConditionExpression("CFDEquipmentAcceptDate", ConditionOperator.NotNull));

    LinkEntity leOpportunityTerm = new LinkEntity();
    leOpportunityTerm.EntityAlias = "ot";
    leOpportunityTerm.LinkFromEntityName = "new_opportunitytermvendor";
    leOpportunityTerm.LinkFromAttributeName = "new_opportunitytermid";
    leOpportunityTerm.LinkToEntityName = "new_opportunityterm";
    leOpportunityTerm.LinkToAttributeName = "new_opportunitytermid";
    leOpportunityTerm.JoinOperator = JoinOperator.Inner;
    leOpportunityTerm.LinkCriteria.AddCondition(new ConditionExpression("new_isinlcw", ConditionOperator.Equal, true));
    leOpportunity.LinkEntities.Add(leOpportunity);

    LinkEntity leOpportunityTermVendor = new LinkEntity();
    leOpportunityTermVendor.EntityAlias = "otv";
    leOpportunityTermVendor.LinkFromEntityName = "contact";
    leOpportunityTermVendor.LinkFromAttributeName = "contactid";
    leOpportunityTermVendor.LinkToEntityName = "new_opportunitytermvendor";
    leOpportunityTermVendor.LinkToAttributeName = "new_contactid";
    leOpportunityTermVendor.JoinOperator = JoinOperator.Inner;
    leOpportunityTermVendor.LinkEntities.Add(leOpportunityTerm);

    qe.LinkEntities.Add(leSystemUser);
    qe.LinkEntities.Add(leOpportunityTermVendor);

    return qe;
}

Advanced By Object Initializer Query:

private QueryExpression QueryByObjectInitializer()
{
    QueryExpression qe = new QueryExpression()
    {

        EntityName = "contact",
        Distinct = true,
        ColumnSet = new ColumnSet(_contactColumnSet),
        Criteria =
        {
            Conditions =
            {
                new ConditionExpression("CFIWCPointsTotal", ConditionOperator.GreaterThan, 0),
                new ConditionExpression("numberofchildren", ConditionOperator.NotNull),
            }
        },
        LinkEntities =
        {
            new LinkEntity
            {

                EntityAlias = "su",
                Columns = new ColumnSet(_systemUserColumnSet),
                LinkFromEntityName = "contact",
                LinkFromAttributeName = "ownerid",
                LinkToEntityName = "systemuser",
                LinkToAttributeName = "systemuserid",
                JoinOperator = JoinOperator.Inner,
            },
            new LinkEntity
            {
                EntityAlias = "otv",
                LinkFromEntityName = "contact",
                LinkFromAttributeName = "contactid",
                LinkToEntityName = "new_opportunitytermvendor",
                LinkToAttributeName = "new_contactid",
                JoinOperator = JoinOperator.Inner,
                LinkEntities =
                {
                    new LinkEntity
                    {
                        EntityAlias = "ot",
                        LinkFromEntityName = "new_opportunitytermvendor",
                        LinkFromAttributeName = "new_opportunitytermid",
                        LinkToEntityName = "new_opportunityterm",
                        LinkToAttributeName = "new_opportunitytermid",
                        JoinOperator = JoinOperator.Inner,
                        LinkCriteria =
                        {
                            Conditions =
                            {
                                new ConditionExpression("new_isinlcw", ConditionOperator.Equal, true)
                            }
                        },
                        LinkEntities =
                        {
                            new LinkEntity
                            {
                                EntityAlias = "o",
                                Columns = new ColumnSet(_opportunityColumnSet),
                                LinkFromEntityName = "new_opportunityterm",
                                LinkFromAttributeName = "new_opportunityid",
                                LinkToEntityName = "opportunity",
                                LinkToAttributeName = "opportunityid",
                                JoinOperator = JoinOperator.Inner,
                                LinkCriteria =
                                {
                                    Conditions =
                                    {
                                        new ConditionExpression("statecode", ConditionOperator.In, new int[] { 0, 1 }),
                                        new ConditionExpression("CFDEquipmentAcceptDate", ConditionOperator.NotNull)
                                    }
                                }
                            }
                        }
                    }
                }
            },
        }
    };

    return qe;
}

Advanced FetchXML Query:

private string FetchXMLQuery()
{
    return @"
        <fetch version='2017.11.06' top='100' distinct='true' >
            <entity name='contact' >
            <all-attributes/>
            <filter>
                <condition attribute='CFIWCPointsTotal' operator='gt' value='0' />
                <condition attribute='numberofchildren' operator='not-null' />
            </filter>
            <link-entity name='systemuser' from='systemuserid' to='ownerid' link-type='inner' alias='su' />
            <link-entity name='new_opportunitytermvendor' from='new_contactid' to='contactid' link-type='inner' alias='otv' >
                <link-entity name='new_opportunityterm' from='new_opportunitytermid' to='new_opportunitytermid' link-type='inner' alias='ot' >
                <filter>
                    <condition attribute='new_isinlcw' operator='eq' value='1' />
                </filter>
                <link-entity name='opportunity' from='opportunityid' to='new_opportunityid' link-type='inner' alias='o' >
                    <filter>
                    <condition attribute='statecode' operator='in' >
                        <value>0</value>
                        <value>1</value>
                    </condition>
                    <condition attribute='CFDEquipmentAcceptDate' operator='not-null' />
                    </filter>
                </link-entity>
                </link-entity>
            </link-entity>
            </entity>
        </fetch>";
}

I would like for all of our developers to use either one way or another. I am leaning towards the object initializer route since it's easier to read. Imagine if you have 2 or 3 levels of linked entities. It's much easier to read as opposed to the constructor method.

Questions:

Are there any guidelines for writing QueryExpressions or is it based on preference?
Which way do you prefer (constructor, object initializer) and why?

Any other tips would be appreciated.

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

RaviKashyap 55,410 Moderator on at

Like (0)

Report

Hi,

To answer your questions-

1. It is based on preference. I would certainly say nesting should be the preference as it is easy to read (as you said)

2. I used to write query expressions but from past year or so I changed my style to use fetch. The reason for me switching from Query Expression to fetch is because it can be defined easily using advance find. Consider a scenario where you are not getting the desired results, if you use fetch, you can simply run that fetch to test the results using any open source FetchXMLbuilder, fetchxmltester or your own fetch tester.

Just a smal code sample here-

================================

private List<Entity> GetTeamApplicants(IOrganizationService service, Guid teammembershipID)

{

var fetchXml = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>

<entity name='msdyn_projectteammembersignup'>

<attribute name='msdyn_bookableresource' />

<filter type='and'>

<condition attribute='msdyn_teammembership' operator='eq' value='{0}' />

</filter>

</entity>

</fetch>";

fetchXml = string.Format(fetchXml, teammembershipID);

return service.RetrieveMultiple(new FetchExpression(fetchXml)).Entities.ToList<Entity>();

}

=========================

Hope this helps.

Was this reply helpful? Yes No
Suggested answer

Aric Levin - MVP 30,190 Moderator on at

Like (0)

Report

It is a matter of preference. As you mentioned the nesting makes it easier to read.

Personally I like nesting better, however there are particular circumstances where I have chosen the inline.

If you are already making standards, you should also consider if other options are applicable as well, such as Query By Attribute.

Hope this helps.

Was this reply helpful? Yes No
Suggested answer

Guido Preite 54,086 Moderator on at

Like (2)

Report

first better to define the names:

what you call "inline" is "by constructor"

what you call "nesting" is "by object initializer"

Regarding the style, try to apply some conditions in your query when you use the "nesting" or the fetchxml style (like for example adding a Condition based on some parameters you received) and ask your developers which one is more convenient to use.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Ravi, thanks for suggesting FetchXML! Like you say, it's very simple to build the query via advanced find and it's very simple to test it.

As far as editing and updating a FetchXML query, do you simply take the existing one, place it into one of the FetchXML builders (http://fxb.xrmtoolbox.com/) and edit from there? I also noticed that the tool that I linked to can generate C# Query Expression. That's pretty cool!

-----------

Aric, thanks for suggesting QueryByAttribute. I have never used this, but it sounds like if you have a scenario of a simple non-linked entity query, you can return records by using less lines of code. Please correct me if I'm wrong.

-----------

Guido, thank you for correcting my nomenclature. I will go ahead and ask my developers to see which way is more convenient. Personally, adding a condition via object initializer route adds more lines of code, but it's simple to as long as the condition is simple like equaling to an Id. Now let's say we need to run some other complicated query to bring back that Id. Now, we could simply have a method that gets the Id.

-----------

I appreciate all the answers. I will be exploring your suggestions shortly.

Was this reply helpful? Yes No
RaviKashyap 55,410 Moderator on at

Like (1)

Report

Hi,

Regarding fetchXML, I generally create the query using advance find, download the fetchxml and the open it in notepad++ to modify if required.

Was this reply helpful? Yes No
Aric Levin - MVP 30,190 Moderator on at

Like (0)

Report

Hi Anatoliy,

That is correct.

Also, one more note to add to Ravi's fetchXml.

After you embed the fetchXml in your code, include it within a string.Format(fetchXml, params), this way it is easy to change ids to parameters inside your code, and not have to deal with formatting it.

For example if you need to replace an id in the fetchXml, replace it with {0}, and in your string.Format add the Guid in the params area: string.Format(fetchXml, contactId).

Good luck

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I did see that in your first example. I saw one of my coworker's using string.Format and was wondering what that was. Very helpful indeed!

Was this reply helpful? Yes No