ClickHouse Entity Framework Core Provider

The official Entity Framework Core provider for ClickHouse, built on top of ClickHouse.Driver.

Detailed documentation is available on the ClickHouse website.

Getting Started

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();

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

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

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

Supported Types

Category	ClickHouse Types	CLR Types
Integers	Int8/Int16/Int32/Int64, UInt8/UInt16/UInt32/UInt64	sbyte, short, int, long, byte, ushort, uint, ulong
Big integers	Int128, Int256, UInt128, UInt256	BigInteger
Floats	Float32, Float64, BFloat16	float, double
Decimals	Decimal(P,S), Decimal32(S), Decimal64(S), Decimal128(S), Decimal256(S)	decimal or ClickHouseDecimal (use ClickHouseDecimal for Decimal128/256 to avoid .NET decimal overflow)
Bool	Bool	bool
Strings	String, FixedString(N)	string
Enums	Enum8(...), Enum16(...)	string or C# enum
Date/time	Date, Date32, DateTime, DateTime64(P, 'TZ')	DateOnly, DateTime
Time	Time, Time64(N)	TimeSpan
UUID	UUID	Guid
Network	IPv4, IPv6	IPAddress
Arrays	Array(T)	T[] or List<T>
Maps	Map(K, V)	Dictionary<K,V>
Tuples	Tuple(T1, ...)	Tuple<...> or ValueTuple<...>
Variant	Variant(T1, T2, ...)	object
Dynamic	Dynamic	object
JSON	Json	JsonNode or string
Geographic	Point, Ring, LineString, Polygon, MultiLineString, MultiPolygon, Geometry	Tuple<double,double> and arrays thereof; object for Geometry
Wrappers	Nullable(T), LowCardinality(T)	Unwrapped automatically

Current Status

This provider is in active development. It supports LINQ queries, inserts, table engine configuration, and migrations — you can define ClickHouse tables with engine-specific settings, create them via dotnet ef migrations or EnsureCreated, query with LINQ, and write data via SaveChanges.

LINQ Queries

Where, OrderBy, Take, Skip, Select, First, Single, Any, Count, Distinct, AsNoTracking

Joins

Join (INNER JOIN), GroupJoin + SelectMany + DefaultIfEmpty (LEFT JOIN), SelectMany (CROSS JOIN), and self-joins. Joining a local sequence (int[], string[], byte[]) also works, as does Contains against a local collection (T[], List<T>, and the ICollection/IList/IReadOnlyList interfaces), which becomes an IN predicate.

ClickHouse returns column defaults (0, "") instead of NULL for unmatched LEFT JOIN rows unless join_use_nulls=1 is set. The provider adds this setting to the connection automatically so LEFT JOIN gives the .NET semantics you expect. To opt out, use DisableJoinNullSemantics():

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

Subqueries

Contains over an IQueryable (IN (SELECT …)), Any (EXISTS), All, correlated scalar subqueries in a projection, and subqueries in FROM.

ClickHouse returns NULL from a scalar subquery that matches no rows, where standard SQL COUNT returns 0. The provider wraps COUNT and SUM scalar subqueries in ifNull(…, 0) when the target CLR type is a non-nullable value type, so a customer with no orders projects 0 rather than throwing.

Set Operations

Concat (UNION ALL), Union (UNION DISTINCT), Intersect, and Except, including chained and nested combinations. ClickHouse leaves union_default_mode empty and rejects a bare UNION, so the provider always emits an explicit ALL or DISTINCT modifier.

GROUP BY & Aggregates

GroupBy with Count, LongCount, Sum, Average, Min, Max — including HAVING (.Where() after .GroupBy()), multiple aggregates in a single projection, and OrderBy on aggregate results.

String Methods

Contains, StartsWith, EndsWith, IndexOf, Replace, Substring, Trim/TrimStart/TrimEnd, ToLower, ToUpper, Length, IsNullOrEmpty, Concat (and + operator)

Math Functions

Math.Abs, Floor, Ceiling, Round, Truncate, Pow, Sqrt, Cbrt, Exp, Log, Log2, Log10, Sign, Sin, Cos, Tan, Asin, Acos, Atan, Atan2, RadiansToDegrees, DegreesToRadians, IsNaN, IsInfinity, IsFinite, IsPositiveInfinity, IsNegativeInfinity — with both Math and MathF overloads.

Date/Time Functions

The ClickHouse toStartOf* family is exposed through EF.Functions, so you can bucket and truncate timestamps directly in queries, including in GROUP BY:

ToStartOfYear, ToStartOfQuarter, ToStartOfMonth, ToStartOfWeek (with an optional ClickHouse week mode), ToStartOfDay, ToStartOfHour, ToStartOfMinute, ToStartOfSecond, ToStartOfFiveMinutes, ToStartOfTenMinutes, ToStartOfFifteenMinutes, and the general ToStartOfInterval(source, value, unit).

// Truncate to the start of the month
var monthly = await ctx.Events
    .Select(e => EF.Functions.ToStartOfMonth(e.Timestamp))
    .ToListAsync();

// Bucket into 15-minute intervals and count per bucket
var buckets = await ctx.Events
    .GroupBy(e => EF.Functions.ToStartOfInterval(e.Timestamp, 15, ClickHouseInterval.Minute))
    .Select(g => new { Bucket = g.Key, Count = g.Count() })
    .ToListAsync();

ToStartOfInterval takes a ClickHouseInterval unit (Second, Minute, Hour, Day, Week, Month, Quarter, Year) — from the ClickHouse.EntityFrameworkCore.Metadata namespace — and emits toStartOfInterval(source, toInterval<unit>(value)). The unit must be an inline enum constant. The interval size and optional ToStartOfWeek mode may be literals or captured query parameters, but cannot depend on values from the current row.

Input and return types follow ClickHouse. The calendar buckets (ToStartOfYear/Quarter/Month/Week) return Date; ToStartOfDay and the hour/minute buckets return DateTime; ToStartOfSecond returns DateTime64. They all accept DateTime and DateTime64 columns, and the plain truncation functions also accept DateOnly (Date/Date32). ToStartOfInterval is the exception: older ClickHouse rejects a DateOnly (Date/Date32) source with Illegal type Date32 of 1st argument while recent versions accept it. Prefer a DateTime/DateTime64 column for interval bucketing.

Date range: those default result types (Date, DateTime) only span 1970–2149/2106, so ClickHouse narrows values outside that window — a pre-1970 date is clamped to the epoch (calendar buckets) or wraps around (sub-day/interval buckets). To preserve the full range, enable enable_extended_results_for_datetime_functions for your session — e.g. add set_enable_extended_results_for_datetime_functions=1 to the connection string — which makes ClickHouse return Date32/DateTime64 instead.

ToStartOfWeek defaults to ClickHouse week mode 0 (Sunday-based); pass a mode to change it.

INSERT via SaveChanges

SaveChanges supports INSERT operations using the driver's native InsertBinaryAsync API — RowBinary encoding with GZip compression, far more efficient than parameterized SQL.

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();

Entities transition from Added to Unchanged after save, just like any other EF Core provider.

Batch size is configurable (default 1000) — controls how many entities are accumulated before flushing to ClickHouse:

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

Bulk Insert

For high-throughput loads that don't need change tracking, use BulkInsertAsync:

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

This calls InsertBinaryAsync directly, bypassing EF Core's change tracker entirely. Entities are not tracked after insert.

JSON Columns

The provider supports ClickHouse's Json column type, mapping to System.Text.Json.Nodes.JsonNode or string.

using System.Text.Json.Nodes;

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

// In OnModelCreating:
entity.Property(e => e.Payload).HasColumnType("Json");

Reading and writing JSON works through both SaveChanges and BulkInsertAsync:

