Modifica

Condividi tramite

FetchXml sample code

  • Articolo

To try using FetchXml with C#, you can use the OutputFetchRequest static methods in this article by adapting the respective quick start samples:

Note

See Paging Cookie examples for sample code to retrieve data in pages.

You can use the following OutputFetchRequest static method to test FetchXml queries in a console application.

The OutputFetchRequest method demonstrates how to use the FetchExpression class and the IOrganizationService.RetrieveMultiple method to return an EntityCollection containing the requested data.

The OutputFetchRequest method depends on the ConsoleTables NuGet package and requires that all entity or link-entity element attribute elements are included, which is a best practice.

/// <summary>
/// Renders the output of a query in a table for a console application
/// </summary>
/// <param name="service">The authenticated IOrganizationService instance to use.</param>
/// <param name="fetchXml">The FetchXml query to use.</param>
static void OutputFetchRequest(IOrganizationService service, string fetchXml)
{
    FetchExpression fetchExpression = new(fetchXml);

    //Retrieve the data
    EntityCollection entityCollection = service.RetrieveMultiple(query: fetchExpression);

    // Get column names from the FetchXml
    List<string> columns = GetColumns(fetchXml);

    // Create the table using https://www.nuget.org/packages/ConsoleTables/2.5.0
    var table = new ConsoleTables.ConsoleTable(columns.ToArray());

    // Add the rows of the table
    entityCollection.Entities.ToList().ForEach(entity =>
    {
        table.Rows.Add(GetRowValues(columns, entity).ToArray());
    });

    // Write the table to the console
    table.Write();

    /// <summary>
    /// Get a list of column names from the FetchXml
    /// </summary>
    /// <param name="fetchXml">The fetchXml query</param>
    /// <returns></returns>
    static List<string> GetColumns(string fetchXml)
    {
        XDocument fetchDoc = XDocument.Parse(fetchXml);

        XElement fetchElement = fetchDoc.Root;

        bool isAggregate = !(fetchElement?.Attributes("aggregate") == null &&
            (fetchElement?.Attribute("aggregate")?.Value == "true" ||
            fetchElement?.Attribute("aggregate")?.Value == "1"));

        // There can only be one entity element
        XElement entityElement = fetchElement.Element("entity");

        // Get the columns from the entity and any related link-entity elements
        return GetColumnsFromElement(element: entityElement, isAggregate: isAggregate);

    }

    /// <summary>
    /// Recursive function to get all column names from an entity or nested link-entity elements
    /// </summary>
    /// <param name="element">The entity or link-entity element</param>
    /// <param name="isAggregate">Whether the query uses aggregation</param>
    /// <param name="alias">The alias of the link-entity element</param>
    /// <returns></returns>
    static List<string> GetColumnsFromElement(XElement element, bool isAggregate, string? alias = null)
    {
        List<string> columns = new();

        // Get the attributes from the element
        foreach (XElement attribute in element.Elements("attribute"))
        {
            StringBuilder sb = new();


            // Prepend the alias for non-aggregate link-entities
            if (!string.IsNullOrWhiteSpace(alias) && !isAggregate)
            {
                sb.Append($"{alias}.");
            }

            // Use the attribute alias if there is one
            if (attribute.Attribute("alias") != null)
            {
                sb.Append(attribute.Attribute("alias")?.Value);
            }
            else
            {
                //Use the attribute name
                sb.Append(attribute.Attribute("name")?.Value);
            }

            columns.Add(sb.ToString());
        }

        // Whether the link-entity intersect attribute is true
        bool isIntersect = (element.Attribute("intersect") != null) &&
            (element.Attribute("intersect")?.Value == "true" ||
            element.Attribute("intersect")?.Value == "1");

        // The name of the element
        string elementName = element.Attribute("name")?.Value;
        // The type of element: 'entity' or 'link-entity'
        string elementType = element.Name.LocalName;

        // This method requires any non-intersect entity to have attributes
        if (columns.Count == 0 && !isIntersect)
        {
            // An non-intersect element with no attribute elements is technically valid,
            // but not supported by this method.
            throw new Exception($"No attribute elements in {elementType} element named '{elementName}'.");
        }

        // Look for any child link-entities
        foreach (XElement linkEntity in element.Elements("link-entity"))
        {
            // Use the alias if any
            string? linkEntityName;
            if (linkEntity.Attribute("alias") != null)
            {
                linkEntityName = linkEntity.Attribute("alias")?.Value;
            }
            else
            {
                linkEntityName = linkEntity.Attribute("name")?.Value;
            }

            // Recursive call for nested link-entity elements
            columns.AddRange(GetColumnsFromElement(linkEntity, isAggregate, linkEntityName));
        }

        return columns;
    }

    /// <summary>
    /// Returns the values of a row as strings
    /// </summary>
    /// <param name="columns">The names of the columns</param>
    /// <param name="entity">The entity with the data</param>
    /// <returns></returns>
    static List<string> GetRowValues(List<string> columns, Entity entity)
    {
        List<string> values = new();
        columns.ForEach(column =>
        {
            if (entity.Attributes.ContainsKey(column))
            {
                // Use the formatted value if it available
                if (entity.FormattedValues.ContainsKey(column) &&
                !string.IsNullOrWhiteSpace(entity.FormattedValues[column]))
                {
                    values.Add($"{entity.FormattedValues[column]}");
                }
                else
                {
                    // When an alias is used, the Aliased value must be converted
                    if (entity.Attributes[column] is AliasedValue aliasedValue)
                    {
                        // When an EntityReference doesn't have a Name, show the Id
                        if (aliasedValue.Value is EntityReference lookup &&
                        string.IsNullOrWhiteSpace(lookup.Name))
                        {
                            values.Add($"{lookup.Id:B}");
                        }
                        else
                        {
                            values.Add($"{aliasedValue.Value}");
                        }
                    }
                    else
                    {
                        // Use the simple attribute value
                        values.Add($"{entity.Attributes[column]}");
                    }
                }
            }
            // Null values are not in the Attributes collection
            else
            {
                values.Add("NULL");
            }

        });
        return values;
    }
}

