In today’s dynamic business environment, the ability to adapt quickly to changing data requirements is crucial for building robust applications. Dataverse for Power Apps offers a powerful solution for creating flexible data models through the concept of elastic tables. In this comprehensive guide, we’ll explore the elastic table concept in Dataverse and provide sample code for implementing CRUD (Create, Read, Update, Delete) operations.

Understanding Elastic Tables in Dataverse

What are Elastic Tables?

• Elastic tables, also known as dynamic or schema-less tables, are tables that can adapt to changing data requirements without the need for predefined schemas.
• Unlike traditional relational databases where the schema is fixed, elastic tables allow for the addition or modification of fields on-the-fly.

Think about a scenario, where you can go for Elastic Tables.

Use elastic tables when:

• Your data might be unstructured or semi-structured, or if your data model might constantly change.
• You need automatic horizontal scaling.
• You need to handle a high volume of read and write requests.

Use standard tables when:

• Your application requires strong consistency.
• Your application requires relational modeling and needs transactional capability across tables and during plugin execution stages.
• Your application requires complex joins.

Elastic tables support the JavaScript Object Notation (JSON) format for text columns. These columns can be used to store schema-less arbitrary JSON according to application needs.

Time to live (TTL) policies ensure that you’re always working with the most up-to-date and accurate information, while optimizing resources and reducing risk. The TTL live value is set in seconds on a record, and it’s interpreted as a delta from the time that a record was last modified.

Elastic tables enable you to store and query data with varying structures, without the need for predefined schemas or migrations. There’s no need to write custom code to map the imported data into a fixed schema. Elastic tables enable you to store and query data with varying structures, without the need for predefined schemas or migrations. There’s no need to write custom code to map the imported data into a fixed schema.

Points to keep in mind when utilizing elastic tables:

• Elastic tables excel in handling a high volume of requests efficiently. However, there are trade-offs to consider:
• Multi-record transactions are not supported, meaning that simultaneous write operations within a single request are not transactional. For instance, errors in a synchronous plug-in step triggered by a Create message won’t roll back the created record in Dataverse.
• Strong consistency is maintained only within a logical session. Changes to a row may not be immediately visible outside the session context.
• Filters on related tables are not supported when creating views, executing advanced finds, or any query via API. To address this limitation, denormalizing columns from related tables into the main table is recommended. For example, if filtering based on related table columns is common, like filtering customers by their associated address city, denormalizing the city column into the customer table allows for such filtering.

Create and Bulk Create Records in Elastc Tables Sample code.

When you create a record via code to be loaded into an elastic table, you need to specify the partitionid. A quick example is the following:

var entity = new Entity("sd_ManufacturingDeviceInfo")
    {
        Attributes =
            {
               { "sd_machineId", "MACHINE01" },
               { "sd_prodOrder", "PROD01" },
               { "sd_item", "ITEM01" },
	             { "sd_qtyProduced", 24 },
               { "sd_timestamp", DateTime.UtcNow},
               { "partitionid", "MACHINE01" }
            }
    };

    var request = new CreateRequest { 
        Target = entity
    };

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

The partitionid attribute is also mandatory if you want to read a record (otherwise you will have an error).

var keys = new KeyAttributeCollection() {
        { "sd_machineId", "MACHINE01" },
        { "partitionid", "MACHINE01" }
    };

    var entityReference = new EntityReference("sd_ManufacturingDeviceData", keys);

    var request = new RetrieveRequest { 
        ColumnSet = new ColumnSet("sd_item"),
        Target = entityReference
    };

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

Elastic tables are extremely faster on bulk operations.

Sample code for create each record in loop.

for (var i = 0; i < 500; i++)
{
  var entity = new Entity("sd_ManufacturingDeviceData")
  {
    Attributes =
            {
               { "sd_machineId", "MACHINE01" },
               { "sd_prodOrder", "PROD01" },
               { "sd_item", "ITEM01" },
		   { "sd_qtyProduced", 23 },
               { "sd_timestamp", DateTime.UtcNow},
               { "partitionid", "MACHINE01" }
            }
  };
  var request = new CreateRequest { 
        Target = entity
    };

    var response = (CreateResponse)service.Execute(request);
}

Bulk Operation sample code

var entities = new EntityCollection();
entities.EntityName = "sd_ManufacturingDeviceData";
for (var i = 0; i < 500; i++)
{
  var entity = new Entity("sd_ManufacturingDeviceData")
  {
    Attributes =
            {
               { "sd_machineId", "MACHINE01" },
               { "sd_prodOrder", "PROD01" },
               { "sd_item", "ITEM01" },
		   { "sd_qtyProduced", 23 },
               { "sd_timestamp", DateTime.UtcNow},
               { "partitionid", "MACHINE01" }
            }
  };
  entities.Entities.Add(entity);
}
service.Execute(new CreateMultipleRequest
{
    Targets = entities
});

Create a record in Elastic Table named as soft_sensordata with JSON Columns Sample code.

public static Guid CreateWithJsonInfo(
    IOrganizationService service, 
    string deviceId, 
    ref string sessionToken)
{
    var entity = new Entity("soft_sensordata")
    {
        Attributes =
        {
            { "soft_deviceid", deviceId },
            { "soft_sensortype", "Humidity" },
            { "soft_value", 40 },
            { "soft_energyconsumption", "{ \"power\": 0.55, \"powerUnit\":\"kWh\", \"voltage\": 2, \"voltageUnit\": \"kV\" }",
            { "soft_timestamp", DateTime.UtcNow},
            { "partitionid", deviceId },
            { "ttlinseconds", 86400  }  // 86400  seconds in a day
        }
    };

    var request = new CreateRequest
    {
        Target = entity
    };

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

    // Capture the session token
    sessionToken = response.Results["x-ms-session-token"].ToString();

    return response.id;
}

Query JSON columns in elastic tables

This example runs a query on the soft_SensorData elastic table to filter for all rows where the energyconsumption.power value is more than 5.

All table columns can be queried by using a c.props prefix on the schema name of the columns. In this prefix, c is an alias or shorthand notation for the elastic table that is being queried. For example, the contoso_deviceid column in the soft_sensordata table can be referenced in the Cosmos SQL query by using c.props.soft_deviceid.

public static List<QueryResult> QueryJsonAttribute(IOrganizationService service)
{
    StringBuilder query = new();
    query.Append("select c.props.soft_deviceid as deviceId, ");
    query.Append("c.props.soft_timestamp as timestamp, ");
    query.Append("c.props.soft_energyconsumption.power as power ");
    query.Append("from c where c.props.contoso_sensortype='Humidity' ");
    query.Append("and c.props.soft_energyconsumption.power > 5");

    var request = new OrganizationRequest("ExecuteCosmosSqlQuery")
    {
        Parameters = {
            { "EntityLogicalName","soft_sensordata" },
            { "QueryText", query.ToString() }
        }
    };

    OrganizationResponse response = service.Execute(request);

    // Deserialized query result into a class with expected schema.
    Entity result = (Entity)response.Results["Result"];
    return JsonConvert.DeserializeObject<List<QueryResult>>(result["Result"].ToString());
}

public class QueryResult
{
    [JsonProperty("deviceId")]
    public string DeviceId {get;set;}
    [JsonProperty("timestamp")]
    public DateTime Timestamp {get;set;}
    [JsonProperty("power")]
    public int Power {get;set;}
}

Bulk Operation is much faster than normal create in loop.

Hope it helps.