1 out of 2 rated this helpful - Rate this topic

QueryExpression Class

[Applies to: Microsoft Dynamics CRM 2011 and Microsoft Dynamics CRM Online]

Contains a complex query expressed in a hierarchy of expressions.

Namespace: Microsoft.Xrm.Sdk.Query
Assembly: Microsoft.Xrm.Sdk (in Microsoft.Xrm.Sdk.dll)

'Usage

Syntax

C++

[DataContractAttribute(Name="QueryExpression", Namespace="http://schemas.microsoft.com/xrm/2011/Contracts")] 
public sealed class QueryExpression : QueryBase

JScript

Example


// Build the following SQL query using QueryExpression:
//
//        SELECT contact.fullname, contact.address1_telephone1
//        FROM contact
//            LEFT OUTER JOIN account
//                ON contact.parentcustomerid = account.accountid
//                AND
//                account.name = 'Litware, Inc.'
//        WHERE (contact.address1_stateorprovince = 'WA'
//        AND
//            contact.address1_city in ('Redmond', 'Bellevue', 'Kirkland', 'Seattle')
//        AND 
//            contact.address1_telephone1 like '(206)%'
//            OR
//            contact.address1_telephone1 like '(425)%'
//        AND
//            DATEDIFF(DAY, contact.createdon, GETDATE()) > 0
//        AND
//            DATEDIFF(DAY, contact.createdon, GETDATE()) < 30
//        AND
//            contact.emailaddress1 Not NULL
//               )

String fetchXml = @"<fetch mapping=""logical"" count=""50"" version=""1.0"">
                        <entity name=""contact"">
                            <attribute name=""address1_telephone1"" />
                            <attribute name=""contactid"" />
                            <attribute name=""firstname"" />
                            <attribute name=""lastname"" />
                            <filter>
                                <condition attribute=""address1_stateorprovince"" operator=""eq"" value=""WA"" />
                                <condition attribute=""address1_city"" operator=""in"">
                                    <value>Redmond</value>
                                    <value>Bellevue</value>
                                    <value>Kirkland</value>
                                    <value>Seattle</value>
                                </condition>
                                <condition attribute=""createdon"" operator=""last-x-days"" value=""30"" />
                                <condition attribute=""emailaddress1"" operator=""not-null"" />
                                <filter type=""or"">
                                    <condition attribute=""address1_telephone1"" operator=""like"" value=""(206)%"" />
                                    <condition attribute=""address1_telephone1"" operator=""like"" value=""(425)%"" />
                                </filter>
                            </filter>
                            <link-entity name=""account"" from=""accountid"" to=""parentcustomerid"">
                                <filter>
                                    <condition attribute=""name"" operator=""eq"" value=""Litware, Inc."" />
                                </filter>
                            </link-entity>
                        </entity>
                    </fetch>";

// Build fetch request and obtain results.
RetrieveMultipleRequest efr = new RetrieveMultipleRequest()
{
    Query = new FetchExpression(fetchXml)
};

EntityCollection entityResults = ((RetrieveMultipleResponse)_service.Execute(efr)).EntityCollection;
 

// Display the results.
Console.WriteLine("List all contacts matching specified parameters");
Console.WriteLine("===============================================");
foreach (var e in entityResults.Entities)
{
    Console.WriteLine("Contact ID: {0}", e.Id);
}


Console.WriteLine("<End of Listing>");
Console.WriteLine();


// Build the following SQL query using QueryExpression:
//
//        SELECT contact.fullname, contact.address1_telephone1
//        FROM contact
//            LEFT OUTER JOIN account
//                ON contact.parentcustomerid = account.accountid
//                AND
//                account.name = 'Litware, Inc.'
//        WHERE (contact.address1_stateorprovince = 'WA'
//        AND
//            contact.address1_city in ('Redmond', 'Bellevue', 'Kirkland', 'Seattle')
//        AND 
//            contact.address1_telephone1 like '(206)%'
//            OR
//            contact.address1_telephone1 like '(425)%'
//        AND
//            DATEDIFF(DAY, contact.createdon, GETDATE()) > 0
//        AND
//            DATEDIFF(DAY, contact.createdon, GETDATE()) < 30
//        AND
//            contact.emailaddress1 Not NULL
//               )

