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)
  • 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 (null). 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.

Partitionid value column on referencing table

When you create a many-to-one relationship on a table that refers to an elastic table, a lookup column is created on the referencing table as you would expect.

At the same time, a string column is created that follows this naming convention <lookup name>pid. This column stores the partitionid value for the related elastic table record.

The <lookup name>pid column value is set automatically when you use the elastic table alternate key to set the lookup column. Learn to associate elastic table records

If you are not using a partitioning strategy for your elastic table, the value for this <lookup name>pid column is null, and you shouldn't change it after the record is created.

If you are using a partitioning strategy for your elastic table, and you want to retrieve the related elastic table record, you can't rely on the value of the lookup column alone. You must also include the partitionid value from the <lookup name>pid column to uniquely identify the related table. Learn more about partitioning and horizontal scaling

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