Use elastic tables using code (preview)
[This topic is pre-release documentation and is subject to change.]
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
};
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.
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.
You can use this alternate key to specify the partitionid
value when you use Retrieve
, Update
, or Delete
operations.
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.
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;
}
Return related rows in a query
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();
}
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.
See also
Elastic tables for developers (preview)
Create elastic tables using code (preview)
Query JSON columns in elastic tables (preview)
Elastic table sample code (preview)
Bulk operation messages (preview)
Feedback
Submit and view feedback for