Редагувати

Поділитися через

Select columns using FetchXml

  • Стаття

As described in Query data using FetchXml, start your query by selecting a table using the entity element.

Use the attribute element to select the columns to return with your query. For example:

<fetch>
  <entity name='account'>
    <attribute name='accountclassificationcode' />
    <attribute name='createdby' />
    <attribute name='createdon' />
    <attribute name='name' />
  </entity>
</fetch>

This query returns the AccountClassificationCode, CreatedBy, CreatedOn, and Name columns of the first 5,000 rows from the Account table. If you need more rows than this, or you want to iterate through smaller sets of data, learn how to page results using FetchXml.

For each attribute you want returned, add an attribute element and set the name attribute value to the LogicalName of the column.

Use the attribute element to select the columns for the entity of your query and any tables joined using the link-entity element. Learn how to join tables using FetchXml.

Important

We strongly discourage returning all columns in a table. Returning all columns will make your applications run slower and may cause timeout errors. You should specify the minimum number of columns to retrieve with your data. If you do not specify columns, or you use the all-attributes element, data for all columns is returned.

Formatted values

The typed data returned may not be suitable to display in your application. Formatted values are string values returned with the request that you can display in your application.

Let's look at the results without using formatted values first. These functions use the ConsoleTables NuGet package to show the table in a console application.

This SimpleOutput method only accesses values in the Entity.Attributes collection.

/// <summary>
/// Output the entity attribute values
/// </summary>
/// <param name="service">The authenticated IOrganizationService instance</param>
static void SimpleOutput(IOrganizationService service) {

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

    FetchExpression fetchExpression = new(fetchXml);

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

    var table = new ConsoleTables.ConsoleTable("classificationcode", "createdby", "createdon", "name");

    foreach (var entity in entityCollection.Entities ) {

        var accountclassificationcode = entity.GetAttributeValue<OptionSetValue>("accountclassificationcode").Value;
        var createdby = entity.GetAttributeValue<EntityReference>("createdby").Name;
        var createdon = entity.GetAttributeValue<DateTime>("createdon");
        var name = entity.GetAttributeValue<string>("name");

        table.AddRow(accountclassificationcode, createdby, createdon, name);

    }
    table.Write();
}

Output:

 ----------------------------------------------------------------------------------------------
 | classificationcode | createdby           | createdon             | name                    |
 ----------------------------------------------------------------------------------------------
 | 1                  | FirstName LastName  | 8/13/2023 10:30:08 PM | Fourth Coffee (sample)  |
 ----------------------------------------------------------------------------------------------
 | 1                  | FirstName LastName  | 8/13/2023 10:30:10 PM | Litware, Inc. (sample)  |
 ----------------------------------------------------------------------------------------------
 | 1                  | FirstName LastName  | 8/13/2023 10:30:10 PM | Adventure Works (sample)|
 ----------------------------------------------------------------------------------------------

These values may not be the user-friendly values you need to display in an application.

  • The accountclassificationcode choice column returns the integer value.
  • The SDK reference to createdby must use the EntityReference.Name property
  • The Web API returns the _createdby_value Lookup property that has the GUID value for the createdby lookup column.

To get the user-friendly values you want, you need to access formatted values that can be returned by Dataverse.

How you get these values depends on whether you use the SDK for .NET or Web API.

The OutputFetchRequest sample method described in FetchXml Sample code uses data from the Entity.FormattedValues collection, so the results of the query look like this:

 --------------------------------------------------------------------------------------------------
 | accountclassificationcode | createdby           | createdon          | name                    |
 --------------------------------------------------------------------------------------------------
 | Default Value             | FirstName LastName  | 8/13/2023 10:30 PM | Fourth Coffee (sample)  |
 --------------------------------------------------------------------------------------------------
 | Default Value             | FirstName LastName  | 8/13/2023 10:30 PM | Litware, Inc. (sample)  |
 --------------------------------------------------------------------------------------------------
 | Default Value             | FirstName LastName  | 8/13/2023 10:30 PM | Adventure Works (sample)|
 --------------------------------------------------------------------------------------------------

This GetRowValues method extracts a list of string values for a record from the Entity.FormattedValues when they are available.

/// <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;
}

Learn more about formatted values:

Column aliases

Column aliases are typically used for aggregate operations, but they also work for simple select operations, so we can introduce them here.

Use the attribute alias attribute to specify a unique column name for the results returned.

Each column returned must have a unique name. By default, the column names returned for the table of your query are the column LogicalName values. All column logical names are unique for each table, so there can't be any duplicate names within that set.

When you use a link-entity element to join tables, the default column names follow this naming convention: {Linked table LogicalName}.{Column LogicalName}. This prevents any duplicate column names. You can override this by using a unique alias. You can also set an alias value for the link-entity representing the joined table.

The behavior you see when using column aliases depends on whether you are using the SDK for .NET or Web API.

This SimpleAliasOutput method uses aliases rather than the logical names of the columns. Because of this, the results are returned as AliasedValue. To access the value of complex types like OptionSetValue or EntityReference, you have to cast the value.

This method uses the ConsoleTables NuGet package .

/// <summary>
/// Output the entity attribute values with aliases
/// </summary>
/// <param name="service">The authenticated IOrganizaitonService instance</param>
static void SimpleAliasOutput(IOrganizationService service)
{
    string fetchXml = @"<fetch top='3'>
            <entity name='account'>
              <attribute name='accountclassificationcode' alias='code' />
              <attribute name='createdby' alias='whocreated' />
              <attribute name='createdon' alias='whencreated' />
              <attribute name='name' alias='companyname' />
            </entity>
          </fetch>";

    FetchExpression fetchExpression = new(fetchXml);

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

    var table = new ConsoleTables.ConsoleTable("code", "whocreated", "whencreated", "companyname");

    foreach (var entity in entityCollection.Entities)
    {

        var code = ((OptionSetValue)entity.GetAttributeValue<AliasedValue>("code").Value).Value;
        var whocreated = ((EntityReference)entity.GetAttributeValue<AliasedValue>("whocreated").Value).Name;
        var whencreated = entity.GetAttributeValue<AliasedValue>("whencreated").Value;
        var companyname = entity.GetAttributeValue<AliasedValue>("companyname").Value;

        table.AddRow(code, whocreated, createdon, companyname);

    }
    table.Write();
}

Output:

 ----------------------------------------------------------------------------------
 | code | whocreated           | whencreated           | companyname              |
 ----------------------------------------------------------------------------------
 | 1    | FirstName LastName   | 8/13/2023 10:30:08 PM | Fourth Coffee (sample)   |
 ----------------------------------------------------------------------------------
 | 1    | FirstName LastName   | 8/13/2023 10:30:10 PM | Litware, Inc. (sample)   |
 ----------------------------------------------------------------------------------
 | 1    | FirstName LastName   | 8/13/2023 10:30:10 PM | Adventure Works (sample) |
 ----------------------------------------------------------------------------------

Note

The AliasedValue class has two properties that tell you the original EntityLogicalName and AttributeLogicalName if you need them.

Next steps

Learn how to join tables.

Join tables