ClickHouse C# client

dotnet add package ClickHouse.Driver

Install-Package ClickHouse.Driver

using ClickHouse.Driver;

// クライアントを作成する（通常はシングルトンとして）
using var client = new ClickHouseClient("Host=my.clickhouse;Protocol=https;Port=8443;Username=user");

// クエリを実行する
var version = await client.ExecuteScalarAsync("SELECT version()");
Console.WriteLine(version);

Host=localhost;Port=8123;Username=default;Password=secret;Database=mydb

Host=localhost;set_max_threads=4;set_readonly=1;set_max_memory_usage=10000000000

var options = new QueryOptions
{
    QueryId = "report-2024-001",
    Database = "analytics",
    CustomSettings = new Dictionary<string, object>
    {
        { "max_threads", 4 },
        { "max_memory_usage", 10_000_000_000 }
    },
    MaxExecutionTime = TimeSpan.FromMinutes(5)
};

var reader = await client.ExecuteReaderAsync(
    "SELECT * FROM large_table",
    parameters: null,
    options: options
);

var insertOptions = new InsertOptions
{
    BatchSize = 50_000,
    MaxDegreeOfParallelism = 4,
    QueryId = "bulk-import-001"
};

long rowsInserted = await client.InsertBinaryAsync(
    "my_table",
    columns,
    rows,
    insertOptions
);

var options = new InsertOptions
{
    ColumnTypes = new Dictionary<string, string>
    {
        ["id"] = "UInt64",
        ["name"] = "Nullable(String)",
        ["score"] = "Float32",
    },
};

await client.InsertBinaryAsync("my_table", ["id", "name", "score"], rows, options);

var options = new InsertOptions { UseSchemaCache = true };

// 最初の呼び出しでサーバーからスキーマを取得する
await client.InsertBinaryAsync("my_table", columns, batch1, options);

// 2回目の呼び出しではキャッシュ済みスキーマを再利用する — 追加のラウンドトリップなし
await client.InsertBinaryAsync("my_table", columns, batch2, options);

using ClickHouse.Driver;

using var client = new ClickHouseClient("Host=localhost;Username=default;Password=secret");

using ClickHouse.Driver;

var settings = new ClickHouseClientSettings
{
    Host = "localhost",
    Username = "default",
    Password = "secret"
};
using var client = new ClickHouseClient(settings);

// In your DI configuration
services.AddHttpClient("ClickHouse", client =>
{
    client.Timeout = TimeSpan.FromMinutes(5);
}).ConfigurePrimaryHttpMessageHandler(() => new HttpClientHandler
{
    AutomaticDecompression = DecompressionMethods.GZip | DecompressionMethods.Deflate
});

// Create client with factory
var factory = serviceProvider.GetRequiredService<IHttpClientFactory>();
var client = new ClickHouseClient("Host=localhost", factory, "ClickHouse");

// テーブルを作成する
await client.ExecuteNonQueryAsync(
    "CREATE TABLE IF NOT EXISTS default.my_table (id Int64, name String) ENGINE = Memory"
);

// テーブルを削除する
await client.ExecuteNonQueryAsync("DROP TABLE IF EXISTS default.my_table");

var count = await client.ExecuteScalarAsync("SELECT count() FROM default.my_table");
Console.WriteLine($"行数: {count}");

var version = await client.ExecuteScalarAsync("SELECT version()");
Console.WriteLine($"サーバーバージョン: {version}");

using ClickHouse.Driver;
using ClickHouse.Driver.ADO.Parameters;

var parameters = new ClickHouseParameterCollection();
parameters.AddParameter("id", 1L);
parameters.AddParameter("name", "Alice");

await client.ExecuteNonQueryAsync(
    "INSERT INTO default.my_table (id, name) VALUES ({id:Int64}, {name:String})",
    parameters
);

// IEnumerable<object[]> としてデータを準備する
var rows = Enumerable.Range(0, 1_000_000)
    .Select(i => new object[] { (long)i, $"value{i}" });

var columns = new[] { "id", "name" };

// 基本的な挿入
long rowsInserted = await client.InsertBinaryAsync("default.my_table", columns, rows);
Console.WriteLine($"Rows inserted: {rowsInserted}");

