EF.CH 8.0.7

EF.CH - Entity Framework Core Provider for ClickHouse

An Entity Framework Core provider for ClickHouse, built on the ClickHouse.Driver ADO.NET driver.

Features

• LINQ to ClickHouse SQL - Full query translation with ClickHouse-specific optimizations
• MergeTree Engine Family - MergeTree, ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree, CollapsingMergeTree
• Rich Type System - Arrays, Maps, Tuples, Nested types, Enums, IPv4/IPv6, DateTime64
• Materialized Views - LINQ-based and raw SQL definitions
• Projections - Pre-sorted and pre-aggregated table-level optimizations
• EF Core Migrations - DDL generation with ClickHouse-specific clauses
• DELETE Support - Lightweight and mutation-based strategies
• Dictionaries - In-memory key-value stores with dictGet translation
• External Entities - Query PostgreSQL, MySQL, Redis, and ODBC sources via table functions
• Scaffolding - Reverse engineering with C# enum generation
• Compression Codecs - Per-column compression via fluent API and attributes
• Window Functions - Row numbering, ranking, lag/lead, running totals with fluent API
• Data Skipping Indices - Minmax, bloom filter, token/ngram bloom filters, and set indices
• Time Series Gap Filling - WITH FILL and INTERPOLATE for continuous time series data
• Query Modifiers - FINAL, SAMPLE, PREWHERE, and SETTINGS for query-level hints
• Computed Columns - MATERIALIZED, ALIAS, and DEFAULT expression columns
• Aggregate Combinators - State, Merge, If, and Array combinators for AggregatingMergeTree
• Native JSON Type - ClickHouse 24.8+ subcolumn access with extension method API

Quick Start

// 1. Install the package
// dotnet add package EF.CH

// 2. Create your entity
public class Order
{
    public Guid Id { get; set; }
    public DateTime OrderDate { get; set; }
    public string CustomerId { get; set; } = string.Empty;
    public decimal Total { get; set; }
}

// 3. Create your DbContext
public class MyDbContext : DbContext
{
    public DbSet<Order> Orders => Set<Order>();

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseClickHouse("Host=localhost;Database=mydb");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.UseMergeTree(x => new { x.OrderDate, x.Id });  // Required: ORDER BY
            entity.HasPartitionByMonth(x => x.OrderDate);         // Optional: partitioning
        });
    }
}

// 4. Use it
await using var context = new MyDbContext();
await context.Database.EnsureCreatedAsync();

context.Orders.Add(new Order
{
    Id = Guid.NewGuid(),
    OrderDate = DateTime.UtcNow,
    CustomerId = "customer-123",
    Total = 99.99m
});
await context.SaveChangesAsync();

var recentOrders = await context.Orders
    .Where(o => o.OrderDate > DateTime.UtcNow.AddDays(-7))
    .ToListAsync();

Installation

dotnet add package EF.CH

Requirements:

• .NET 8.0+
• ClickHouse 22.0+
• EF Core 8.0+

ClickHouse Concepts for EF Core Developers

If you're coming from SQL Server or PostgreSQL, ClickHouse works differently. Understanding these differences is essential:

Every Table Needs an ENGINE

Unlike SQL Server where tables "just work", ClickHouse requires you to specify a table engine. The MergeTree family is most common:

// This is REQUIRED - there's no default engine
entity.UseMergeTree(x => new { x.OrderDate, x.Id });

No ACID Transactions

ClickHouse uses eventual consistency. SaveChanges() batches INSERTs but there's no rollback on failure. Design your application accordingly.

No Row-Level UPDATE

ClickHouse doesn't support efficient UPDATE statements. Attempting to update an entity throws NotSupportedException. Instead:

• Use ReplacingMergeTree with a version column for "last write wins" semantics
• Use delete-and-reinsert patterns for infrequent updates

Batch-Oriented, Not Row-at-a-Time

ClickHouse is optimized for bulk inserts (thousands of rows). Single-row inserts work but aren't efficient. Batch your writes when possible.

No Auto-Increment

There's no IDENTITY or auto-increment. Use Guid or application-generated IDs:

public Guid Id { get; set; } = Guid.NewGuid();

No Foreign Key Enforcement

ClickHouse doesn't enforce referential integrity. Foreign keys are your application's responsibility.

Table Engines

Choose the right engine for your use case:

See docs/engines/ for detailed documentation on each engine.

Type Mappings

See docs/types/ for the complete type mapping reference.

Key Differences from SQL Server/PostgreSQL

Table Options

// Partitioning - improves query performance and data management
entity.HasPartitionByMonth(x => x.CreatedAt);  // PARTITION BY toYYYYMM()
entity.HasPartitionByDay(x => x.EventDate);    // PARTITION BY toYYYYMMDD()

