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 };

// 第一次调用从服务器拉取 schema
await client.InsertBinaryAsync("my_table", columns, batch1, options);

// 第二次调用复用已缓存的 schema，无需额外往返
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; }
}

// 注册类型（每个客户端生命周期只需注册一次）
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]                     // 排除在插入操作之外
    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; // 推荐：等待 flush 确认

// 或通过 连接字符串
// "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)

// 通过 settings 配置字符串模式
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; }  // 不会被序列化
}

// 对于 Binary 模式：注册类型并启用 Binary 模式
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 驱动的 span
        .AddAspNetCoreInstrumentation()
        .AddOtlpExporter());             // 或使用 AddJaegerExporter() 等

using OpenTelemetry;
using OpenTelemetry.Trace;

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

using ClickHouse.Driver.Diagnostic;

// 在 spans 中包含 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; }
}

// 使用枚举值查询
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");