var options = new InsertOptions
{
    BatchSize = 100_000,           // バッチあたりの行数（デフォルト: 100,000）
    MaxDegreeOfParallelism = 4     // 並列バッチアップロード数（デフォルト: 1）
};

// テーブルのカラムに対応するPOCOを定義する
public class SensorReading
{
    public ulong Id { get; set; }
    public string SensorName { get; set; }
    public double Value { get; set; }
    public DateTime Timestamp { get; set; }
}

// 型を登録する（クライアントのライフタイムにつき1回）
client.RegisterBinaryInsertType<SensorReading>();

// 直接挿入する — カラム名はプロパティ名から導出される
var readings = Enumerable.Range(0, 100_000)
    .Select(i => new SensorReading
    {
        Id = (ulong)i,
        SensorName = $"sensor_{i % 10}",
        Value = Random.Shared.NextDouble() * 100,
        Timestamp = DateTime.UtcNow,
    });

long rowsInserted = await client.InsertBinaryAsync("sensors", readings);

public class Event
{
    [ClickHouseColumn(Name = "event_id")]     // 異なる名前のカラムにマッピングする
    public ulong Id { get; set; }

    [ClickHouseColumn(Type = "LowCardinality(String)")]  // ClickHouseの型を明示的に指定
    public string Category { get; set; }

    public string Payload { get; set; }

    [ClickHouseNotMapped]                     // insertから除外
    public string InternalTag { get; set; }
}

using ClickHouse.Driver.ADO.Parameters;

var parameters = new ClickHouseParameterCollection();
parameters.AddParameter("max_id", 100L);

var reader = await client.ExecuteReaderAsync(
    "SELECT * FROM default.my_table WHERE id < {max_id:Int64}",
    parameters
);

while (reader.Read())
{
    Console.WriteLine($"Id: {reader.GetInt64(0)}, Name: {reader.GetString(1)}");
}

// Define a POCO matching your result columns
public class SensorReading
{
    public ulong Id { get; set; }
    public DateTime Timestamp { get; set; }

    [ClickHouseColumn(Name = "sensor_name")]
    public string SensorName { get; set; }
    public double Value { get; set; }

}

// Register the type (once per client lifetime)
client.RegisterPocoType<SensorReading>();

// Stream results as typed objects
await foreach (var reading in client.QueryAsync<SensorReading>(
    "SELECT Id, sensor_name, Value, Timestamp FROM sensors"))
{
    Console.WriteLine($"{reading.SensorName}: {reading.Value}");
}

var reader = await client.ExecuteReaderAsync("SELECT Id, SensorName, Value, Timestamp FROM sensors");

while (reader.Read())
{
    SensorReading reading = reader.MapTo<SensorReading>();
    Console.WriteLine($"{reading.SensorName}: {reading.Value}");
}

SELECT {value:Array(UInt16)} as a

SELECT * FROM table WHERE val = {tuple_in_tuple:Tuple(UInt8, Tuple(String, UInt8))}

INSERT INTO table VALUES ({val1:Int32}, {val2:Array(UInt8)})

var parameters = new ClickHouseParameterCollection();
parameters.AddParameter("name", "my_database");

await client.ExecuteNonQueryAsync("CREATE DATABASE {name:Identifier}", parameters);

var parameters = new ClickHouseParameterCollection();
parameters.AddParameter("col", "user_id");

var reader = await client.ExecuteReaderAsync("SELECT {col:Identifier} FROM t", parameters);

var options = new QueryOptions
{
    QueryId = $"report-{Guid.NewGuid()}"
};

var reader = await client.ExecuteReaderAsync(
    "SELECT * FROM large_table",
    parameters: null,
    options: options
);

using ClickHouse.Driver.ADO.Parameters;

var settings = new ClickHouseClientSettings("Host=localhost")
{
    ParameterTypeResolver = new DictionaryParameterTypeResolver(new Dictionary<Type, string>
    {
        [typeof(DateTime)] = "DateTime64(3)",
        [typeof(decimal)] = "Decimal64(4)",
    }),
};
using var client = new ClickHouseClient(settings);

