Giter VIP home page Giter VIP logo

entityframeworkcore.clickhouse's Introduction

ClickHouse provider for Entity Framework Core

GitHub Tag NuGet Count Issues Open

Quick start

  1. Create console app
  2. Install the necessary packages
dotnet add package EntityFrameworkCore.ClickHouse
dotnet add package Spectre.Console.Cli
class MyFirstTable
{
    public uint UserId { get; set; }

    public string Message { get; set; }

    public DateTime Timestamp { get; set; }

    public float Metric { get; set; }
}

class QuickStartDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);

        optionsBuilder.UseClickHouse("Host=localhost;Protocol=http;Port=8123;Database=QuickStart");
    }

    public DbSet<MyFirstTable> MyFirstTable { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        var entityTypeBuilder = modelBuilder.Entity<MyFirstTable>();

        entityTypeBuilder.Property(e => e.UserId).HasColumnName("user_id");
        entityTypeBuilder.Property(e => e.Message).HasColumnName("message");
        entityTypeBuilder.Property(e => e.Timestamp).HasColumnName("timestamp");
        entityTypeBuilder.Property(e => e.Metric).HasColumnName("metric");

        entityTypeBuilder.HasKey(e => new { e.UserId, e.Timestamp });

        entityTypeBuilder.ToTable("my_first_table", table => table
            .HasMergeTreeEngine()
            .WithPrimaryKey("user_id", "timestamp"));
    }
}

class Program
{
    static async Task Main(string[] args)
    {
        await using var context = new QuickStartDbContext();
        await context.Database.EnsureCreatedAsync();

        await context.MyFirstTable.AddRangeAsync(
            new MyFirstTable
            {
                UserId = 101,
                Message = "Hello, ClickHouse!",
                Timestamp = DateTime.Now,
                Metric = -1f
            },
            new MyFirstTable
            {
                UserId = 102,
                Message = "Insert a lot of rows per batch",
                Timestamp = DateTime.Now.AddDays(-1),
                Metric = 1.41421f
            },
            new MyFirstTable
            {
                UserId = 102,
                Message = "Sort your data based on your commonly-used queries",
                Timestamp = DateTime.Today,
                Metric = 2.718f
            },
            new MyFirstTable
            {
                UserId = 101,
                Message = "Granules are the smallest chunks of data read",
                Timestamp = DateTime.Now.AddSeconds(5),
                Metric = 3.14159f
            });

        await context.SaveChangesAsync();

        var data = context.MyFirstTable.OrderBy(e => e.Timestamp).ToArray();

        var table = new Table()
            .AddColumns(
                new TableColumn("user_id").RightAligned(),
                new TableColumn("message").LeftAligned(),
                new TableColumn("timestamp").RightAligned(),
                new TableColumn("metric").RightAligned());

        Array.ForEach(data, d => table.AddRow(
            d.UserId.ToString(),
            d.Message,
            d.Timestamp.ToString(CultureInfo.InvariantCulture),
            d.Metric.ToString(CultureInfo.InvariantCulture)));

        AnsiConsole.Write(table);
    }
}
┌─────────┬────────────────────────────────────────────────────┬─────────────────────┬─────────┐
│ user_id │ message                                            │           timestamp │  metric │
├─────────┼────────────────────────────────────────────────────┼─────────────────────┼─────────┤
│     102 │ Insert a lot of rows per batch                     │ 04/29/2024 21:05:26 │ 1.41421 │
│     102 │ Sort your data based on your commonly-used queries │ 04/30/2024 00:00:00 │   2.718 │
│     101 │ Hello, ClickHouse!                                 │ 04/30/2024 21:05:26 │      -1 │
│     101 │ Granules are the smallest chunks of data read      │ 04/30/2024 21:05:31 │ 3.14159 │
└─────────┴────────────────────────────────────────────────────┴─────────────────────┴─────────┘

entityframeworkcore.clickhouse's People

Contributors

denis-ivanov avatar

Stargazers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

Watchers

 avatar

Forkers

sandreykin96

entityframeworkcore.clickhouse's Issues

Can translate, clickhouse cannot execute

