Tablestore .NET SDK filters - Tablestore - Alibaba Cloud Documentation Center

You can use filters in Tablestore to refine query results on the server based on specified conditions. This topic describes how to use filters in the .NET SDK.

Prerequisites

Initialize a Tablestore client

Filter types

Tablestore provides the following two types of filters.

RelationalCondition: Determines whether the value of a single attribute column meets a specified condition.
CompositeCondition: Combines multiple conditions to filter data.

RelationalCondition

public class RelationalCondition : IColumnCondition

Parameters

Parameter	Type	Description
Operator (Required)	CompareOperator	The relational operator. Supported operators include EQUAL, NOT_EQUAL, GREATER_THAN, GREATER_EQUAL, LESS_THAN, and LESS_EQUAL.
ColumnName (Required)	string	The name of the attribute column to evaluate.
ColumnValue (Required)	ColumnValue	The value to compare against.
PassIfMissing (Optional)	bool	Controls whether to return a row that is missing the specified attribute column. The default value of `true` returns the row.
LatestVersionsOnly (Optional)	bool	Specifies whether to evaluate only the latest data version of an attribute column, even if multiple versions exist. The default is `true`.

Example

This example performs a range query for row data where the primary key is in the [row1, row3) range. The query then uses a filter to return only rows where the col1 attribute column is val1.

try
{
    // Set the start primary key for the query.
    PrimaryKey inclusiveStartPrimaryKey = new PrimaryKey()
    {
        { "id", new ColumnValue("row1") }
    };
    // Set the end primary key for the query. The end key is exclusive.
    PrimaryKey exclusiveEndPrimaryKey = new PrimaryKey()
    {
        { "id", new ColumnValue("row3") }
    };
    // Create a filter with the condition: col1 == "val1".
    RelationalCondition relationalCondition = new RelationalCondition("col1", CompareOperator.EQUAL, new ColumnValue("val1"));

    // Call the GetRange method to read row data.
    GetRangeRequest getRangeRequest = new GetRangeRequest("test_table", GetRangeDirection.Forward, inclusiveStartPrimaryKey, exclusiveEndPrimaryKey, null, null, relationalCondition);
    GetRangeResponse getRangeResponse = client.GetRange(getRangeRequest);

    // Process the response.
    Console.WriteLine($"RequestId: {getRangeResponse.RequestID}");
    Console.WriteLine($"Read CU Cost: {getRangeResponse.ConsumedCapacityUnit.Read}");
    Console.WriteLine($"Write CU Cost: {getRangeResponse.ConsumedCapacityUnit.Write}");
    Console.WriteLine("Row Data: ");
    foreach (Row row in getRangeResponse.RowDataList)
    {
        Console.WriteLine(row);
    }
}
catch (Exception ex)
{
    Console.WriteLine($"Get Range failed, exception: {ex.Message}");
}

To exclude a row that is missing the specified attribute column, set PassIfMissing to false.
```
relationalCondition.PassIfMissing = false;
```

CompositeCondition

You can combine up to 32 conditions.

public class CompositeCondition : IColumnCondition

Parameters

Parameter	Type	Description
LogicOperator (Required)	LogicOperator	The logical operator. Supported operators are NOT, AND, and OR.
subConditions (Required)	List<IColumnCondition>	A list of filters to combine using the logical operator. The list can include both RelationalCondition and CompositeCondition objects.

Example

This example shows how to perform a range query for row data where the primary key is in the range of [row1, row3). It then uses a CompositeCondition to filter the results.

try
{
    // Set the start primary key for the query.
    PrimaryKey inclusiveStartPrimaryKey = new PrimaryKey()
    {
        { "id", new ColumnValue("row1") }
    };
    // Set the end primary key for the query. The end key is exclusive.
    PrimaryKey exclusiveEndPrimaryKey = new PrimaryKey()
    {
        { "id", new ColumnValue("row3") }
    };

    // Create RelationalCondition 1: col1 == "val1".
    RelationalCondition relationalCondition1 = new RelationalCondition("col1", CompareOperator.EQUAL, new ColumnValue("val1"));
    // Create RelationalCondition 2: col2 == "val2".
    RelationalCondition relationalCondition2 = new RelationalCondition("col2", CompareOperator.EQUAL, new ColumnValue("val2"));
    // Create CompositeCondition 1: col1 == "val1" OR col2 == "val2".
    CompositeCondition compositeCondition1 = new CompositeCondition(LogicOperator.OR);
    compositeCondition1.AddCondition(relationalCondition1);
    compositeCondition1.AddCondition(relationalCondition2);
    // Create RelationalCondition 3: col3 == "val3".
    RelationalCondition relationalCondition3 = new RelationalCondition("col3", CompareOperator.EQUAL, new ColumnValue("val3"));
    // Create CompositeCondition 2, which combines the previous conditions: (col1 == "val1" OR col2 == "val2") AND col3 == "val3".
    CompositeCondition compositeCondition2 = new CompositeCondition(LogicOperator.AND);
    compositeCondition2.AddCondition(compositeCondition1);
    compositeCondition2.AddCondition(relationalCondition3);

    // Call the GetRange method to read row data.
    GetRangeRequest getRangeRequest = new GetRangeRequest("test_table", GetRangeDirection.Forward, inclusiveStartPrimaryKey, exclusiveEndPrimaryKey, null, null, compositeCondition2);
    GetRangeResponse getRangeResponse = client.GetRange(getRangeRequest);

    // Process the response.
    Console.WriteLine($"RequestId: {getRangeResponse.RequestID}");
    Console.WriteLine($"Read CU Cost: {getRangeResponse.ConsumedCapacityUnit.Read}");
    Console.WriteLine($"Write CU Cost: {getRangeResponse.ConsumedCapacityUnit.Write}");
    Console.WriteLine("Row Data: ");
    foreach (Row row in getRangeResponse.RowDataList)
    {
        Console.WriteLine(row);
    }
}
catch (Exception ex)
{
    Console.WriteLine($"Get Range failed, exception: {ex.Message}");
}

References

Read a single row
Read a range of rows
Read data in batches
Iterate through rows