FetchXml sample code
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:
Install the ConsoleTables NuGet package
Add the following using statements at the top of the
program.cs
fileusing Microsoft.Xrm.Sdk; using Microsoft.Xrm.Sdk.Query; using System.Text; using System.Xml.Linq;
Copy and paste the
OutputFetchRequest
method below theMain
method.Edit the
Main
method to set your query and use theOutputFetchRequest
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(); }
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) |
---------------------------------------------------------------------------------------------------------
Related articles
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
Atsauksmes
https://aka.ms/ContentUserFeedback.
Drīzumā: 2024. gada laikā mēs pakāpeniski pārtrauksim izmantot “GitHub problēmas” kā atsauksmju par saturu mehānismu un aizstāsim to ar jaunu atsauksmju sistēmu. Papildinformāciju skatiet:Iesniegt un skatīt atsauksmes par