Edit

Share via

Use elastic tables using code

  • Article

This article describes how to use code to perform data operations on elastic tables.

Work with the session token

As was mentioned in Consistency level, you can achieve session-level consistency by passing the current session token with your requests. If you don't include the session token, the data that you retrieve might not include data changes that you've just made.

Getting the session token

The session token is available in the response of all write operations. Look for the x-ms-session-token value.

For any OrganizationResponse that performs a write operation, you can capture the x-ms-session-token value in the Results collection.

Note

DeleteResponse doesn't currently return the x-ms-session-token value. For more information, go to Known issue: No x-ms-session-token value is returned for delete operations.

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

Sending the session token

The way that you send the session token in a read operation depends on whether you're using the SDK or Web API.

When you perform an operation that retrieves data, set the SessionToken optional parameter on OrganizationRequest.

var request = new RetrieveRequest
{
    Target = new EntityReference("contoso_sensordata", sensordataid),
    ColumnSet = new ColumnSet("contoso_value"),
    ["partitionId"] = deviceId,
    ["SessionToken"] = sessionToken
};

Learn more about using optional parameters.

Specify PartitionId

As was mentioned in Partitioning and horizontal scaling, each elastic table has a partitionid column that you must use if you choose to apply a partitioning strategy for the table. Otherwise, don't set a value for the partitionid column.

Important

If you choose to use a partitioning strategy for your elastic table, all operations on that table or referring to records in that table MUST specify the partitionid column value to uniquely identify the record. There is no error thrown if partitionid is not specified in the lookup value of referencing table, but the lookup will fail to locate the record when you use it. You must document and enforce this requirement via code reviews to ensure that your data is consistent and partitionid is used appropriately for all the operations.

After you specify a non-null value for the partitionid column when you create a row, you must specify it when you perform any other data operation on that row. You can't change the value later.

If you don't set a partitionid value for a record when you create it, the partitionid column value remains null, and you can't change it later. In this case, you can identify records by using the primary key, just as you do with standard tables. Specifying a partitionid value isn't required.

Note

The examples in this article assume that you specify a non-null value for the partitionid column.

You can set the partitionid value in following ways when you perform various data operations.

Using the alternate key

As was mentioned in Alternate keys, every elastic table has an alternate key that is named KeyForNoSqlEntityWithPKPartitionId. This alternate key combines the primary key of the table with the partitionid column.

If you are using a partitioning strategy, you must specify an alternate key to specify the partitionid value when you use Retrieve, Update, or Delete operations, or when you set a lookup column for another table that refers to an elastic table record.

This example shows how you can use the alternate key to specify the partitionid value when you use Retrieve, Update, and Delete requests on elastic tables.

var keys = new KeyAttributeCollection() {
    { "contoso_sensordataid", sensordataid },
    { "partitionid", deviceId }
};

var entity = new Entity("contoso_sensordata", keys)

Using the partitionId parameter

Currently, you can use a partitionId parameter to specify the value of the partitionid column only for Retrieve and Delete operations. For more information, go to Known issue: The partitionId optional parameter isn't available for all messages.

Note

The partitionId parameter doesn't work with Create, Update, or Upsert messages, and it is ignored if it's sent.

request["partitionId"] = "device-001"

Using the partitionid column directly

For Create, Upsert, or Update operations, you can directly specify the value of the partitionid column.

This example shows how you can directly specify the value of the partitionid column in Entity when you perform a Create, Upsert, or Update operation.

var entity = new Entity("contoso_sensordata", sensordataid)
{
    Attributes = {
        { "partitionid", "device-001" }
    }
};

Create a record in an elastic table

This example creates a row in the contoso_SensorData table, where partitionid is set to deviceid. It also sets the ttlinseconds column to ensure that the row expires after one day (86,400 seconds) and is automatically deleted from Dataverse.

This example also captures the x-ms-session-token value that you can use when you retrieve the created record.