QueryExpression query = new QueryExpression()
{
    Distinct = false,
    EntityName = Contact.EntityLogicalName,
    ColumnSet = new ColumnSet("fullname", "address1_telephone1"),
    LinkEntities = 
    {
        new LinkEntity 
        {
            JoinOperator = JoinOperator.LeftOuter,
            LinkFromAttributeName = "parentcustomerid",
            LinkFromEntityName = Contact.EntityLogicalName,
            LinkToAttributeName = "accountid",
            LinkToEntityName = Account.EntityLogicalName,
            LinkCriteria = 
            {
                Conditions = 
                {
                    new ConditionExpression("name", ConditionOperator.Equal, "Litware, Inc.")
                }
            }
        }
    },
    Criteria =
    {
        Filters = 
        {
            new FilterExpression
            {
                FilterOperator = LogicalOperator.And,
                Conditions = 
                {
                    new ConditionExpression("address1_stateorprovince", ConditionOperator.Equal, "WA"),
                    new ConditionExpression("address1_city", ConditionOperator.In, new String[] {"Redmond", "Bellevue" , "Kirkland", "Seattle"}),
                    new ConditionExpression("createdon", ConditionOperator.LastXDays, 30),
                    new ConditionExpression("emailaddress1", ConditionOperator.NotNull)
                },
            },
            new FilterExpression
            {
                FilterOperator = LogicalOperator.Or,
                Conditions =
                {
                    new ConditionExpression("address1_telephone1", ConditionOperator.Like, "(206)%"),
                    new ConditionExpression("address1_telephone1", ConditionOperator.Like, "(425)%")
                }
            }
        }
    }
};

DataCollection<Entity> entityCollection = _service.RetrieveMultiple(query).Entities;

// Display the results.
Console.WriteLine("List all contacts matching specified parameters");
Console.WriteLine("===============================================");
foreach (Contact contact in entityCollection)
{
    Console.WriteLine("Contact ID: {0}", contact.Id);
    Console.WriteLine("Contact Name: {0}", contact.FullName);
    Console.WriteLine("Contact Phone: {0}", contact.Address1_Telephone1);
}
Console.WriteLine("<End of Listing>");
Console.WriteLine();


' Build the following SQL query using QueryExpression:
'
'        SELECT contact.fullname, contact.address1_telephone1
'        FROM contact
'            LEFT OUTER JOIN account
'                ON contact.parentcustomerid = account.accountid
'                AND
'                account.name = 'Litware, Inc.'
'        WHERE (contact.address1_stateorprovince = 'WA'
'        AND
'            contact.address1_city in ('Redmond', 'Bellevue', 'Kirkland', 'Seattle')
'        AND 
'            contact.address1_telephone1 like '(206)%'
'            OR
'            contact.address1_telephone1 like '(425)%'
'        AND
'            DATEDIFF(DAY, contact.createdon, GETDATE()) > 0
'        AND
'            DATEDIFF(DAY, contact.createdon, GETDATE()) < 30
'        AND
'            contact.emailaddress1 Not NULL
'               )

Dim query As New QueryExpression() With
    {
        .Distinct = False,
        .EntityName = Contact.EntityLogicalName,
        .ColumnSet = New ColumnSet("fullname", "address1_telephone1")
    }

Dim queryLink As New LinkEntity With
    {
        .LinkToEntityName = Account.EntityLogicalName,
        .LinkFromAttributeName = "parentcustomerid",
        .LinkToAttributeName = "accountid",
        .JoinOperator = JoinOperator.LeftOuter
    }
queryLink.LinkCriteria().AddCondition("name",
                                      ConditionOperator.Equal,
                                      "Litware, Inc.")
query.LinkEntities().Add(queryLink)
Dim queryCriteraFilter1 As New FilterExpression()
queryCriteraFilter1.FilterOperator = LogicalOperator.And
queryCriteraFilter1.AddCondition(
    New ConditionExpression("address1_stateorprovince",
                            ConditionOperator.Equal, "WA"))
queryCriteraFilter1.AddCondition(
    New ConditionExpression("address1_city",
                            ConditionOperator.In,
                            New String() {"Redmond",
                                          "Bellevue",
                                          "Kirkland",
                                          "Seattle"}))
queryCriteraFilter1.AddCondition(
    New ConditionExpression("createdon",
                            ConditionOperator.LastXDays,
                            30))
queryCriteraFilter1.AddCondition(
    New ConditionExpression("emailaddress1",
                            ConditionOperator.NotNull))
Dim queryCriteraFilter2 As New FilterExpression()
queryCriteraFilter2.FilterOperator = LogicalOperator.Or
queryCriteraFilter2.AddCondition(
    New ConditionExpression("address1_telephone1",
                            ConditionOperator.Like,
                            "(206)%"))
queryCriteraFilter2.AddCondition(
    New ConditionExpression("address1_telephone1",
                            ConditionOperator.Like,
                            "(425)%"))
query.Criteria.Filters.AddRange(queryCriteraFilter1,
                                queryCriteraFilter2)

Dim entityCollection As DataCollection(Of Entity) =
    _service.RetrieveMultiple(query).Entities

' Display the results.
Console.WriteLine("List all contacts matching specified parameters")
Console.WriteLine("===============================================")
For Each contact As Contact In entityCollection
    Console.WriteLine("Contact ID: {0}", contact.Id)
    Console.WriteLine("Contact Name: {0}", contact.FullName)
    Console.WriteLine("Contact Phone: {0}", contact.Address1_Telephone1)
Next contact
Console.WriteLine("<End of Listing>")
Console.WriteLine()