---
type: Development Guide
title: Getting Started with LINQ in the ScalarDB Cluster .NET Client SDK
description: The ScalarDB Cluster .NET Client SDK supports querying the cluster with LINQ and some Entity Framework-like functionality.
resource: https://scalardb.scalar-labs.com/docs/3.18/scalardb-cluster-dotnet-client-sdk/getting-started-with-linq/
tags:
- scalardb
- v3.18
- phase:implement
- section:develop
- edition:enterprise-premium
status: stable
product: scalardb
product_title: ScalarDB
version: '3.18'
patch_version: 3.18.1
doc_id: scalardb-cluster-dotnet-client-sdk/getting-started-with-linq
lifecycle_phase: implement
breadcrumb:
- Develop
- Run Transactions
- Reference
- .NET Interface Guides
editions:
- Enterprise Premium
generated:
  by: process:okf-build/1.0.0
  at: '2026-08-04T23:50:49Z'
sources:
- id: docs-scalardb
  resource: https://github.com/scalar-labs/docs-scalardb/blob/6126dfe2f56389351d88b134752618641f9771dd/versioned_docs/version-3.18/scalardb-cluster-dotnet-client-sdk/getting-started-with-linq.mdx
  title: ScalarDB documentation source (MDX)
  author: process:scalar-labs/docs-scalardb
  last_modified: '2026-08-04T15:05:02Z'
---

# Getting Started with LINQ in the ScalarDB Cluster .NET Client SDK

The ScalarDB Cluster .NET Client SDK supports querying the cluster with LINQ and some Entity Framework-like functionality.

:::note

This SDK doesn't support [Entity Framework](https://learn.microsoft.com/en-us/ef/). Instead, this SDK implements functionality that is similar to Entity Framework.

:::

:::note

SQL support must be enabled on the cluster to use LINQ.

:::

## Install the SDK