/// <summary>
/// Demonstrates creating a record with a partitionid and capturing the session token
/// </summary>
/// <param name="service">Authenticated client implementing the IOrganizationService interface</param>
/// <param name="deviceId">The value used as partitionid for the contoso_sensordata table. </param>
/// <param name="sessionToken">The current session token</param>
/// <returns>The Id of the created record.</returns>
public static Guid CreateExample(
    IOrganizationService service, 
    string deviceId, 
    ref string sessionToken )
{
    var entity = new Entity("contoso_sensordata")
    {
        Attributes =
            {
                { "contoso_deviceid", deviceId },
                { "contoso_sensortype", "Humidity" },
                { "contoso_value", 40 },
                { "contoso_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;
}

Use the x-ms-session-token value that is returned to set the SessionToken optional parameter when you retrieve the record that you created. Learn more about sending the session token.

Note

Deep insert is not supported with elastic tables. Each related record needs to be created independently. Only standard tables support deep insert

Setting the primary key value

If you don't specify a primary key value, Dataverse sets a primary key value for the record when you create it. Letting Dataverse set this value is the normal practice. You can specify the primary key value if you need to. For elastic tables, there's no performance benefit in letting Dataverse set the primary key value.

Dataverse stores primary key data in telemetry to help maintain the service. If you specify customized primary key values, don't use sensitive information in those values.

Elastic tables don't return an error when you create a record with a primary key value that isn't unique. By setting the primary key values with elastic tables, you can create records with that have the same primary key values and different partitionid values. However, this pattern isn't compatible with Power Apps. Don't create records with duplicate primary key values when people need to use this data in canvas or model-driven apps.

Update a record in an elastic table

This example updates the contoso_value and contoso_timestamp values of an existing row in the contoso_SensorData table by using the contoso_sensordataid primary key and partitionid = 'device-001'.

If you're using a partitioning strategy, the primary key and partitionid columns must uniquely identify an existing elastic table row. The partitionid value of an existing row can't be updated and is used only to uniquely identify the row to update.

This example uses the KeyForNoSqlEntityWithPKPartitionId alternate key to uniquely identify the record by using both the primary key and partitionid values. Learn more about alternate keys.

This example shows how to use the partitionid value as an alternate key.

/// <summary>
/// Demonstrates updating elastic table row with partitionid as alternate key.
/// </summary>
/// <param name="service">Authenticated client implementing the IOrganizationService interface</param>
/// <param name="sensordataid">The unique identifier of the contoso_sensordata table.</param>
/// <param name="deviceId">The value used as partitionid for the contoso_sensordata table. </param>
/// <param name="sessionToken">The current session token</param>
public static void UpdateExample(
    IOrganizationService service, 
    Guid sensordataid, 
    string deviceId, 
    ref string sessionToken)
{
    var keys = new KeyAttributeCollection() {
        { "contoso_sensordataid", sensordataid },
        { "partitionid", deviceId }
    };

    var entity = new Entity("contoso_sensordata", keys)
    {
        Attributes = {
            { "contoso_value", 60 },
            { "contoso_timestamp", DateTime.UtcNow }
        }
    };

    var request = new UpdateRequest { 
        Target = entity,
        ["SessionToken"] = sessionToken
    };

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

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

Learn more about using the Entity class to set alternate keys.

Retrieve a record in an elastic table

If the partitionid value was set when an elastic table record was created, you must use it together with the primary key value to uniquely identify a record.

If the partitionid wasn't set, you can retrieve the record in the usual way, by using only the primary key value.

There are two different ways to compose a request to retrieve a record by using the partitionid value.

This example uses the RetrieveRequest class. The Target property is set to an EntityReference that is created by using the constructor that accepts a KeyAttributeCollection to use the KeyForNoSqlEntityWithPKPartitionId alternate key. Learn more about using the EntityReference class with alternate keys.

public static void RetrieveExampleAlternateKey(IOrganizationService service, Guid sensorDataId, string deviceId) {

    var keys = new KeyAttributeCollection() {
        { "contoso_sensordataid", sensorDataId },
        { "partitionid", deviceId }
    };

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

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

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

    Console.WriteLine($"contoso_value: {response.Entity.GetAttributeValue<int>("contoso_value")}");
}

This example uses an optional parameter that is named partitionId on the RetrieveRequest class. Learn more about using optional parameters.

public static void RetrieveExampleOptionalParameter(IOrganizationService service, Guid sensorDataId, string deviceId)
{
    var entityReference = new EntityReference("contoso_sensordata", sensorDataId);

    var request = new RetrieveRequest
    {
        ColumnSet = new ColumnSet("contoso_value"),
        Target = entityReference,
        ["partitionId"] = deviceId
    };

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

    Console.WriteLine($"contoso_value: {response.Entity.GetAttributeValue<int>("contoso_value")}");
}

Query rows of an elastic table

When you query the rows of an elastic table, you get the best performance if you limit your query to a specific partition. Otherwise, your query returns data across all logical partitions, which isn't as fast.

Note

When you use this approach, the parameter must use the name partitionId (with a capital I) instead of partitionid (in all lowercase letters).

When you specify a filter this way, you don't have to specify the filter criteria on partitionid in your query in the usual manner (that is, by using FetchXML condition, QueryExpression ConditionExpression, or Web API $filter).

Specifying a filter on the partitionid value in the usual manner doesn't have the same performance benefits as specifying it through the partitionId parameter as shown in the following examples.

These examples retrieve the first 5,000 rows in the contoso_SensorData table that belong to the logical partition where partitionid = 'deviceid-001'.

public static EntityCollection RetrieveMultipleExample(IOrganizationService service)
{
    var request = new RetrieveMultipleRequest
    {
        Query = new QueryExpression("contoso_sensordata")
        {
            ColumnSet = new ColumnSet("contoso_value")
        },
        ["partitionId"] = "deviceid-001"
    };

    var response = (RetrieveMultipleResponse)service.Execute(request);
    return response.EntityCollection;
}

Elastic tables don't currently support returning related rows when a query is run. If you try to return related rows, Dataverse throws an error with code 0x80048d0b and the following message:

Link entities are not supported.

However, elastic tables do support returning related rows when a single row is retrieved.

Upsert a record in an elastic table

Important

Upsert operations with elastic tables differ from upsert operations with standard tables. Upsert operations are expected to contain the full payload and will overwrite any existing record data. They don't call the Create or Update messages. Learn more about elastic table upsert.

With elastic tables, if a record that has a given ID and partitionid doesn't exist, it's created. If it already exists, it's replaced.

This example upserts a row in the contoso_SensorData table with the specified id value and partitionid = deviceid-001.

/// <summary>
/// Demonstrates an upsert operation
/// </summary>
/// <param name="service">Authenticated client implementing the IOrganizationService interface</param>
/// <param name="id">The id of the record to update or create.</param>
/// <param name="sessionToken">The current session token</param>
/// <returns>Whether a record was created or not</returns>
public static bool UpsertExample(IOrganizationService service, Guid id, ref string sessionToken)
{
    var entity = new Entity("contoso_sensordata", id)
    {
        Attributes = {
            { "contoso_deviceid", "deviceid-001" },
            { "contoso_sensortype", "Humidity" },
            { "contoso_value", 60 },
            { "contoso_timestamp", DateTime.UtcNow },
            { "partitionid", "deviceid-001" },
            { "ttlinseconds", 86400 }
        }
    };

    var request = new UpsertRequest
    {
        Target = entity,
        ["SessionToken"] = sessionToken
    };

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

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

    return response.RecordCreated;
}

Delete a record in an elastic table

When you delete a record that uses a custom partitionid value, you must include the partitionid value.

This example deletes a row in the contoso_SensorData table with the specified ID and partitionid = 'deviceid-001'.

/// <summary>
/// Demonstrates a delete operation
/// </summary>
/// <param name="service">Authenticated client implementing the IOrganizationService interface</param>
/// <param name="sensordataid">The unique identifier of the contoso_sensordata table.</param>
/// <param name="sessionToken">The current session token</param>
public static void DeleteExample(
    IOrganizationService service, 
    Guid sensordataid, 
    ref string sessionToken)
{
    var request = new DeleteRequest
    {
        Target = new EntityReference("contoso_sensordata", sensordataid),
        ["partitionId"] = "deviceid-001"
    };

    var response = service.Execute(request);
    // Known issue: Value not currently being returned.
    // sessionToken = response.Results["x-ms-session-token"].ToString();
}

Associate elastic table records

When a table record refers to an elastic table record where the partitionid column value is null, you can associate a record in that table to a elastic table record just like standard records. Refer SDK for .NET, or the Web API.

When a table record refers to an elastic table record which has partitionid column value set, you must include the partitionid column value of the elastic table record when you set the lookup column of the referencing table. You can do this by including the value as an alternate key.

As described in Partitionid value column on referencing table, when a one-to-many relationship is created and the elastic table is the referenced table, a string column and a lookup column is created on the referencing table. The string column stores the partitionid value of the referenced elastic table record.

You can set both the lookup and the string column values with their respective values by:

  • Using an alternate key reference to set only the lookup
  • Setting the two column values together in one update

How you do this depends on whether you are using the SDK for .NET or Web API

This example associates an elastic contoso_SensorData table record with the specified ID and partitionid to an existing account record by setting the lookup column with an alternate key:

/// <summary>
/// Demonstrates associate to elastic table operation.
/// </summary>
/// <param name="service">Authenticated client implementing the IOrganizationService interface</param>
/// <param name="sensordataId">The unique identifier of the contoso_sensordata table.</param>
/// <param name="deviceId">The deviceId. PartitionId of sensor data record.</param>
/// <param name="accountId">The unique identifier of the account record to update.</param>
public static void AssociateAccountAlternateKeyExample(
    IOrganizationService service,
    Guid sensordataId,
    string deviceId,
    Guid accountId)
{
    var keys = new KeyAttributeCollection() {
        { "contoso_sensordataid", sensordataId },
        { "partitionid", deviceId }
    };

    var sensorDataReference = new EntityReference("contoso_sensordata", keys);

    Entity account = new("account", accountId)
    {
        Attributes =
            {
                {"contoso_sensordata", sensorDataReference}
            }
    };

    service.Update(account);
}

This example does the same thing, but sets both of the columns together:

/// <summary>
/// Demonstrates associate to elastic table operation.
/// </summary>
/// <param name="service">Authenticated client implementing the IOrganizationService interface</param>
/// <param name="sensordataId">The unique identifier of the contoso_sensordata table.</param>
/// <param name="deviceId">The deviceId. PartitionId of sensor data record.</param>
/// <param name="deviceId">The unique identifier of the account record to update.</param>
public static void AssociateAccountBothColumnsExample(
    IOrganizationService service,
    Guid sensordataId,
    string deviceId,
    Guid accountId)
{
    Entity account = new("account", accountId) {
        Attributes =
            {
                {"contoso_sensordata", new EntityReference("contoso_sensordata", sensordataId)},
                {"contoso_sensordatapid", deviceId }
            }
    };

    service.Update(account);
}

Finally, this example shows using the AssociateRequest to associate a collection of account records to the same contoso_SensorData table record in one operation.

/// <summary>
/// Demonstrates associating multiple accounts to a contoso_sensordata elastic table record
/// </summary>
/// <param name="service">Authenticated client implementing the IOrganizationService interface</param>
/// <param name="sensordataId">The unique identifier of the contoso_sensordata table.</param>
/// <param name="deviceId">The deviceId. PartitionId of sensor data record.</param>
/// <param name="relatedEntities">A collection of references to account records to associate to the contoso_sensordata elastic table record</param>
public static void AssociateMultipleElasticTableExample(
   IOrganizationService service,
   Guid sensordataId,
   string deviceId,
   EntityReferenceCollection relatedEntities)
{

   // The keys to the elastic table record including the partitionid
   var keys = new KeyAttributeCollection() {
         { "contoso_sensordataid", sensordataId },
         { "partitionid", deviceId }
   };

   AssociateRequest request = new()
   {
         Target = new EntityReference("contoso_sensordata", keys),
         Relationship = new Relationship("contoso_SensorData_contoso_SensorData_Acc"),
         RelatedEntities = relatedEntities
   };

   service.Execute(request);
}

Bulk operations with elastic tables

Often, applications must ingest a large amount of data into Dataverse in a short time. Dataverse has a group of messages that are designed to achieve high throughput. With elastic tables, the throughput can be even higher.

Bulk operations are optimized for performance when multiple write operations are performed on the same table by taking a batch of rows as input in a single write operation. Learn more about bulk Operation messages (preview).

Use CreateMultiple with elastic tables

You can use the CreateMultiple message with either the SDK for .NET or Web API.

This example uses the CreateMultipleRequest class to create multiple rows in the contoso_SensorData elastic table.

public static Guid CreateMultiple(IOrganizationService service)
{
    string tableLogicalName = "contoso_sensordata";

    List<Microsoft.Xrm.Sdk.Entity> entityList = new List<Microsoft.Xrm.Sdk.Entity>
    {      
        new Microsoft.Xrm.Sdk.Entity(tableLogicalName)
        {
            Attributes =
            {
                { "contoso_deviceId", "deviceid-001" },
                { "contoso_sensortype", "Humidity" },
                { "contoso_value", "40" },
                { "contoso_timestamp", "2023-05-01Z05:00:00"},
                { "partitionid", "deviceid-001" },
                { "ttlinseconds", 86400 }
            }
        },
        new Microsoft.Xrm.Sdk.Entity(tableLogicalName)
        {
            Attributes =
            {
                { "contoso_deviceId", "deviceid-002" },
                { "contoso_sensortype", "Humidity" },
                { "contoso_value", "10" },
                { "contoso_timestamp", "2023-05-01Z09:30:00"},
                { "partitionid", "deviceid-002" },
                { "ttlinseconds", 86400 }
            }
        }
        new Microsoft.Xrm.Sdk.Entity(tableLogicalName)
        {
            Attributes =
            {
                { "contoso_deviceId", "deviceid-002" },
                { "contoso_sensortype", "Pressure" },
                { "contoso_value", "20" },
                { "contoso_timestamp", "2023-05-01Z07:20:00"},
                { "partitionid", "deviceid-002" },
                { "ttlinseconds", 86400 }
            }
        }
    };

    // Create an EntityCollection populated with the list of entities.
    EntityCollection entities = new(entityList)
    {
        EntityName = tableLogicalName
    };

    // Use CreateMultipleRequest
    CreateMultipleRequest createMultipleRequest = new()
    {
        Targets = entities,
    };
    return service.Execute(request);
}

Use UpdateMultiple with elastic tables

You can use the UpdateMultiple message with either the SDK for .NET or Web API.

This example uses the UpdateMultipleRequest class to update multiple rows of the contoso_SensorData elastic table. These updates set the contoso_value column.

public static Guid UpdateMultiple(IOrganizationService service)
{
    string tableLogicalName = "contoso_sensordata";

    List<Microsoft.Xrm.Sdk.Entity> entityList = new List<Microsoft.Xrm.Sdk.Entity>
    {
        new Microsoft.Xrm.Sdk.Entity(tableLogicalName)
        {
            Attributes =
            {
                { "contoso_value", "45" },
                { "partitionid", "deviceid-001" }
            }
        },
        new Microsoft.Xrm.Sdk.Entity(tableLogicalName)
        {
            Attributes =
            {
                { "contoso_value", "15" },
                { "partitionid", "deviceid-002" }
            }
        }
        new Microsoft.Xrm.Sdk.Entity(tableLogicalName)
        {
            Attributes =
            {
                { "contoso_value", "25" },
                { "partitionid", "deviceid-002" }
            }
        }
    };

    // Create an EntityCollection populated with the list of entities.
    EntityCollection entities = new(entityList)
    {
        EntityName = tableLogicalName
    };

    // Use UpdateMultipleRequest
    UpdateMultipleRequest updateMultipleRequest = new()
    {
        Targets = entities,
    };
    return service.Execute(request);
}

Use DeleteMultiple with elastic tables

You can use the DeleteMultiple message with either the SDK for .NET or Web API.

Note

With the SDK, you must use the OrganizationRequest class because the SDK doesn't currently have a DeleteMultipleRequest class. Learn more about using messages with the SDK for .NET.

The following DeleteMultipleExample static method uses the DeleteMultiple message with the OrganizationRequest class to delete multiple rows from the contoso_SensorData elastic table. The alternate key is used to include the partitionid value to uniquely identify the rows.

public static void DeleteMultipleExample(IOrganizationService service)
{
    string tableLogicalName = "contoso_sensordata";

    List<EntityReference> entityReferences = new() {
        {
            new EntityReference(logicalName: tableLogicalName,
                keyAttributeCollection: new KeyAttributeCollection
                {
                    { "contoso_sensordataid", "3f56361a-b210-4a74-8708-3c664038fa41" },
                    { "partitionid", "deviceid-001" }
                })
        },
        { new EntityReference(logicalName: tableLogicalName,
            keyAttributeCollection: new KeyAttributeCollection
            {
                { "contoso_sensordataid", "e682715b-1bba-415e-b2bc-de9327308423" },
                { "partitionid", "deviceid-002" }
            })
        }
    };

    OrganizationRequest request = new(requestName:"DeleteMultiple")
    {
        Parameters = {
            {"Targets", new EntityReferenceCollection(entityReferences)}
        }
    };

    service.Execute(request);
}

Next steps

Learn how to use code to create and query JavaScript Object Notation (JSON) data in JSON columns in elastic tables.

Query JSON columns

See also

Elastic tables for developers
Create elastic tables using code
Query JSON columns in elastic tables
Elastic table sample code
Bulk operation messages (preview)