var parameters = new ClickHouseParameterCollection();
parameters.AddParameter("dt", DateTime.UtcNow);     // Mapped to DateTime64(3)
parameters.AddParameter("amount", 99.1234m);         // Mapped to Decimal64(4)

await client.ExecuteReaderAsync("SELECT @dt, @amount", parameters);

public class SmartDecimalResolver : IParameterTypeResolver
{
    public string ResolveType(Type clrType, object value, string parameterName)
    {
        if (clrType != typeof(decimal))
            return null; // Fall through to default

        var scale = (decimal.GetBits((decimal)value)[3] >> 16) & 0x7F;
        return scale <= 4 ? $"Decimal64({scale})" : $"Decimal128({scale})";
    }
}

using ClickHouse.Driver.ADO.Parameters;

var settings = new ClickHouseClientSettings("Host=localhost")
{
    ParameterFormatter = new DictionaryParameterFormatter(new Dictionary<Type, Func<object, string>>
    {
        [typeof(DateTime)] = v => ((DateTime)v).ToString("yyyy-MM-ddTHH:mm:ss.ffffff",
            System.Globalization.CultureInfo.InvariantCulture),
        [typeof(decimal)] = v => ((decimal)v).ToString("F4",
            System.Globalization.CultureInfo.InvariantCulture),
    }),
};
using var client = new ClickHouseClient(settings);

public class FixedDecimalFormatter : IParameterFormatter
{
    public string Format(object value, string typeName, string parameterName)
    {
        if (value is decimal d)
            return d.ToString("F4", System.Globalization.CultureInfo.InvariantCulture);
        return null; // Fall through for anything else
    }
}

using ClickHouse.Driver.ADO.Readers;

var converter = new DictionaryReadValueConverter()
    .For<DateTime>(dt => DateTime.SpecifyKind(dt, DateTimeKind.Utc))
    .For<string>(s => s.Trim());

var settings = new ClickHouseClientSettings("Host=localhost")
{
    ReadValueConverter = converter,
};
using var client = new ClickHouseClient(settings);

public class UtcKindForNoTzDateTimeConverter : IReadValueConverter
{
    public object ConvertValue(object value, string columnName, string clickhouseType)
    {
        if (value is DateTime dt && clickhouseType == "DateTime")
            return DateTime.SpecifyKind(dt, DateTimeKind.Utc);
        return value;
    }

    public T ConvertValue<T>(T value, string columnName, string clickhouseType)
    {
        if (typeof(T) == typeof(DateTime) && value is DateTime dt && clickhouseType == "DateTime")
            return (T)(object)DateTime.SpecifyKind(dt, DateTimeKind.Utc);
        return value;
    }
}

using var result = await client.ExecuteRawResultAsync(
    "SELECT * FROM default.my_table LIMIT 100 FORMAT JSONEachRow"
);

await using var stream = await result.ReadAsStreamAsync();
using var reader = new StreamReader(stream);
var json = await reader.ReadToEndAsync();

using var result = await client.ExecuteRawResultAsync(
    "SELECT * FROM events FORMAT Parquet",
    options: new QueryOptions { AcceptEncoding = "zstd" });

// Decode yourself or write to a file
await using var body = await result.ReadAsStreamAsync();

await using var fileStream = File.OpenRead("data.csv");

using var response = await client.InsertRawStreamAsync(
    table: "my_table",
    stream: fileStream,
    format: "CSV",
    columns: ["id", "product", "price"] // Optional: specify columns
);

using ClickHouse.Driver.ADO;

// DataSourceは一度だけ作成する（DIにシングルトンとして登録する）
var dataSource = new ClickHouseDataSource("Host=localhost;Username=default;Password=secret");

// 必要に応じて軽量な接続を作成する
await using var connection = await dataSource.OpenConnectionAsync();

// 接続を使用する
await using var command = connection.CreateCommand("SELECT version()");
var version = await command.ExecuteScalarAsync();

// Startup.cs または Program.cs 内
services.AddSingleton(sp =>
{
    var factory = sp.GetRequiredService<IHttpClientFactory>();
    return new ClickHouseDataSource("Host=localhost", factory, "ClickHouse");
});