ctx.Events.Add(new Event
{
    Id = 1,
    Payload = 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.Payload!["action"]!.GetValue<string>(); // "click"

If you prefer working with raw JSON strings, map the property as string with a Json column type — the provider will store and retrieve the raw JSON string as-is:

public class Event
{
    public long Id { get; set; }
    public string? Payload { get; set; }  // raw JSON string
}

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

Querying JSON paths

Indexing a JsonNode property translates to ClickHouse's native dot and subscript syntax, so filters and projections run on the server:

// WHERE CAST(`e`.`payload`.`age` AS Int32) > 20
//   AND CAST(`e`.`payload`.`username` AS String) = 'alice_dev'
var users = await ctx.Events
    .Where(e => (int)e.Payload!["age"]! > 20
             && e.Payload!["username"]!.GetValue<string>() == "alice_dev")
    .ToListAsync();

Paths nest to any depth (e.Payload!["meta"]!["runtime"]!["cpu_limit"]!), and array subscripts work too — the provider converts the 0-based .NET index to ClickHouse's 1-based one, so ["orders"]![0] emits .orders[1].

Both .GetValue<T>() and an explicit cast emit a CAST(… AS <storeType>), with the store type taken from the target CLR type. Keys and array indices must be compile-time constants. A variable key does not translate: in a Where it throws, and it can only be client-evaluated in a final Select.

simpleJSON* functions

For JSON held in a String column (not the native Json type), use the EF.Functions helpers, which map to ClickHouse's simpleJSON* family:

var clicks = await ctx.Logs
    .Where(l => EF.Functions.SimpleJsonExtractString(l.RawPayload, "action") == "click")
    .ToListAsync();

SimpleJsonExtractBool, SimpleJsonExtractFloat, SimpleJsonExtractInt, SimpleJsonExtractRaw, SimpleJsonExtractString, SimpleJsonExtractUInt, SimpleJsonHas.

Limitations:

• No owned entity mapping — .ToJson() / StructuralJsonTypeMapping is not supported. JSON columns are opaque JsonNode or string values.
• JsonElement / JsonDocument not supported — only JsonNode and string CLR types are mapped.
• NULL semantics — ClickHouse's JSON type returns {} (empty object) for NULL values rather than SQL NULL. A row inserted with Data = null will read back as an empty JsonNode, not null.
• Integer precision — ClickHouse JSON stores all integers as Int64 unless the path is typed otherwise. When inspecting a materialized JsonNode in memory, use GetValue<long>() rather than GetValue<int>(). This does not apply to a translated path query, where GetValue<int>() emits an explicit CAST(… AS Int32) that the server applies.

Table Engine Configuration

Configure ClickHouse table engines, ordering, partitioning, and more via EF Core's fluent API:

modelBuilder.Entity<SensorReading>(b =>
{
    b.HasKey(e => e.Id);
    b.Property(e => e.Temperature).HasCodec("Delta, ZSTD");
    b.Property(e => e.Location).HasColumnComment("Installation site");
    b.HasIndex(e => e.Timestamp)
        .HasSkippingIndexType("minmax")
        .HasGranularity(4);
    b.ToTable("sensor_readings", t => t
        .HasReplacingMergeTreeEngine("Version")
        .WithOrderBy("Id", "Timestamp")
        .WithPartitionBy("toYYYYMM(Timestamp)")
        .WithPrimaryKey("Id")
        .WithTtl("Timestamp + INTERVAL 1 YEAR")
        .WithSetting("index_granularity", "4096"));
});

Supported engines: MergeTree, ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree, CollapsingMergeTree, VersionedCollapsingMergeTree, GraphiteMergeTree, Log, TinyLog, StripeLog, Memory

Column-level DDL: .HasCodec("Delta, ZSTD"), .HasColumnTtl("expr"), .HasColumnComment("text")

Data-skipping indices: .HasSkippingIndexType("minmax"), .HasGranularity(4), .HasSkippingIndexParams("100")

Engine settings: .WithSetting("index_granularity", "4096") — any ClickHouse setting as a key-value pair

Default behavior: If no engine is configured, the provider defaults to MergeTree with the EF primary key as ORDER BY.

Migrations

The provider supports dotnet ef migrations for creating and applying migrations:

dotnet ef migrations add InitialCreate
dotnet ef database update

EnsureCreated() / EnsureDeleted() also work for quick setup without migrations.

Supported migration operations:

• CREATE TABLE with full ENGINE clause (all engine types, ORDER BY, PARTITION BY, PRIMARY KEY, SAMPLE BY, TTL, SETTINGS, codecs, comments, data-skipping indices)
• ADD COLUMN, DROP COLUMN, MODIFY COLUMN, RENAME COLUMN, RENAME TABLE
• DROP TABLE, CREATE/DROP INDEX (data-skipping)
• Custom ClickHouseCreateDatabaseOperation / ClickHouseDropDatabaseOperation

ClickHouse limitations reflected in migrations:

• ALTER TABLE cannot change engine, ORDER BY, PARTITION BY, or other structural metadata — the provider throws NotSupportedException with a clear message
• Foreign keys, unique constraints, and sequences throw NotSupportedException
• Primary key add/drop is a no-op (ClickHouse PK is structural, not a constraint)
• Idempotent scripts (--idempotent) are not supported (ClickHouse has no conditional SQL blocks)
• Transactions are suppressed (ClickHouse does not support them)

Not Yet Implemented

• UPDATE / DELETE (ClickHouse mutations are async, not OLTP-compatible)
• Server-generated values — identity columns, RETURNING, computed defaults read back after insert
• Reverse engineering / scaffolding (dotnet ef dbcontext scaffold)
• Owned entities mapped to JSON (.ToJson())
• Queries that EF Core lowers to CROSS APPLY / OUTER APPLY, which ClickHouse has no equivalent for. This covers correlated SelectMany that selects the outer element or entity, Take inside a collection projection, and correlated collections over a UNION source.
• Set operations after a client projection (for example Union(...).FirstOrDefault() on a client-evaluated shape)

Building

dotnet build
dotnet test    # requires Docker (uses Testcontainers)

Targets .NET 10.0, EF Core 10.