Edit

Create elastic tables using code

  • Article

By using Power Apps, you can create and edit elastic tables without writing code.

However, it's sometimes useful to use code to create and update table definitions. The following examples show how to use the Dataverse SDK for .NET and Web API to create a new elastic table that has the schema name contoso_SensorData. To create an elastic table through code, use the EntityMetadata.TableType property with a value of Elastic. If you don't set TableType, the default value Standard is used, and a standard table is created.

public static CreateEntityResponse CreateElasticTable(IOrganizationService service)
{
    var request = new CreateEntityRequest
    {
        // Define table properties
        Entity = new EntityMetadata
        {
            SchemaName = "contoso_SensorData",
            DisplayName = new Label("SensorData", 1033),
            DisplayCollectionName = new Label("SensorData", 1033),
            Description = new Label("Stores IoT data emitted from devices", 1033),
            OwnershipType = OwnershipTypes.UserOwned,
            TableType = "Elastic",
            IsActivity = false,
            CanCreateCharts = new Microsoft.Xrm.Sdk.BooleanManagedProperty(false)
        },

        // Define the primary attribute for the entity
        PrimaryAttribute = new StringAttributeMetadata
        {
            SchemaName = "contoso_SensorType",
            RequiredLevel = new AttributeRequiredLevelManagedProperty(AttributeRequiredLevel.None),
            MaxLength = 100,
            FormatName = StringFormatName.Text,
            DisplayName = new Label("Sensor Type", 1033),
            Description = new Label("Type of sensor emitting data", 1033)
        }

    };
    return (CreateEntityResponse)service.Execute(request);
}

CreateEntityResponse has these properties:

  • AttributeId: The ID of the contoso_SensorType primary name string column.
  • EntityId: The ID of the contoso_SensorData table.

Learn more about creating custom tables using the SDK for .NET.

Adding columns

By using Power Apps, you can create columns in elastic tables without writing code.

You can also create columns by using the SDK or Web API. However, there are limits on the types of columns that you can add. Currently, you can't add the following types of columns:

  • Money (MoneyAttributeMetadata)
  • MultiSelectPicklist (MultiSelectPicklistAttributeMetadata)
  • State (StateAttributeMetadata)
  • Status (StatusAttributeMetadata)
  • File (FileAttributeMetadata)
  • Image (ImageAttributeMetadata)
  • Calculated, Rollup, or Formula Columns

Elastic tables support string columns that store JavaScript Object Notation (JSON) data.

Create a column with JSON format

This example creates a contoso_SensorValue string column with JSON format in the contoso_SensorData elastic table. For cases where a large amount of JSON data must be stored, you can use the MemoAttributeMetadata column type with JSON format instead of using the StringAttributeMetadata column type.

This function creates a StringAttributeMetadata column by using the CreateAttributeRequest class.

public static Guid CreateJsonAttribute(IOrganizationService service)
{
    var request = new CreateAttributeRequest
    {
        EntityName = "contoso_sensordata",
        Attribute = new StringAttributeMetadata
        {
            SchemaName = "contoso_EnergyConsumption",
            RequiredLevel = new AttributeRequiredLevelManagedProperty(AttributeRequiredLevel.None),
            MaxLength = 1000,
            FormatName = StringFormatName.Json,
            DisplayName = new Label("Energy Consumption", 1033),
            Description = new Label("Contains information about energy consumed by the IoT devices", 1033)
        },
        SolutionUniqueName  = "examplesolution"
    };

    var response = (CreateAttributeResponse)service.Execute(request);

    return response.AttributeId;
}

For more information, go to Add a String column to the custom table.

Alternate keys

You can't create custom alternate keys for elastic tables.

Each elastic table is created with one alternate key that uses these values:

  • Display Name: Entity key for NoSql Entity that contains PrimaryKey and PartitionId attributes
  • Name: KeyForNoSqlEntityWithPKPartitionId
  • LogicalName: keyfornosqlentitywithpkpartitionid

This alternate key has the key values <table primary key name> and partitionid.

If you must reference a record where a partitionid value is set, you can reference it by using this alternate key.

Learn how to use an alternate key to reference a record.

Adding relationships

Dataverse currently doesn't support creating many-to-many relationships with elastic tables.

One-to-many relationships are supported for elastic tables with the following limitations:

  • Cascading isn't supported. Cascading behavior must be set to Cascade.None when the relationship is created.

  • Formatted values for lookup columns aren't returned when the following conditions are true:

    • The table that is retrieved is a standard table, and the lookup refers to an elastic table.
    • You're using a custom elastic table partitionid value. In other words, the partitionid value is set to something other than the default value (the primary key value of the elastic table row). Learn how to choose a partitionid value.

Elastic tables support one-to-many relationships, and related rows can be retrieved when a record is retrieved. Related records can't be included in a query. Learn how to return related rows in a query.

Next steps

Use elastic tables using code

See also

Elastic tables for developers
Query JSON columns in elastic tables
Use bulk operation messages
Elastic table sample code