// サービス内
public class MyService
{
    private readonly ClickHouseDataSource _dataSource;

    public MyService(ClickHouseDataSource dataSource)
    {
        _dataSource = dataSource;
    }

    public async Task DoWorkAsync()
    {
        await using var connection = await _dataSource.OpenConnectionAsync();
        // 接続を使用...
    }
}

await using var connection = await dataSource.OpenConnectionAsync();

// SQLでコマンドを作成する
await using var command = connection.CreateCommand("SELECT * FROM my_table WHERE id = {id:Int64}");
command.AddParameter("id", 42L);

// 実行して結果を読み取る
await using var reader = await command.ExecuteReaderAsync();
while (reader.Read())
{
    Console.WriteLine($"Name: {reader.GetString("name")}");
}

await using var reader = await command.ExecuteReaderAsync();

while (reader.Read())
{
    // カラムインデックスによるアクセス
    var id = reader.GetInt64(0);
    var name = reader.GetString(1);

    // カラム名によるアクセス
    var email = reader.GetString("email");

    // 汎用アクセス
    var timestamp = reader.GetFieldValue<DateTime>("created_at");

    // nullチェック
    if (!reader.IsDBNull("optional_field"))
    {
        var value = reader.GetString("optional_field");
    }
}

// CustomSettingsを使用する場合
var settings = new ClickHouseClientSettings("Host=localhost");
settings.CustomSettings["async_insert"] = 1;
settings.CustomSettings["wait_for_async_insert"] = 1; // 推奨: フラッシュの完了確認を待機する

// または接続文字列を使用する場合
// "Host=localhost;set_async_insert=1;set_wait_for_async_insert=1"

var settings = new ClickHouseClientSettings
{
    Host = "localhost",
    UseSession = true,
    SessionId = "my-session", // 省略可能 -- 指定しない場合は自動生成されます
};

using var client = new ClickHouseClient(settings);

await client.ExecuteNonQueryAsync("CREATE TEMPORARY TABLE temp_ids (id UInt64)");
await client.ExecuteNonQueryAsync("INSERT INTO temp_ids VALUES (1), (2), (3)");

var reader = await client.ExecuteReaderAsync(
    "SELECT * FROM users WHERE id IN (SELECT id FROM temp_ids)"
);

var settings = new ClickHouseClientSettings
{
    Host = "localhost",
    UseSession = true,
    SessionId = "my-session",
};

var dataSource = new ClickHouseDataSource(settings);
await using var connection = await dataSource.OpenConnectionAsync();

await using var cmd1 = connection.CreateCommand("CREATE TEMPORARY TABLE temp_ids (id UInt64)");
await cmd1.ExecuteNonQueryAsync();

await using var cmd2 = connection.CreateCommand("INSERT INTO temp_ids VALUES (1), (2), (3)");
await cmd2.ExecuteNonQueryAsync();

await using var cmd3 = connection.CreateCommand("SELECT * FROM users WHERE id IN (SELECT id FROM temp_ids)");
await using var reader = await cmd3.ExecuteReaderAsync();

var reader = (ClickHouseDataReader)await connection.ExecuteReaderAsync(
    "SELECT toDateTime('2024-06-15 14:30:00', 'Europe/Amsterdam')");
reader.Read();

var dt = reader.GetDateTime(0);    // 2024-06-15 14:30:00, Kind=Unspecified（未指定）
var dto = reader.GetDateTimeOffset(0); // 2024-06-15 14:30:00 +02:00 (CEST)

// 設定でStringモードを構成する
var settings = new ClickHouseClientSettings("Host=localhost")
{
    JsonReadMode = JsonReadMode.String
};

// または接続文字列で指定する
// "Host=localhost;JsonReadMode=String"

var utcTime = new DateTime(2024, 1, 15, 12, 0, 0, DateTimeKind.Utc);
// Stored as 12:00 UTC
// Read from DateTime('Europe/Amsterdam') column: 13:00 (UTC+1)
// Read from DateTime('UTC') column: 12:00 UTC