SELECT "t"."Sku", "t"."StockRegionCode", "t"."ShipmentDate0", COALESCE(SUM("t"."SalesQty"), 0), COALESCE(SUM("t"."NetSalesPrice" * "t"."ExchangeRate"), 0.0), COALESCE(SUM("t"."NetSalesPrice" * "t"."ExchangeRate"), 0.0) / CAST(COALESCE(SUM("t"."SalesQty"), 0) AS Decimal(32, 9))
FROM (
    SELECT "g"."NetSalesPrice", "g"."SalesQty", "g"."Sku", "s"."StockRegionCode", "o"."ExchangeRate", "toYYYYMM"("g"."ShipmentDate") AS "ShipmentDate0"
    FROM "GP_Order_View" AS "g"
    LEFT JOIN "Site" AS "s" ON "g"."SiteID" = "s"."SiteID"
    LEFT JOIN "ODS_Base_CurrencyMonth" AS "o" ON "g"."Period" = "o"."DayPeriod" AND "g"."Currency" = "o"."CurrencyName"
    WHERE "g"."OrderTypeID" = 10 AND "g"."SourceTalbe" = 'GP_OrderDetail' AND "g"."ShipmentDate" >= '2024-04-01' AND "g"."ShipmentDate" < '2024-04-30'
) AS "t"
GROUP BY "t"."Sku", "t"."StockRegionCode", "t"."ShipmentDate0"

The outer layer cannot access the inner layer, and "As" needs to be added inside. I'm not sure if there is a good solution
Nested syntax is prone to this problem and is quite common

Error
[47] [07000]: Code: 47. DB::Exception: There's no column 't.Sku' in table 't': While processing t.Sku. (UNKNOWN_IDENTIFIER) (version 23.6.2.18 (official build))

Count is not working

When you call Count, the next exception is thrown.

image

The table have more than 5 million records.

Tested on 0.0.13.

Appreciate your help.

AppendIdentityWhereCondition not inplemented

