EF.CH 0.0.2

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
• 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
• Scaffolding - Reverse engineering with C# enum generation

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

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

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

Documentation

Samples

License

MIT License - see LICENSE for details.

Acknowledgments

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