Projections for tables (C#)

You can use a projection with some Data API commands to control what columns to return.

When you specify a projection, you specify which columns to include or exclude. You cannot specify a mix of inclusions and exclusions.

If you don’t specify a projection, the Data API returns all columns. In order to optimize the response size and improve read performance, DataStax recommends always providing a projection tailored to the needs of your application.

Null values

If you make a direct HTTP request to the Data API, the response always excludes null values, regardless of the projection. This means that the response may include different columns for each returned row, depending on which columns are null in the row. You cannot forcibly include null values in the response.

If you use one of the clients, the client adds columns that were omitted due to a null value.

Include specific columns

The following examples include the is_checked_out and title columns.

For FindOne, use the Projection option of the TableFindOneOptions class:

Typed
Untyped

using System.Text.Json;
using DataStax.AstraDB.DataApi;
using DataStax.AstraDB.DataApi.Core;
using DataStax.AstraDB.DataApi.Core.Query;
using DataStax.AstraDB.DataApi.Tables;

namespace Examples;

public class Book
{
  [ColumnPrimaryKey(1)]
  [ColumnName("title")]
  public string Title { get; set; } = null!;

  [ColumnPrimaryKey(2)]
  [ColumnName("author")]
  public string Author { get; set; } = null!;

  [ColumnName("is_checked_out")]
  public bool? IsCheckedOut { get; set; }

  [ColumnName("number_of_pages")]
  public int? NumberOfPages { get; set; }
}

public class Program
{
  static async Task Main()
  {
    // Get an existing table
    var client = new DataAPIClient(
      new CommandOptions() { Destination = DataAPIDestination.HCD }
    );

    var database = client.GetDatabase(
      "API_ENDPOINT",
      DataAPIClient.UsernamePasswordTokenProvider(
        "USERNAME",
        "PASSWORD"
      ),
      "KEYSPACE_NAME"
    );

    var table = database.GetTable<Book>("TABLE_NAME");

    // Use a projection
    var filterBuilder = Builders<Book>.TableFilter;
    var filter = filterBuilder.Lt(b => b.NumberOfPages, 300);
    var findOptions = new TableFindOneOptions<Book>()
    {
      Projection = Builders<Book>
        .Projection.Include(b => b.IsCheckedOut)
        .Include(b => b.Title),
    };

    var result = await table.FindOneAsync(filter, findOptions);

    Console.WriteLine(JsonSerializer.Serialize(result));
  }
}

using System.Text.Json;
using DataStax.AstraDB.DataApi;
using DataStax.AstraDB.DataApi.Core;
using DataStax.AstraDB.DataApi.Core.Query;
using DataStax.AstraDB.DataApi.Tables;

namespace Examples;

public class Program
{
  static async Task Main()
  {
    // Get an existing table
    var client = new DataAPIClient(
      new CommandOptions() { Destination = DataAPIDestination.HCD }
    );

    var database = client.GetDatabase(
      "API_ENDPOINT",
      DataAPIClient.UsernamePasswordTokenProvider(
        "USERNAME",
        "PASSWORD"
      ),
      "KEYSPACE_NAME"
    );

    var table = database.GetTable("TABLE_NAME");

    // Use a projection
    var filterBuilder = Builders<Row>.TableFilter;
    var filter = filterBuilder.Lt("number_of_pages", 300);
    var findOptions = new TableFindOneOptions<Row>()
    {
      Projection = Builders<Row>
        .Projection.Include("is_checked_out")
        .Include("title"),
    };

    var result = await table.FindOneAsync(filter, findOptions);

    Console.WriteLine(JsonSerializer.Serialize(result));
  }
}

For Find, use the Projection option of the TableFindOptions class:

Typed
Untyped

using System.Text.Json;
using DataStax.AstraDB.DataApi;
using DataStax.AstraDB.DataApi.Core;
using DataStax.AstraDB.DataApi.Core.Query;
using DataStax.AstraDB.DataApi.Tables;

namespace Examples;

public class Book
{
  [ColumnPrimaryKey(1)]
  [ColumnName("title")]
  public string Title { get; set; } = null!;

  [ColumnPrimaryKey(2)]
  [ColumnName("author")]
  public string Author { get; set; } = null!;

  [ColumnName("is_checked_out")]
  public bool? IsCheckedOut { get; set; }

  [ColumnName("number_of_pages")]
  public int? NumberOfPages { get; set; }
}

public class Program
{
  static async Task Main()
  {
    // Get an existing table
    var client = new DataAPIClient(
      new CommandOptions() { Destination = DataAPIDestination.HCD }
    );

    var database = client.GetDatabase(
      "API_ENDPOINT",
      DataAPIClient.UsernamePasswordTokenProvider(
        "USERNAME",
        "PASSWORD"
      ),
      "KEYSPACE_NAME"
    );

    var table = database.GetTable<Book>("TABLE_NAME");

    // Use a projection
    var filterBuilder = Builders<Book>.TableFilter;
    var filter = filterBuilder.Lt(b => b.NumberOfPages, 300);
    var projection = Builders<Book>
      .Projection.Include(b => b.IsCheckedOut)
      .Include(b => b.Title);

    var results = table.Find(
      filter,
      new TableFindOptions<Book>() { Projection = projection }
    );

    await foreach (var row in results)
    {
      Console.WriteLine(JsonSerializer.Serialize(row));
    }
  }
}

using System.Text.Json;
using DataStax.AstraDB.DataApi;
using DataStax.AstraDB.DataApi.Core;
using DataStax.AstraDB.DataApi.Core.Query;
using DataStax.AstraDB.DataApi.Tables;

namespace Examples;

public class Program
{
  static async Task Main()
  {
    // Get an existing table
    var client = new DataAPIClient(
      new CommandOptions() { Destination = DataAPIDestination.HCD }
    );

    var database = client.GetDatabase(
      "API_ENDPOINT",
      DataAPIClient.UsernamePasswordTokenProvider(
        "USERNAME",
        "PASSWORD"
      ),
      "KEYSPACE_NAME"
    );

    var table = database.GetTable("TABLE_NAME");

    // Use a projection
    var filterBuilder = Builders<Row>.TableFilter;
    var filter = filterBuilder.Lt("number_of_pages", 300);
    var projection = Builders<Row>
      .Projection.Include("is_checked_out")
      .Include("title");

    var results = table.Find(
      filter,
      new TableFindOptions<Row>() { Projection = projection }
    );

    await foreach (var row in results)
    {
      Console.WriteLine(JsonSerializer.Serialize(row));
    }
  }
}

Exclude specific columns

The following examples exclude the is_checked_out and title columns.

For FindOne, use the Projection option of the TableFindOneOptions class:

Typed
Untyped

using System.Text.Json;
using DataStax.AstraDB.DataApi;
using DataStax.AstraDB.DataApi.Core;
using DataStax.AstraDB.DataApi.Core.Query;
using DataStax.AstraDB.DataApi.Tables;

namespace Examples;

public class Book
{
  [ColumnPrimaryKey(1)]
  [ColumnName("title")]
  public string Title { get; set; } = null!;

  [ColumnPrimaryKey(2)]
  [ColumnName("author")]
  public string Author { get; set; } = null!;

  [ColumnName("is_checked_out")]
  public bool? IsCheckedOut { get; set; }

  [ColumnName("number_of_pages")]
  public int? NumberOfPages { get; set; }
}

public class Program
{
  static async Task Main()
  {
    // Get an existing table
    var client = new DataAPIClient(
      new CommandOptions() { Destination = DataAPIDestination.HCD }
    );

    var database = client.GetDatabase(
      "API_ENDPOINT",
      DataAPIClient.UsernamePasswordTokenProvider(
        "USERNAME",
        "PASSWORD"
      ),
      "KEYSPACE_NAME"
    );

    var table = database.GetTable<Book>("TABLE_NAME");

    // Use a projection
    var filterBuilder = Builders<Book>.TableFilter;
    var filter = filterBuilder.Lt(b => b.NumberOfPages, 300);
    var findOptions = new TableFindOneOptions<Book>()
    {
      Projection = Builders<Book>
        .Projection.Exclude(b => b.IsCheckedOut)
        .Exclude(b => b.Title),
    };

    var result = await table.FindOneAsync(filter, findOptions);

    Console.WriteLine(JsonSerializer.Serialize(result));
  }
}

using System.Text.Json;
using DataStax.AstraDB.DataApi;
using DataStax.AstraDB.DataApi.Core;
using DataStax.AstraDB.DataApi.Core.Query;
using DataStax.AstraDB.DataApi.Tables;

namespace Examples;

public class Program
{
  static async Task Main()
  {
    // Get an existing table
    var client = new DataAPIClient(
      new CommandOptions() { Destination = DataAPIDestination.HCD }
    );

    var database = client.GetDatabase(
      "API_ENDPOINT",
      DataAPIClient.UsernamePasswordTokenProvider(
        "USERNAME",
        "PASSWORD"
      ),
      "KEYSPACE_NAME"
    );

    var table = database.GetTable("TABLE_NAME");

    // Use a projection
    var filterBuilder = Builders<Row>.TableFilter;
    var filter = filterBuilder.Lt("number_of_pages", 300);
    var findOptions = new TableFindOneOptions<Row>()
    {
      Projection = Builders<Row>
        .Projection.Exclude("is_checked_out")
        .Exclude("title"),
    };

    var result = await table.FindOneAsync(filter, findOptions);

    Console.WriteLine(JsonSerializer.Serialize(result));
  }
}

For Find, use the Projection option of the TableFindOptions class:

Typed
Untyped

using System.Text.Json;
using DataStax.AstraDB.DataApi;
using DataStax.AstraDB.DataApi.Core;
using DataStax.AstraDB.DataApi.Core.Query;
using DataStax.AstraDB.DataApi.Tables;

namespace Examples;

public class Book
{
  [ColumnPrimaryKey(1)]
  [ColumnName("title")]
  public string Title { get; set; } = null!;

  [ColumnPrimaryKey(2)]
  [ColumnName("author")]
  public string Author { get; set; } = null!;

  [ColumnName("is_checked_out")]
  public bool? IsCheckedOut { get; set; }

  [ColumnName("number_of_pages")]
  public int? NumberOfPages { get; set; }
}

public class Program
{
  static async Task Main()
  {
    // Get an existing table
    var client = new DataAPIClient(
      new CommandOptions() { Destination = DataAPIDestination.HCD }
    );

    var database = client.GetDatabase(
      "API_ENDPOINT",
      DataAPIClient.UsernamePasswordTokenProvider(
        "USERNAME",
        "PASSWORD"
      ),
      "KEYSPACE_NAME"
    );

    var table = database.GetTable<Book>("TABLE_NAME");

    // Use a projection
    var filterBuilder = Builders<Book>.TableFilter;
    var filter = filterBuilder.Lt(b => b.NumberOfPages, 300);
    var projection = Builders<Book>
      .Projection.Exclude(b => b.IsCheckedOut)
      .Exclude(b => b.Title);

    var results = table.Find(
      filter,
      new TableFindOptions<Book>() { Projection = projection }
    );

    await foreach (var row in results)
    {
      Console.WriteLine(JsonSerializer.Serialize(row));
    }
  }
}

using System.Text.Json;
using DataStax.AstraDB.DataApi;
using DataStax.AstraDB.DataApi.Core;
using DataStax.AstraDB.DataApi.Core.Query;
using DataStax.AstraDB.DataApi.Tables;

namespace Examples;

public class Program
{
  static async Task Main()
  {
    // Get an existing table
    var client = new DataAPIClient(
      new CommandOptions() { Destination = DataAPIDestination.HCD }
    );

    var database = client.GetDatabase(
      "API_ENDPOINT",
      DataAPIClient.UsernamePasswordTokenProvider(
        "USERNAME",
        "PASSWORD"
      ),
      "KEYSPACE_NAME"
    );

    var table = database.GetTable("TABLE_NAME");

    // Use a projection
    var filterBuilder = Builders<Row>.TableFilter;
    var filter = filterBuilder.Lt("number_of_pages", 300);
    var projection = Builders<Row>
      .Projection.Exclude("is_checked_out")
      .Exclude("title");

    var results = table.Find(
      filter,
      new TableFindOptions<Row>() { Projection = projection }
    );

    await foreach (var row in results)
    {
      Console.WriteLine(JsonSerializer.Serialize(row));
    }
  }
}

Include all columns

The wildcard projection "*" represents the whole row. If you use this projection, it must be the only key in the projection.

If set to true, all columns are returned. This is equivalent to not specifying a projection.

You cannot set the wildcard projection to false.

using System.Text.Json;
using DataStax.AstraDB.DataApi;
using DataStax.AstraDB.DataApi.Core;
using DataStax.AstraDB.DataApi.Core.Query;
using DataStax.AstraDB.DataApi.Tables;

namespace Examples;

public class Program
{
  static async Task Main()
  {
    // Get an existing table
    var client = new DataAPIClient(
      new CommandOptions() { Destination = DataAPIDestination.HCD }
    );

    var database = client.GetDatabase(
      "API_ENDPOINT",
      DataAPIClient.UsernamePasswordTokenProvider(
        "USERNAME",
        "PASSWORD"
      ),
      "KEYSPACE_NAME"
    );

    var table = database.GetTable("TABLE_NAME");

    // Use a projection
    var filter = Builders<Row>.TableFilter.Lt("number_of_pages", 300);
    var findOptions = new TableFindOneOptions<Row>()
    {
      Projection = Builders<Row>.Projection.Include("*"),
    };

    var result = await table.FindOneAsync(filter, findOptions);

    Console.WriteLine(JsonSerializer.Serialize(result));
  }
}

Unsupported cases

A projection cannot include or exclude sub-column values, such as keys in map columns.

The Data API doesn’t support false wildcard projections for tables.