Update SDK for .NET quick start sample

You can adapt the Quick Start: Execute an SDK for .NET request (C#) sample to test queries with the following steps:

  1. Install the ConsoleTables NuGet package

  2. Add the following using statements at the top of the program.cs file

    using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using System.Text;
using System.Xml.Linq;

  3. Copy and paste the OutputFetchRequest method below the Main method.

  4. Edit the Main method to set your query and use the OutputFetchRequest method.

        static void Main(string[] args)
    {
        using (ServiceClient serviceClient = new(connectionString))
        {
            if (serviceClient.IsReady)
            {
                //WhoAmIResponse response = 
                //    (WhoAmIResponse)serviceClient.Execute(new WhoAmIRequest());

                //Console.WriteLine("User ID is {0}.", response.UserId);

                string fetchQuery = @"<fetch top='5'>
                    <entity name='account'>
                    <attribute name='accountclassificationcode' />
                    <attribute name='createdby' />
                    <attribute name='createdon' />
                    <attribute name='name' />
                    </entity>
                </fetch>";

                OutputFetchRequest(serviceClient, fetchQuery);
            }
            else
            {
                Console.WriteLine(
                    "A web service connection was not established.");
            }
        }

        // Pause the console so it does not close.
        Console.WriteLine("Press the <Enter> key to exit.");
        Console.ReadLine();
    }

Read the following important information about using a connection string in application code.

Important

Microsoft recommends that you use the most secure authentication flow available. The authentication flow described in this article requires a very high degree of trust in the application, and carries risks that are not present in other flows. You should only use this flow when other more secure flows, such as managed identities, aren't viable.

When you run the program using the OutputFetchRequest method, the output should look like this:

 ---------------------------------------------------------------------------------------------------------
 | accountclassificationcode | createdby          | createdon          | name                             |
 ---------------------------------------------------------------------------------------------------------
 | Default Value             | FirstName LastName | 3/25/2023 10:42 AM | Litware, Inc. (sample)           |
 ---------------------------------------------------------------------------------------------------------
 | Default Value             | FirstName LastName | 3/25/2023 10:42 AM | Adventure Works (sample)         |
 ---------------------------------------------------------------------------------------------------------
 | Default Value             | FirstName LastName | 3/25/2023 10:42 AM | Fabrikam, Inc. (sample)          |
 ---------------------------------------------------------------------------------------------------------
 | Default Value             | FirstName LastName | 3/25/2023 10:42 AM | Blue Yonder Airlines (sample)    |
 ---------------------------------------------------------------------------------------------------------
 | Default Value             | FirstName LastName | 3/25/2023 10:42 AM | City Power & Light (sample)      |
 ---------------------------------------------------------------------------------------------------------

Query data using FetchXml Use FetchXml to retrieve data Sample: Use FetchXML with a paging cookie Sample: Use aggregation in FetchXML Sample: Convert queries between FetchXML and QueryExpression