Im using library in project on net6. I want to create initial migration. After executing the command add-migration Init an exception occurs. Maybe i'm doing something wrong or migration dont work?

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation.
---> System.TypeLoadException: Method 'AppendIdentityWhereCondition' in type 'ClickHouse.EntityFrameworkCore.Update.Internal.ClickHouseUpdateSqlGenerator' from assembly 'EntityFrameworkCore.ClickHouse, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null' does not have an implementation.
at ClickHouse.EntityFrameworkCore.Extensions.ClickHouseServiceCollectionExtensions.AddEntityFrameworkClickHouse(IServiceCollection serviceCollection)
at ClickHouse.EntityFrameworkCore.Infrastructure.Internal.ClickHouseOptionsExtension.ApplyServices(IServiceCollection services)
at Microsoft.EntityFrameworkCore.Internal.ServiceProviderCache.ApplyServices(IDbContextOptions options, ServiceCollection services)
at Microsoft.EntityFrameworkCore.Internal.ServiceProviderCache.g__BuildServiceProvider|4_0(IDbContextOptions _, ValueTuple2 arguments) at System.Collections.Concurrent.ConcurrentDictionary2.GetOrAdd[TArg](TKey key, Func`3 valueFactory, TArg factoryArgument)
at Microsoft.EntityFrameworkCore.Internal.ServiceProviderCache.GetOrAdd(IDbContextOptions options, Boolean providerRequired)
at Microsoft.EntityFrameworkCore.DbContext..ctor(DbContextOptions options)

In source not imlementation 'AppendIdentityWhereCondition'

protected override void AppendIdentityWhereCondition(StringBuilder commandStringBuilder, ColumnModification columnModification)
{
}

How to use your library?

Hello!)
I'm trying to implement your library into my project, but I can't install it. Help me please. Are there any examples of how to install it?

public class HistoryDbContext : Microsoft.EntityFrameworkCore.DbContext
{
    #region Constructors

    /// <inheritdoc />
    public HistoryDbContext(DbContextOptions<HistoryDbContext> options) : base(options)
    {
    }

    #endregion
    
    #region DbSets
    
    public DbSet<ChangeHistory> ChangeHistory { get; set; }
    
    #endregion
}
public class HistoryDbContextDesignTimeFactory : IDesignTimeDbContextFactory<HistoryDbContext>
{
    /// <inheritdoc />
    public HistoryDbContext CreateDbContext(string[] args) => CreateDbContext("_");
    
    public HistoryDbContext CreateDbContext(string connectionString)
    {
        var optionsBuilder = new DbContextOptionsBuilder<HistoryDbContext>();
        optionsBuilder.UseClickHouse(connectionString)
            .ReplaceService<IMigrationsIdGenerator, MigrationsIdGenerator>()
            .UseLoggerFactory(LoggerFactory.Create(builder => builder.AddConsole()));
        return new HistoryDbContext(optionsBuilder.Options);
    }
}
dotnet ef migrations add Migration00001 --context HistoryDbContext --project SkinTrove.DataAccess.ClickHouse --startup-project DatabaseMigrator
Unable to resolve service for type 'Microsoft.EntityFrameworkCore.Design.IAnnotationCodeGenerator' while attempting to activate 'Microsoft.EntityFrameworkCore.Migrations.Design.MigrationsCodeGeneratorDependencies'.

insert entity failed with 'not implement exception'

hi, i had use dotnet 5 to make a simple demo,

public class ClickHouseContext : DbContext
    {
        public DbSet<SalesOrder> SalesOrders { get; set; }

        //public DbSet<StoreConfig> StoreConfigs { get; set; }

        private readonly string connectionString;

        public ClickHouseContext(string connectionString)
        {
            this.connectionString = connectionString;
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<SalesOrder>().HasKey(e => e.Id);
            //modelBuilder.Entity<StoreConfig>().HasKey(e => e.Id);


            modelBuilder.Entity<SalesOrder>()
                .HasMergeTreeEngine(new Expression<Func<SalesOrder, object>>[]
                {
                        e => e.Id
                });
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //optionsBuilder.LogTo(ClickHouseContext.WriteLine);
            optionsBuilder.UseClickHouse(this.connectionString);
        }
    }

POCO.SalesOrder so = new POCO.SalesOrder() { Id = 0, SheetID = "11aa" };
string cnstr = "Host=192.168.1.43;Port=9090;User=default;Password=by123456";
EF.ClickHouseContext ctx = new EF.ClickHouseContext(cnstr);

ctx.Database.EnsureCreated();   //it is ok here,the table had been created.

ctx.SalesOrders.Add(so);
var ret = ctx.SaveChanges();  //failed here 

the stacktrace is :

   at ClickHouse.EntityFrameworkCore.Update.Internal.ClickHouseUpdateSqlGenerator.AppendRowsAffectedWhereCondition(StringBuilder commandStringBuilder, Int32 expectedRowsAffected)
   at Microsoft.EntityFrameworkCore.Update.UpdateSqlGenerator.AppendWhereAffectedClause(StringBuilder commandStringBuilder, IReadOnlyList`1 operations)
   at Microsoft.EntityFrameworkCore.Update.UpdateSqlGenerator.AppendSelectAffectedCommand(StringBuilder commandStringBuilder, String name, String schema, IReadOnlyList`1 readOperations, IReadOnlyList`1 conditionOperations, Int32 commandPosition)
   at Microsoft.EntityFrameworkCore.Update.UpdateSqlGenerator.AppendInsertOperation(StringBuilder commandStringBuilder, ModificationCommand command, Int32 commandPosition)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.UpdateCachedCommandText(Int32 commandPosition)
   at ClickHouse.EntityFrameworkCore.Update.Internal.ClickHouseModificationCommandBatch.GetCommandText()
   at ClickHouse.EntityFrameworkCore.Update.Internal.ClickHouseModificationCommandBatch.CreateStoreCommand()
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(DbContext _, Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.Storage.NonRetryingExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
   at bytt.clickhouse.demo.Program.Main(String[] args) in D:\code\bytt\bytt.clickhouse.demo\Program.cs:line 27

any idea ? very thanks!

A very long value in a string. Invalid URI: The Uri string is too long.

class MyTable
{
       public string Txt => "a very very long value .........";
}

var item = new MyTable();

var _dbSet = _dbContext.Set<MyTable>();
await _dbSet.AddAsync(item);
await _dbContext.SaveChangesAsync();

when I use EntityFramework, and I try to insert an object into the table that has a string type property and this property has a very long value, then I get this error:

exception.InnerException.Message => Invalid URI: The Uri string is too long.

exception.InnerException.StackTrace =>

at System.Uri.CreateThis(String uri, Boolean dontEscape, UriKind uriKind, UriCreationOptions& creationOptions)
at System.Uri..ctor(String uriString, UriKind uriKind)
at System.Net.Http.HttpRequestMessage..ctor(HttpMethod method, String requestUri)
at ClickHouse.Client.ADO.ClickHouseCommand.d__54.MoveNext()
at ClickHouse.Client.ADO.ClickHouseCommand.d__53.MoveNext()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.d__19.MoveNext()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.d__19.MoveNext()
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.d__50.MoveNext()

but, if you use this method, then everything works out successfully:

using var bulkCopy = new ClickHouseBulkCopy(connection)
{
      DestinationTableName = "mytable",
      BatchSize = 1
};

var list = (new List<object[]>() { new object[] { item.Txt } });
await bulkCopy.WriteToServerAsync(list);

as I understand it, this method generates the following query:

INSERT INTO {DestinationTableName} ({string.Join(", ", columnNames)}) FORMAT RowBinary;

is there a way to execute a query using ORM?

remove the entity is failed

hello , i am trying to remove the entity but it is failed, here is an example

using ClickHouse.EntityFrameworkCore.Extensions;
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
using System.Linq.Expressions;

namespace chdemo
{
    public class Order
    {
        public long Id { get; set; }

        public string Name { get; set; }

        public float Price { get; set; }
    }

    class MyConext : DbContext
    {
        public DbSet<Order> Orders { get; set; }

        private readonly string connectionString;

        public MyConext(string connectionString)
        {
            this.connectionString = connectionString;
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Order>().HasKey(e => e.Id);
            
            modelBuilder.Entity<Order>()
                .HasMergeTreeEngine(new Expression<Func<Order, object>>[]
                {
                    e => e.Id
                });

        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {            
            optionsBuilder.UseClickHouse(this.connectionString);
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var cn = "Host=192.168.1.100;Port=8123;Database=test2;User=default;Password=by123456";
            using var ctx = new MyConext(cn);

            ctx.Database.EnsureCreated();

            Order order1 = new Order() { Id = 1, Name = "test1", Price = 0.123F },
                order2 = new Order() { Id = 2, Name = "bbb", Price = 100 };

            ctx.Orders.AddRange(order1, order2);

            ctx.SaveChanges();    //it is ok for now, we can insert two instances into clickhouse.

            var orders = ctx.Orders.AsEnumerable().Where(f => f.Price > 0)
                .ToArray();
            foreach (var order in orders)   //we can retrive all orders
                Console.WriteLine($"order:{order.Id},{order.Name},{order.Price}");

            var first = orders.FirstOrDefault();
            ctx.Orders.Remove(first);           //but , the remove comes failed
            var r = ctx.SaveChanges();
            Console.WriteLine($"delete one order:{r}");

            Console.ReadKey();
        }
    }
}
Microsoft.EntityFrameworkCore.DbUpdateException:“An error occurred while updating the entries. 
See the inner exception for details.”

inner exception:

ClickHouseServerException: Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 1 ('DELETE') (line 1, col 1): DELETE FROM "Orders"
WHERE "Id" = @p0;
. Expected one of: ALTER query, Query with output, ALTER PROFILE, RENAME DATABASE, SHOW PRIVILEGES query, TRUNCATE, KILL, KILL QUERY query, SELECT query, possibly with UNION, list of union elements, ALTER ROLE, SELECT subquery, DESCRIBE query, SELECT query, subquery, possibly with UNION, SHOW GRANTS, SHOW CREATE, WATCH, CREATE SETTINGS PROFILE or ALTER SETTINGS PROFILE query, SHOW PROCESSLIST query, ALTER POLICY, ALTER USER, CREATE VIEW query, CHECK TABLE, SET ROLE, SELECT query, SELECT, REVOKE, CREATE USER, CREATE DICTIONARY, CREATE PROFILE, SET ROLE DEFAULT, EXPLAIN, ALTER SETTINGS PROFILE, SYSTEM, ALTER LIVE VIEW, RENAME TABLE, DROP query, SHOW ACCESS, OPTIMIZE query, USE, DROP access entity query, RENAME DICTIONARY, DETACH, SET, SHOW, DESC, OPTIMIZE TABLE, CREATE ROW POLICY, SET DEFAULT ROLE, CREATE POLICY, ALTER ROW POLICY, INSERT INTO, INSERT query, SHOW [TEMPORARY] TABLES|DATABASES|CLUSTERS|CLUSTER 'name' [[NOT] [I]LIKE 'str'] [LIMIT expr], GRANT, RENAME query, SHOW GRANTS query, SHOW PRIVILEGES, EXISTS, DROP, SYSTEM query, CREATE LIVE VIEW query, CREATE ROW POLICY or ALTER ROW POLICY query, CREATE QUOTA or ALTER QUOTA query, SHOW PROCESSLIST, ALTER QUOTA, CREATE QUOTA, CREATE DATABASE query, SET query, Query, CREATE, WITH, CREATE ROLE or ALTER ROLE query, EXTERNAL DDL FROM, EXCHANGE TABLES, EXISTS or SHOW CREATE query, WATCH query, REPLACE, CREATE ROLE, CREATE SETTINGS PROFILE, SET ROLE or SET DEFAULT ROLE query, CREATE USER or ALTER USER query, EXTERNAL DDL query, SHOW ACCESS query, SHOW CREATE QUOTA query, USE query, ATTACH, DESCRIBE, ALTER TABLE, ShowAccessEntitiesQuery, GRANT or REVOKE query, CREATE TABLE or ATTACH TABLE query (version 21.4.6.55 (official build))

The instance of entity type 'AnswerSheet' cannot be tracked because another instance with the same key value for {'SheetId'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see the conflicting key values.

halo ,author ,Update Data report an error:The instance of entity type 'AnswerSheet' cannot be tracked because another instance with the same key value for {'SheetId'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see the conflicting key values.

Scaffold have problems

When i do scaffold from manager-console i have an error:
Unable to find expected assembly attribute [DesignTimeProviderServices] in provider assembly 'EntityFrameworkCore.ClickHouse'. This attribute is required to identify the class which acts as the design-time service provider factory for the provider.
Maybe i do something wrong? Could u add some "GetStart" or any basic documentation for usage? Thank you for attention.

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.