// TTL - automatic data expiration
entity.HasTtl("CreatedAt + INTERVAL 90 DAY");

// Sampling - for approximate queries on large datasets
entity.HasSampleBy("intHash32(UserId)");

Computed Columns

ClickHouse supports three types of computed columns:

public class Order
{
    public Guid Id { get; set; }
    public decimal Amount { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;

    // MATERIALIZED - computed on INSERT, stored on disk
    [MaterializedColumn("Amount * 1.1")]
    public decimal TotalWithTax { get; set; }

    // ALIAS - computed at query time, not stored
    [AliasColumn("concat(FirstName, ' ', LastName)")]
    public string FullName { get; set; } = string.Empty;

    // DEFAULT expression - computed if no value provided
    [DefaultExpression("now()")]
    public DateTime CreatedAt { get; set; }
}

// Or via fluent API
modelBuilder.Entity<Order>(entity =>
{
    entity.Property(e => e.TotalWithTax).HasMaterializedExpression("Amount * 1.1");
    entity.Property(e => e.FullName).HasAliasExpression("concat(FirstName, ' ', LastName)");
    entity.Property(e => e.CreatedAt).HasDefaultExpression("now()");
});

Note: MATERIALIZED columns are excluded from SELECT * by default. Use explicit column selection to read them.

See docs/features/computed-columns.md for full documentation.

Query Modifiers

ClickHouse-specific query hints via LINQ extension methods:

using EF.CH.Extensions;

// FINAL - force deduplication for ReplacingMergeTree
var users = await context.Users
    .Final()
    .ToListAsync();

// SAMPLE - probabilistic sampling (~10% of rows)
var sample = await context.Events
    .Sample(0.1)
    .ToListAsync();

// PREWHERE - optimized pre-filtering (reads filter columns first)
var filtered = await context.Events
    .PreWhere(e => e.Date > cutoffDate)
    .ToListAsync();

// SETTINGS - query-level execution hints
var events = await context.Events
    .WithSetting("max_threads", 4)
    .ToListAsync();

When to use PREWHERE:

• Filter on indexed/sorted columns (ORDER BY key columns)
• Highly selective filters that eliminate most rows
• Large tables where I/O reduction matters

See docs/features/query-modifiers.md for full documentation.

DELETE Operations

// Via change tracker (lightweight delete by default)
var entity = await context.Orders.FindAsync(id);
context.Orders.Remove(entity);
await context.SaveChangesAsync();

// Bulk delete
await context.Orders
    .Where(o => o.OrderDate < cutoffDate)
    .ExecuteDeleteAsync();

// Configure mutation-based delete
options.UseClickHouse("...", o => o.UseDeleteStrategy(ClickHouseDeleteStrategy.Mutation));

Window Functions

using EF.CH.Extensions;

var analytics = context.Orders.Select(o => new
{
    o.Id,
    // Lambda style (recommended) - no .Value needed
    RowNum = Window.RowNumber(w => w
        .PartitionBy(o.Region)
        .OrderBy(o.OrderDate)),

    PrevAmount = Window.Lag(o.Amount, 1, w => w
        .OrderBy(o.OrderDate)),

    RunningTotal = Window.Sum(o.Amount, w => w
        .PartitionBy(o.Region)
        .OrderBy(o.OrderDate)
        .Rows().UnboundedPreceding().CurrentRow())
});

Available Functions: RowNumber, Rank, DenseRank, PercentRank, NTile, Lag, Lead, FirstValue, LastValue, NthValue, Sum, Avg, Count, Min, Max

See docs/features/window-functions.md for full documentation including fluent API style.

Time Series Gap Filling

Fill gaps in time series data with ClickHouse's WITH FILL and INTERPOLATE clauses:

using EF.CH.Extensions;

// Basic gap filling - insert missing hourly rows
var hourlyData = context.Readings
    .OrderBy(x => x.Hour)
    .Interpolate(x => x.Hour, TimeSpan.FromHours(1));

// With FROM/TO bounds for complete date range
var fullRange = context.Readings
    .OrderBy(x => x.Date)
    .Interpolate(x => x.Date, TimeSpan.FromDays(1), startDate, endDate);

// Forward-fill values from previous row
var filledData = context.Readings
    .OrderBy(x => x.Hour)
    .Interpolate(x => x.Hour, TimeSpan.FromHours(1),
                 x => x.Value, InterpolateMode.Prev);

// Multiple columns with builder
var multiColumn = context.Readings
    .OrderBy(x => x.Hour)
    .Interpolate(x => x.Hour, TimeSpan.FromHours(1), i => i
        .Fill(x => x.Temperature, InterpolateMode.Prev)
        .Fill(x => x.Count, 0));

Step Types:

See docs/features/interpolate.md for full documentation.

Data Skipping Indices

Skip indices allow ClickHouse to skip reading granules that don't match query predicates, dramatically improving query performance for selective filters.

modelBuilder.Entity<LogEvent>(entity =>
{
    entity.UseMergeTree(x => new { x.Timestamp, x.Id });

    // Minmax for datetime range queries
    entity.HasIndex(x => x.Timestamp)
        .UseMinmax()
        .HasGranularity(4);

    // Bloom filter for array membership (has(Tags, 'error'))
    entity.HasIndex(x => x.Tags)
        .UseBloomFilter(falsePositive: 0.025)
        .HasGranularity(3);

    // Token bloom filter for log search (LIKE '%exception%')
    entity.HasIndex(x => x.Message)
        .UseTokenBF(size: 10240, hashes: 3, seed: 0)
        .HasGranularity(4);

    // Set index for low-cardinality columns
    entity.HasIndex(x => x.Status)
        .UseSet(maxRows: 100)
        .HasGranularity(2);
});

Or use attributes:

public class LogEvent
{
    [MinMaxIndex(Granularity = 4)]
    public DateTime Timestamp { get; set; }