var wallClock = new DateTime(2024, 1, 15, 14, 30, 0, DateTimeKind.Unspecified);
// Written to DateTime('Europe/Amsterdam') column: stored as 14:30 Amsterdam time
// Read back from DateTime('Europe/Amsterdam') column: 14:30

// 正しい例: SQLの型ヒントにタイムゾーンを指定 - 型は自動的に抽出される
command.CommandText = "INSERT INTO table (dt_amsterdam) VALUES ({dt:DateTime('Europe/Amsterdam')})";
command.AddParameter("dt", myDateTime);

// 誤った例: タイムゾーンヒントなしの場合、UTCとして解釈される
command.CommandText = "INSERT INTO table (dt_amsterdam) VALUES ({dt:DateTime})";
command.AddParameter("dt", myDateTime);
// 文字列値 "2024-01-15 14:30:00" はアムステルダム時間ではなくUTCとして解釈される！

// デフォルトのStringモードはあらゆる入力で動作
await client.InsertBinaryAsync(
    "my_table",
    new[] { "id", "data" },
    new[] { new object[] { 1u, new { name = "test", value = 42 } } }
);

// Binaryモードは明示的なオプトインと型の登録が必要
var settings = new ClickHouseClientSettings("Host=localhost")
{
    JsonWriteMode = JsonWriteMode.Binary
};
using var client = new ClickHouseClient(settings);
client.RegisterJsonSerializationType<MyPocoType>();

CREATE TABLE events (
    id UInt32,
    data JSON(`user.id` Int64, `user.name` String, Timestamp DateTime64(3))
) ENGINE = MergeTree() ORDER BY id

using ClickHouse.Driver.Json;

public class UserEvent
{
    [ClickHouseJsonPath("user.id")]
    public long UserId { get; set; }

    [ClickHouseJsonPath("user.name")]
    public string UserName { get; set; }

    public DateTime Timestamp { get; set; }

    [ClickHouseJsonIgnore]
    public string InternalData { get; set; }  // シリアライズされない
}

// バイナリモードの場合: 型を登録してバイナリモードを有効にする
var settings = new ClickHouseClientSettings("Host=localhost") { JsonWriteMode = JsonWriteMode.Binary };
using var client = new ClickHouseClient(settings);
client.RegisterJsonSerializationType<UserEvent>();

// POCOを挿入 - カスタムパス属性によりネスト構造を持つJSONにシリアライズされる
await client.InsertBinaryAsync(
    "events",
    new[] { "id", "data" },
    new[] { new object[] { 1u, new UserEvent { UserId = 123, UserName = "Alice", Timestamp = DateTime.UtcNow } } }
);
// 生成されるJSON: {"user": {"id": 123, "name": "Alice"}, "Timestamp": "2024-01-15T..."}

CREATE TABLE test.nested (
    id UInt32,
    params Nested (param_id UInt8, param_val String)
) ENGINE = Memory

var row1 = new object[] { 1, new[] { 1, 2, 3 }, new[] { "v1", "v2", "v3" } };
var row2 = new object[] { 2, new[] { 4, 5, 6 }, new[] { "v4", "v5", "v6" } };

await client.InsertBinaryAsync(
    "test.nested",
    new[] { "id", "params.param_id", "params.param_val" },
    new[] { row1, row2 }
);

using ClickHouse.Driver;
using Microsoft.Extensions.Logging;

var loggerFactory = LoggerFactory.Create(builder =>
{
    builder
        .AddConsole()
        .SetMinimumLevel(LogLevel.Information);
});

var settings = new ClickHouseClientSettings("Host=localhost;Port=8123")
{
    LoggerFactory = loggerFactory
};

using var client = new ClickHouseClient(settings);

using ClickHouse.Driver;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;

var configuration = new ConfigurationBuilder()
    .SetBasePath(Directory.GetCurrentDirectory())
    .AddJsonFile("appsettings.json")
    .Build();

var loggerFactory = LoggerFactory.Create(builder =>
{
    builder
        .AddConfiguration(configuration.GetSection("Logging"))
        .AddConsole();
});

