Share via


QueryExpression Class

Applies To: Microsoft Dynamics CRM 2013, 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)

Syntax

'Declaration
<DataContractAttribute(Name:="QueryExpression", Namespace:="https://schemas.microsoft.com/xrm/2011/Contracts")> _
Public NotInheritable Class QueryExpression
    Inherits QueryBase
[DataContractAttribute(Name="QueryExpression", Namespace="https://schemas.microsoft.com/xrm/2011/Contracts")] 
public sealed class QueryExpression : QueryBase

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

' Build fetch request and obtain results.
Dim efr As New RetrieveMultipleRequest() With {.Query = New FetchExpression(fetchXml)}

Dim entityResults As EntityCollection = (CType(_service.Execute(efr), RetrieveMultipleResponse)).EntityCollection


' Display the results.
Console.WriteLine("List all contacts matching specified parameters")
Console.WriteLine("===============================================")
For Each e In entityResults.Entities
    Console.WriteLine("Contact ID: {0}", e.Id)
Next e


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()

Remarks

A query expression can be expressed as FetchXML or as a hierarchy of expressions.

Inheritance Hierarchy

System.Object
   Microsoft.Xrm.Sdk.Query.QueryBase
    Microsoft.Xrm.Sdk.Query.QueryExpression

Thread Safety

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

Platforms

Development Platforms

Windows Server 2008, Windows Server 2012, Windows 7 (All Versions), Windows 8 (All Versions)

Target Platforms

Windows Server 2008, ,Windows Server 2012, ,Windows 7 (All Versions),

Change History

See Also

Reference

QueryExpression Members
Microsoft.Xrm.Sdk.Query Namespace

Other Resources

Build Queries with QueryExpression
Sample: Retrieve Multiple with Query Expression
Sample: Query Connection Roles by Entity Type Code (Early Bound)

Send comments about this topic to Microsoft.
© 2013 Microsoft Corporation. All rights reserved.