    [BloomFilterIndex(FalsePositive = 0.025, Granularity = 3)]
    public string[] Tags { get; set; } = [];

    [TokenBFIndex(Granularity = 4)]
    public string Message { get; set; } = string.Empty;

    [SetIndex(MaxRows = 100, Granularity = 2)]
    public string Status { get; set; } = string.Empty;
}

Index Types:

See docs/features/skip-indices.md for full documentation.

Materialized Views

// LINQ-based (type-safe)
modelBuilder.Entity<HourlySummary>(entity =>
{
    entity.UseSummingMergeTree(x => new { x.Hour, x.ProductId });
    entity.AsMaterializedView<HourlySummary, Order>(
        query: orders => orders
            .GroupBy(o => new { Hour = o.OrderDate.Date, o.ProductId })
            .Select(g => new HourlySummary
            {
                Hour = g.Key.Hour,
                ProductId = g.Key.ProductId,
                OrderCount = g.Count(),
                TotalRevenue = g.Sum(o => o.Total)
            }),
        populate: false);
});

Projections

Projections are table-level optimizations stored alongside the main table data. Unlike materialized views, projections are not separately queryable - the query optimizer automatically uses them when beneficial.

// Sort-order projection - auto-named: orders__prj_ord__customer_id__order_date
entity.HasProjection()
    .OrderBy(x => x.CustomerId)
    .ThenBy(x => x.OrderDate)
    .Build();

// Aggregation projection - explicit name, anonymous type
entity.HasProjection("daily_stats")
    .GroupBy(x => x.OrderDate.Date)
    .Select(g => new {
        Date = g.Key,
        TotalAmount = g.Sum(o => o.Amount),
        OrderCount = g.Count()
    })
    .Build();

// ClickHouse-specific aggregates (uniq, argMax, quantile, etc.)
entity.HasProjection("advanced_stats")
    .GroupBy(x => x.Date)
    .Select(g => new {
        Date = g.Key,
        UniqueUsers = ClickHouseAggregates.Uniq(g, o => o.UserId),
        TopProduct = ClickHouseAggregates.ArgMax(g, o => o.ProductId, o => o.Revenue)
    })
    .Build();

See docs/features/projections.md for full documentation including all ClickHouse aggregate functions.

Aggregate Combinators

ClickHouse aggregate combinators modify aggregate function behavior. Use these for AggregatingMergeTree workflows with full LINQ support:

using EF.CH.Extensions;

// Store aggregate states in byte[] columns
public class HourlySummary
{
    public DateTime Hour { get; set; }
    public byte[] CountState { get; set; } = [];
    public byte[] SumAmountState { get; set; } = [];
    public byte[] AvgTimeState { get; set; } = [];
}

// Configure AggregateFunction columns
modelBuilder.Entity<HourlySummary>(entity =>
{
    entity.HasNoKey();
    entity.UseAggregatingMergeTree(x => x.Hour);

    entity.Property(e => e.CountState).HasAggregateFunction("count", typeof(ulong));
    entity.Property(e => e.SumAmountState).HasAggregateFunction("sum", typeof(long));
    entity.Property(e => e.AvgTimeState).HasAggregateFunction("avg", typeof(double));
});

// Materialize with State combinators
.Select(g => new HourlySummary
{
    Hour = g.Key,
    CountState = g.CountState(),              // countState()
    SumAmountState = g.SumState(x => x.Amount), // sumState(Amount)
    AvgTimeState = g.AvgState(x => x.ResponseTime)
})

// Query with Merge combinators to produce final values
context.HourlySummary
    .GroupBy(s => s.Hour.Date)
    .Select(g => new
    {
        Date = g.Key,
        TotalCount = g.CountMerge(s => s.CountState),     // countMerge()
        TotalAmount = g.SumMerge<HourlySummary, long>(s => s.SumAmountState),
        AvgTime = g.AvgMerge(s => s.AvgTimeState)
    })

// Conditional aggregation with If combinators
.Select(g => new
{
    HighValueCount = g.CountIf(x => x.Amount > 1000),      // countIf()
    HighValueSum = g.SumIf(x => x.Amount, x => x.Amount > 1000)
})

// Array element aggregation
.Select(p => new
{
    TotalPrices = p.Prices.ArraySum(),   // arraySum()
    AvgPrice = p.Prices.ArrayAvg()       // arrayAvg()
})

See docs/features/aggregate-combinators.md for full documentation.

Native JSON Type

Query JSON columns with ClickHouse 24.8+ native subcolumn syntax via extension methods.

using EF.CH.Extensions;

// Configure JSON column
modelBuilder.Entity<Event>(entity =>
{
    entity.Property(e => e.Payload)
        .HasColumnType("JSON")
        .HasMaxDynamicPaths(2048);  // Optional
});

// Query with extension methods - translates to subcolumn syntax
var results = await context.Events
    .Where(e => e.Payload.GetPath<string>("user.email") == "test@example.com")
    .Where(e => e.Payload.HasPath("metrics.score"))
    .Select(e => new {
        e.Id,
        Email = e.Payload.GetPath<string>("user.email"),
        Score = e.Payload.GetPathOrDefault<int>("metrics.score", 0),
        FirstTag = e.Payload.GetPath<string>("tags[0]")
    })
    .ToListAsync();

// Generated SQL:
// SELECT "Id", "Payload"."user"."email",
//        ifNull("Payload"."metrics"."score", 0),
//        "Payload"."tags"[1]
// FROM "Events"
// WHERE "Payload"."user"."email" = 'test@example.com'
//   AND "Payload"."metrics"."score" IS NOT NULL

Extension Methods:

Typed POCO Support:

public class Order
{
    [ClickHouseJson(IsTyped = true)]
    public OrderMetadata Metadata { get; set; } = new();
}

See docs/types/json.md for full documentation.

Dictionaries

ClickHouse dictionaries are in-memory key-value stores for fast lookups:

// Define dictionary entity with marker interface
public class CountryLookup : IClickHouseDictionary
{
    public ulong Id { get; set; }
    public string Name { get; set; } = string.Empty;
}

// Configure in OnModelCreating
entity.AsDictionary<CountryLookup, Country>(cfg => cfg
    .HasKey(x => x.Id)
    .FromTable()
    .UseHashedLayout()
    .HasLifetime(300));

// Use in LINQ queries - translates to dictGet()
var orders = db.Orders
    .Select(o => new {
        o.Id,
        CountryName = db.CountryDict.Get(o.CountryId, c => c.Name)
    });

Layouts: Flat, Hashed, ComplexKeyHashed, Cache, Direct

External Entities

Query remote databases directly through ClickHouse table functions:

// Define external entity (keyless - uses table function, not a ClickHouse table)
public class ExternalCustomer
{
    public int id { get; set; }
    public string name { get; set; } = string.Empty;
    public string email { get; set; } = string.Empty;
}

// Configure in OnModelCreating
modelBuilder.ExternalPostgresEntity<ExternalCustomer>(ext => ext
    .FromTable("customers", schema: "public")
    .Connection(c => c
        .HostPort(env: "PG_HOST")
        .Database(env: "PG_DATABASE")
        .Credentials("PG_USER", "PG_PASSWORD")));

// Query like any other entity - generates postgresql() table function
var customers = await context.ExternalCustomers
    .Where(c => c.name.StartsWith("A"))
    .ToListAsync();

// JOIN with native ClickHouse tables
var orderSummary = await context.Orders
    .Join(context.ExternalCustomers, o => o.CustomerId, c => c.id,
        (o, c) => new { c.name, o.Amount })
    .ToListAsync();

Supported Providers:

See docs/features/external-entities.md for detailed configuration.

Documentation

Samples

License

MIT License - see LICENSE for details.

Acknowledgments

• ClickHouse.Driver - The ADO.NET driver this provider builds on
• EntityFrameworkCore.ClickHouse - Reference implementation