var settings = new ClickHouseClientSettings("Host=localhost;Port=8123")
{
    LoggerFactory = loggerFactory
};

using var client = new ClickHouseClient(settings);

using ClickHouse.Driver;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;

var categoriesConfiguration = new Dictionary<string, string>
{
    { "LogLevel:Default", "Warning" },
    { "LogLevel:ClickHouse.Driver.Connection", "Information" },
    { "LogLevel:ClickHouse.Driver.Command", "Debug" }
};

var config = new ConfigurationBuilder()
    .AddInMemoryCollection(categoriesConfiguration)
    .Build();

using var loggerFactory = LoggerFactory.Create(builder =>
{
    builder
        .AddConfiguration(config)
        .AddSimpleConsole();
});

var settings = new ClickHouseClientSettings("Host=localhost;Port=8123")
{
    LoggerFactory = loggerFactory
};

using var client = new ClickHouseClient(settings);

{
    "Logging": {
        "LogLevel": {
            "ClickHouse.Driver.Connection": "Trace",
            "ClickHouse.Driver.Transport": "Trace"
        }
    }
}

var loggerFactory = LoggerFactory.Create(builder =>
{
    builder
        .AddConsole()
        .SetMinimumLevel(LogLevel.Trace); // ネットワークイベントを表示するにはTraceレベルが必要
});

var settings = new ClickHouseClientSettings()
{
    LoggerFactory = loggerFactory,
    EnableDebugMode = true,  // 低レベルのネットワークトレースを有効にする
};

builder.Services.AddOpenTelemetry()
    .WithTracing(tracing => tracing
        .AddSource(ClickHouseDiagnosticsOptions.ActivitySourceName)  // ClickHouse ドライバーのスパンを購読する
        .AddAspNetCoreInstrumentation()
        .AddOtlpExporter());             // または AddJaegerExporter() など

using OpenTelemetry;
using OpenTelemetry.Trace;

var tracerProvider = Sdk.CreateTracerProviderBuilder()
    .AddSource(ClickHouseDiagnosticsOptions.ActivitySourceName)
    .AddConsoleExporter()
    .Build();

using ClickHouse.Driver.Diagnostic;

// スパンにSQLステートメントを含める（デフォルト: セキュリティのためfalse）
ClickHouseDiagnosticsOptions.IncludeSqlInActivityTags = true;

// 長いSQLステートメントを切り詰める（デフォルト: 1000文字）
ClickHouseDiagnosticsOptions.StatementMaxLength = 500;

using System.Net;
using System.Net.Security;
using ClickHouse.Driver;

var handler = new HttpClientHandler
{
    // 圧縮が有効な場合に必要（デフォルトで有効）
    AutomaticDecompression = DecompressionMethods.GZip | DecompressionMethods.Deflate,

    ServerCertificateCustomValidationCallback = (message, cert, chain, sslPolicyErrors) =>
    {
        // 例: 特定の証明書サムプリントを許可する
        if (cert?.Thumbprint == "YOUR_EXPECTED_THUMBPRINT")
            return true;

        // 例: 特定の発行者からの証明書を許可する
        if (cert?.Issuer.Contains("YourOrganization") == true)
            return true;

        // デフォルト: 標準の検証を使用
        return sslPolicyErrors == SslPolicyErrors.None;
    },
};

var httpClient = new HttpClient(handler) { Timeout = TimeSpan.FromMinutes(5) };

var settings = new ClickHouseClientSettings
{
    Host = "my.clickhouse.server",
    Protocol = "https",
    HttpClient = httpClient,
};

using var client = new ClickHouseClient(settings);

// DataSourceをシングルトンとして登録する
var dataSource = new ClickHouseDataSource("Host=localhost;Username=default");

// ORM用のコネクションを作成する
await using var connection = await dataSource.OpenConnectionAsync();
// コネクションをORMに渡す...

var dataSource = new ClickHouseDataSource("Host=localhost");
services.AddSingleton(dataSource); // DIにシングルトンとして登録

using var connection = dataSource.CreateConnection();

await connection.ExecuteAsync(
    "INSERT INTO users (id, name, balance) VALUES (@Id, @Name, @Balance)",
    new { Id = 1, Name = "alice", Balance = 3.14 });