Install the same major and minor version of the [SDK](https://www.nuget.org/packages/ScalarDB.Client) as ScalarDB Cluster into the .NET project. You can do this by using the built-in NuGet package manager, replacing `<MAJOR>.<MINOR>` with the version that you're using:

```console
dotnet add package ScalarDB.Client --version '<MAJOR>.<MINOR>.*'
```

## Add client settings

Add the `ScalarDbOptions` section to the `appsettings.json` file of your ASP.NET Core app, replacing `<HOSTNAME_OR_IP_ADDRESS>` with the FQDN or the IP address, and `<PORT>` with the port number (`60053` by default) of your cluster:

```json
{
  "ScalarDbOptions": {
    "Address": "http://<HOSTNAME_OR_IP_ADDRESS>:<PORT>",
    "HopLimit": 10
  }
}
```

For details about settings files and other ways to configure the client, see [Client configuration](./common-reference.md#client-configuration).

## Set up classes

After confirming that SQL support is enabled, create a C# class for each ScalarDB table that you want to use. For example:

```c#
using System.ComponentModel.DataAnnotations.Schema;
using ScalarDB.Client.DataAnnotations;

// ...

[Table("ns.statements")]
public class Statement
{
    [PartitionKey]
    [Column("statement_id", Order = 0)]
    public int Id { get; set; }

    [SecondaryIndex]
    [Column("order_id", Order = 1)]
    public string OrderId { get; set; } = String.Empty;

    [SecondaryIndex]
    [Column("item_id", Order = 2)]
    public int ItemId { get; set; }

    [Column("quantity", Order = 3)]
    public int Quantity { get; set; }
}

[Table("order_service.items")]
public class Item
{
    [PartitionKey]
    [Column("item_id", Order = 0)]
    public int Id { get; set; }

    [Column("name", Order = 1)]
    public string Name { get; set; } = String.Empty;

    [Column("price", Order = 2)]
    public int Price { get; set; }
}
```

If a partition key, clustering key, or secondary index consists of more than one column, the `Order` property of `ColumnAttribute` will decide the order inside the key or index.

For details about which types should be used for properties, see [How ScalarDB Column Types Are Converted to and from .NET Types](./common-reference.md#how-scalardb-column-types-are-converted-to-and-from-net-types).

Create a context class that has properties for all the tables you want to use. For example:

```c#
    public class MyDbContext: ScalarDbContext
    {
        public ScalarDbSet<Statement> Statements { get; set; }
        public ScalarDbSet<Item> Items { get; set; }
    }
```

After all the classes are created, you need to register the created context in the dependency injection container. For example:

```c#
using ScalarDB.Client.Extensions;

//...

var builder = WebApplication.CreateBuilder(args);

//...

builder.Services.AddScalarDbContext<MyDbContext>();
```

:::note

The context class will be registered as a transient service. For details about service lifetimes, see [.NET dependency injection - Service lifetimes](https://learn.microsoft.com/en-us/dotnet/core/extensions/dependency-injection#service-lifetimes).

:::

The context can be injected into the controller's constructor as follows:

```c#
[ApiController]
public class OrderController: ControllerBase
{
    private readonly MyDbContext _myDbContext;

    public OrderController(MyDbContext myDbContext)
    {
        _myDbContext = myDbContext;
    }
}
```

## Use LINQ to query properties

After receiving `MyDbContext` in your controller, you can query its properties by using LINQ. For example:

### Use query syntax

```c#
from stat in _myDbContext.Statements
join item in _myDbContext.Items on stat.ItemId equals item.Id
where stat.Quantity > 2 && item.Name.Contains("apple")
orderby stat.Quantity descending, stat.ItemId
select new { item.Name, stat.Quantity };
```

### Use method syntax

```c#
_myDbContext.Statements
            .Where(stat => stat.OrderId == "1")
            .Skip(1)
            .Take(2);
```

### Use the `First` method to get one `Statement` by its partition key

```c#
_myDbContext.Statements.First(stat => stat.OrderId == "1");
```

### Use the `DefaultIfEmpty` method to perform left outer join

```c#
from stat in _myDbContext.Statements
join item in _myDbContext.Items on stat.ItemId equals item.Id into items
from i in items.DefaultIfEmpty()
select new { ItemName = i != null ? i.Name : "" }
```

### Use the `GroupBy` method to group and aggregate data

```c#
// Group by single key with count
_myDbContext.Statements
            .GroupBy(stat => stat.ItemId)
            .Select(g => new { ItemId = g.Key, Count = g.Count() });
```

```c#
// Group by composite key
_myDbContext.Statements
            .GroupBy(stat => new { stat.ItemId, stat.OrderId })
            .Select(g => new
            {
                ItemId = g.Key.ItemId,
                OrderId = g.Key.OrderId,
                Count = g.Count()
            });
```

```c#
// With WHERE before GROUP BY
_myDbContext.Statements
            .Where(stat => stat.Quantity > 0)
            .GroupBy(stat => stat.ItemId)
            .Select(g => new { ItemId = g.Key, Total = g.Sum(x => x.Quantity) });
```

```c#
// With HAVING clause (Where after GroupBy filters groups)
_myDbContext.Statements
            .GroupBy(stat => stat.ItemId)
            .Where(g => g.Count() > 5)
            .Select(g => new { ItemId = g.Key, Count = g.Count() });
```

```c#
// With multiple aggregate functions
_myDbContext.Statements
            .GroupBy(stat => stat.ItemId)
            .Select(g => new
            {
                ItemId = g.Key,
                Count = g.Count(),
                TotalQuantity = g.Sum(x => x.Quantity),
                AverageQuantity = g.Average(x => x.Quantity),
                MinQuantity = g.Min(x => x.Quantity),
                MaxQuantity = g.Max(x => x.Quantity)
            });
```

```c#
// Aggregate all data by using a constant key to retrieve multiple aggregates in a single query
_myDbContext.Statements
            .GroupBy(_ => 1)
            .Select(g => new
            {
                Count = g.Count(),
                TotalQuantity = g.Sum(x => x.Quantity),
                AverageQuantity = g.Average(x => x.Quantity),
                MinQuantity = g.Min(x => x.Quantity),
                MaxQuantity = g.Max(x => x.Quantity)
            });
```

### Use standalone aggregate methods without `GroupBy`

```c#
// Count all records
_myDbContext.Statements.Count();
```

```c#
// Count with a condition
_myDbContext.Statements.Count(stat => stat.Quantity > 0);
```

```c#
// Other standalone aggregate functions
_myDbContext.Statements.Sum(stat => stat.Quantity);
_myDbContext.Statements.Average(stat => stat.Quantity);
_myDbContext.Statements.Min(stat => stat.Quantity);
_myDbContext.Statements.Max(stat => stat.Quantity);
```

The following methods are translated into SQL and executed remotely on the cluster. For chainable methods, any unsupported methods that follow will fall back to being executed locally in memory by using LINQ to Objects. Terminal methods like `First`/`FirstOrDefault` and standalone aggregate methods return a result directly.

- `Select`
- `Where`
- `Join`
- `GroupJoin`
- `GroupBy`
- `Skip`
- `Take`
- `OrderBy`/`OrderByDescending`
- `ThenBy`/`ThenByDescending`
- `First`/`FirstOrDefault`
- `Count`/`LongCount`
- `Sum`
- `Average`
- `Min`
- `Max`

The following aggregate functions are supported inside `Select` after `GroupBy`:

- `Count`/`LongCount`
- `Sum`
- `Average`
- `Min`
- `Max`

The following `String` methods are supported inside the predicates of `Where` and `First`/`FirstOrDefault` methods:

- `Contains`
- `StartsWith`
- `EndsWith`

Unsupported LINQ methods can be used after the supported methods. For example:

```c#
_myDbContext.Statements
            .Where(stat => stat.OrderId == "1") // Will be executed remotely on the cluster.
            .Distinct() // Will be executed locally in the app.
            .Where(stat => stat.ItemId < 5); // Will be executed locally.
```

:::note

If `Skip` is specified before `Take` or `First`/`FirstOrDefault`, the number that is passed to `Skip` will be added to the `LIMIT` number in the SQL query. By itself, `Skip` won't change the resulting SQL query.

:::

## Limitations when using LINQ against `ScalarDbSet{T}` objects

- All method calls are supported inside `Select`. For example:

```c#
.Select(stat => convertToSomething(stat.ItemId))
//...
.Select(stat => stat.ItemId * getSomeNumber())
```

- Method calls, except for calls against the querying object, are also supported inside `Where` and `First`/`FirstOrDefault`. For example:

```c#
.Where(stat => stat.ItemId == getItemId()) // is OK
//...
.Where(stat => stat.ItemId.ToString() == "1") // is not supported
```

- All method calls are supported inside the result-selecting lambda of `Join` and `GroupJoin`. For example:

```c#
.Join(_myDbContext.Items,
      stat => stat.ItemId,
      item => item.Id,
      (stat, item) => new { ItemName = convertToSomething(item.Name),
                            ItemQuantity = stat.Quantity.ToString() })
```

- Method calls are not supported inside the key-selecting lambdas of `Join` and `GroupJoin`.
- Custom equality comparers are not supported. The `comparer` argument in `Join` and `GroupJoin` methods will be ignored if the argument has been passed.
- More than one `from` directly in one query is not supported, except when the `DefaultIfEmpty` method is used to perform left outer join. Each subsequent `from` is considered to be a separate query.

```c#
var firstQuery = from stat in _myDbContext.Statements
                 where stat.Quantity > 2
                 select new { stat.Quantity };

var secondQuery = from item in _myDbContext.Items
                  where item.Price > 6
                  select new { item.Name };

var finalQuery = from first in firstQuery
                 from second in secondQuery
                 select new { first.Quantity, second.Name };

// 1. firstQuery will be executed against the cluster.
// 2. secondQuery will be executed against the cluster for each object (row) from 1.
// 3. finalQuery will be executed locally with the results from 1 and 2.
var result = finalQuery.ToArray();
```

- Method calls are not supported inside `OrderBy`/`OrderByDescending` or `ThenBy`/`ThenByDescending`.
- Only overloads of `Contains`, `StartsWith`, and `EndsWith` methods that have a single string argument are supported inside `Where` and `First`/`FirstOrDefault`.
- The following methods are not translated to SQL after `GroupBy` and will fall back to being executed locally in memory by using LINQ to Objects: `OrderBy`/`OrderByDescending`, `ThenBy`/`ThenByDescending`, `Skip`, `Take`, `First`/`FirstOrDefault`, `Join`, and `GroupJoin`.
- Nested `GroupBy` (calling `GroupBy` after another `GroupBy`) is not translated to SQL. The second `GroupBy` will fall back to being executed locally in memory by using LINQ to Objects.
- `Count` with a predicate (for example, `g.Count(x => x.Quantity > 5)`) is not supported inside `Select` or `Where` (HAVING) after `GroupBy`. To filter rows before grouping, use `Where` before `GroupBy` instead.

## Modify data in a cluster by using `ScalarDbContext`

The properties of the class inherited from `ScalarDbContext` can be used to modify data.

### Add a new object by using the `AddAsync` method

```c#
var statement = new Statement
                {
                    OrderId = "2",
                    ItemId = 4,
                    Quantity = 8
                };
await _myDbContext.Statements.AddAsync(statement);
```

### Update an object by using the `UpdateAsync` method

```c#
var statement = _myDbContext.Statements.First(stat => stat.Id == 1);

// ...

statement.Quantity = 10;
await _myDbContext.Statements.UpdateAsync(statement);
```

### Remove an object by using the `RemoveAsync` method

```c#
var statement = _myDbContext.Statements.First(stat => stat.Id == 1);

// ...

await _myDbContext.Statements.RemoveAsync(statement);
```

## Manage transactions

LINQ queries and `AddAsync`, `UpdateAsync`, and `RemoveAsync` methods can be executed without an explicitly started transaction. However, to execute multiple queries and methods as part of a single transaction, the transaction must be explicitly started and committed. `ScalarDbContext` supports both ordinary transactions and transactions with the two-phase commit interface in ScalarDB.

### Begin a new transaction

```c#
await _myDbContext.BeginTransactionAsync();
```

### Begin a new read-only transaction

If you perform only read operations (that is, LINQ queries without `AddAsync`, `UpdateAsync`, or `RemoveAsync`) in a transaction, you can begin a read-only transaction as follows:

```c#
await _myDbContext.BeginReadOnlyTransactionAsync();
```

:::note

Read-only transactions are processed more efficiently than read transactions begun without the read-only mode by assuming no write operations are issued within them. Therefore, attempting to perform write operations (`AddAsync`, `UpdateAsync`, or `RemoveAsync`) in a read-only transaction will result in an error. Read-only mode is not supported for transactions with the two-phase commit interface.

:::

### Begin a new transaction with the two-phase commit interface

```c#
await _myDbContext.BeginTwoPhaseCommitTransactionAsync();
```

### Get the ID of a currently active transaction

```c#
var transactionId = _myDbContext.CurrentTransactionId;
```

### Join an existing transaction with the two-phase commit interface

```c#
await _myDbContext.JoinTwoPhaseCommitTransactionAsync(transactionId);
```

### Resume an existing transaction

```c#
await _myDbContext.ResumeTransaction(transactionId);
```

### Resume an existing transaction with the two-phase commit interface

```c#
await _myDbContext.ResumeTwoPhaseCommitTransaction(transactionId);
```

:::note

The `ResumeTransaction`/`ResumeTwoPhaseCommitTransaction` methods don't have asynchronous versions because they only initialize the transaction data in the `ScalarDbContext` inheriting object without querying the cluster. Because of this, resuming a transaction by using the wrong ID is possible.

:::

### Commit a transaction (ordinary or two-phase commit)

```c#
await _myDbContext.CommitTransactionAsync();
```

### Roll back a transaction (ordinary or two-phase commit)

```c#
await _myDbContext.RollbackTransactionAsync();
```

### Prepare a transaction with the two-phase commit interface for the commit

```c#
await _myDbContext.PrepareTransactionAsync();
```

### Validate a transaction with the two-phase commit interface before the commit

```c#
await _myDbContext.ValidateTransactionAsync();
```
