denis-ivanov / entityframeworkcore.clickhouse Goto Github PK
View Code? Open in Web Editor NEWClickHouse provider for Entity Framework Core.
Home Page: https://clickhouse.tech/
ClickHouse provider for Entity Framework Core.
Home Page: https://clickhouse.tech/
not support Math.Round(a.Sum(),2)
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.ConcurrentDictionary
2.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)
{
}
See docs!
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'.
See docs!
like this throw Can't translated exception
var a = "abc"
_dbContext.Users.Where(x => x.Name == a)
but this is correct
_dbContext.Users.Where(x => x.Name == "abc")
and nullable type has same issue
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.
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!
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.
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?
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))
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))
Is it possible to support MaterializedPostgreSQL?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.