class InsertParams
{
    public int Id { get; set; }
    public string Name { get; set; }
    public double Balance { get; set; }
}

var param = new InsertParams { Id = 42, Name = "bob", Balance = 99.9 };
await connection.ExecuteAsync(
    "INSERT INTO users (id, name, balance) VALUES (@Id, @Name, @Balance)", param);

var parameters = new Dictionary<string, object> { { "Id", 2 } };
var rows = await connection.QueryAsync<User>(
    "SELECT id, name FROM users WHERE id = @Id", parameters);

var dynParams = new DynamicParameters(new { Id = 1 });
// または: new DynamicParameters(new Dictionary<string, object> { { "Id", 1 } });

var rows = await connection.QueryAsync<User>(
    "SELECT id, name FROM users WHERE id = @Id", dynParams);

class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public double Balance { get; set; }
}

// テーブルから
var users = (await connection.QueryAsync<User>("SELECT id, name, balance FROM users")).ToList();

// リテラルから
var row = (await connection.QueryAsync<User>("SELECT 1 as id, 'hello' as name, 2.5 as balance")).Single();

var parameters = new Dictionary<string, object> { { "value", 42 } };
var result = await connection.QueryAsync<int>("SELECT {value:Int32}", parameters);

var rows = await connection.QueryAsync<User>(
    "SELECT id, name FROM users WHERE id IN @Ids ORDER BY id",
    new { Ids = new[] { 1, 3, 5 } });

var parameters = new Dictionary<string, object> { { "ids", new[] { 1, 3, 5 } } };
var rows = await connection.QueryAsync<User>(
    "SELECT id, name FROM users WHERE has({ids:Array(Int32)}, id) ORDER BY id",
    parameters);

// ClickHouseDecimal（Decimal64/128/256カラム用）
SqlMapper.AddTypeHandler(new ClickHouseDecimalHandler());

// BigInteger（Int128/Int256/UInt128/UInt256カラム用）
SqlMapper.AddTypeHandler(new BigIntegerHandler());

// IPAddress（IPv4/IPv6カラム用）
SqlMapper.AddTypeHandler(new IpAddressHandler());

[Table("test.users")]
record class UserRecord(int Id, string Name, DateTime Timestamp);

var all = await connection.GetAllAsync<UserRecord>();
var one = await connection.GetAsync<UserRecord>(1);

using LinqToDB;
using LinqToDB.Data;
using LinqToDB.DataProvider.ClickHouse;

var connectionString = "Host=localhost;Port=8123;Database=default";
var options = new DataOptions()
    .UseClickHouse(connectionString, ClickHouseProvider.ClickHouseDriver);

await using var db = new DataConnection(options);

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

await using var db = new DataConnection(options);

var products = await db.GetTable<Product>()
    .Where(p => p.Price > 100)
    .OrderByDescending(p => p.Name)
    .ToListAsync();

await using var db = new DataConnection(options);
var table = db.GetTable<Product>();

var options = new BulkCopyOptions
{
    MaxBatchSize = 100000,
    MaxDegreeOfParallelism = 1,
    WithoutSession = true
};

await table.BulkCopyAsync(options, products);

dotnet add package ClickHouse.EntityFrameworkCore

using Microsoft.EntityFrameworkCore;

public class PageView
{
    public long Id { get; set; }
    public string Path { get; set; }
    public DateOnly Date { get; set; }
    public string UserAgent { get; set; }
}

