EF.CH 0.0.12

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

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 10.0+
• ClickHouse 22.0+
• EF Core 10.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)");

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.

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.

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