public class AnalyticsContext : DbContext
{
    public DbSet<PageView> PageViews { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseClickHouse("Host=localhost;Database=analytics");
}

// クエリ
await using var ctx = new AnalyticsContext();

var topPages = await ctx.PageViews
    .Where(v => v.Date >= new DateOnly(2024, 1, 1))
    .GroupBy(v => v.Path)
    .Select(g => new { Path = g.Key, Views = g.Count() })
    .OrderByDescending(x => x.Views)
    .Take(10)
    .ToListAsync();

optionsBuilder.UseClickHouse(connectionString, o => o.DisableJoinNullSemantics());

await using var ctx = new AnalyticsContext();

ctx.PageViews.Add(new PageView
{
    Id = 1,
    Path = "/home",
    Date = new DateOnly(2024, 6, 15),
    UserAgent = "Mozilla/5.0"
});

await ctx.SaveChangesAsync();

optionsBuilder.UseClickHouse("Host=localhost", o => o.MaxBatchSize(5000));

var events = Enumerable.Range(0, 100_000)
    .Select(i => new PageView
    {
        Id = i,
        Path = $"/page/{i}",
        Date = DateOnly.FromDateTime(DateTime.Today)
    });

long rowsInserted = await ctx.BulkInsertAsync(events);

public enum Status { Active, Inactive, Pending }

public class User
{
    public long Id { get; set; }
    public Status Status { get; set; }
}

// enum値を使ったクエリ
var active = await ctx.Users
    .Where(u => u.Status == Status.Active)
    .ToListAsync();

public class Money
{
    public decimal Amount { get; set; }
    public string Currency { get; set; }
}

public class Order
{
    public long Id { get; set; }
    public Money Price { get; set; }
}

// OnModelCreating 内:
modelBuilder.Entity<Order>()
    .Property(o => o.Price)
    .HasConversion(
        m => $"{m.Amount}|{m.Currency}",
        s => new Money
        {
            Amount = decimal.Parse(s.Split('|')[0]),
            Currency = s.Split('|')[1]
        })
    .HasColumnType("String");

public class MoneyConverter : ValueConverter<Money, string>
{
    public MoneyConverter() : base(
        m => $"{m.Amount}|{m.Currency}",
        s => Parse(s)) { }

    private static Money Parse(string s)
    {
        var parts = s.Split('|');
        return new Money { Amount = decimal.Parse(parts[0]), Currency = parts[1] };
    }
}

// 単一のプロパティに適用する場合:
.HasConversion<MoneyConverter>()

// または、規約を使用して型のすべてのプロパティに適用する場合:
protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
    configurationBuilder.Properties<Money>()
        .HaveConversion<MoneyConverter>();
}

using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;

[Table("sensor_readings")]
public class SensorReading
{
    public long Id { get; set; }

    [Column(TypeName = "Array(String)")]
    public string[] Tags { get; set; }

    [Column(TypeName = "Map(String, String)")]
    public Dictionary<string, string> Metadata { get; set; }

    [Column(TypeName = "Nullable(Float64)")]
    public double? Value { get; set; }

    [Column(TypeName = "Decimal128(18)")]
    public decimal HighPrecision { get; set; }
}

modelBuilder.Entity<SensorReading>(e =>
{
    e.ToTable("sensor_readings");
    e.Property(x => x.Tags).HasColumnType("Array(String)");
    e.Property(x => x.Metadata).HasColumnType("Map(String, String)");
    e.Property(x => x.Value).HasColumnType("Nullable(Float64)");
    e.Property(x => x.Category).HasColumnType("LowCardinality(String)");
    e.Property(x => x.HighPrecision).HasColumnType("Decimal128(18)");
});

public class Event
{
    public long Id { get; set; }
    public object? Payload { get; set; }
}

// OnModelCreating 内:
entity.Property(e => e.Payload).HasColumnType("Variant(String, UInt64, Array(UInt64))");
// または:
entity.Property(e => e.Payload).HasColumnType("Dynamic");

using System.Text.Json.Nodes;

public class Event
{
    public long Id { get; set; }
    public JsonNode? Data { get; set; }
}

// OnModelCreating 内:
entity.Property(e => e.Data).HasColumnType("Json");

ctx.Events.Add(new Event
{
    Id = 1,
    Data = JsonNode.Parse("""{"action": "click", "x": 100, "y": 200}""")
});
await ctx.SaveChangesAsync();

var ev = await ctx.Events.Where(e => e.Id == 1).SingleAsync();
string action = ev.Data!["action"]!.GetValue<string>(); // "click"

public class Event
{
    public long Id { get; set; }
    public string? Data { get; set; }  // 生のJSON文字列
}

entity.Property(e => e.Data).HasColumnType("Json");