`Feature Request`: Add Primary Key Support

[SignalMQ]

Hello ClickHouse Team. I want to give thanks for support EntityFrameworkCore!

I encountered a situation where I called DbContext.Database.EnsureCreatedAsync(cancellationToken) and received tables without primary key specifications in the SQL script:

And primary key is defined with HasKey in model description:

[alex-clickhouse]

Hi,

Our docs could be a bit clearer on this point, but .HasKey() is translated into the ORDER BY clause in the DDL. In ClickHouse the ordering key will also be the primary key unless a separate primary key is specified, so this is actually working as you would expect.

If you want to specify a different primary key, you can use .WithOrderBy(dimensions...).WithPrimaryKey(prefix...), but do note that the primary key must be a prefix of the ordering key.

Some relevant docs: https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree#choosing-a-primary-key-that-differs-from-the-sorting-key

[SignalMQ]

@alex-clickhouse thanks for the documentation!

Taking this opportunity I wanted to clarify one more thing:
I ran into limitations with the number of parameters passed (>10 000) in SELECT ... WHERE IN(... queries:

fail: Microsoft.EntityFrameworkCore.Query[10100]
 An exception occurred while iterating over the results of a query for context type 'ClickHouseSync.Contexts.ClickHouse.ClickHouseContext'.
 ClickHouse.Driver.ClickHouseServerException (0x000003E8): Poco::Exception. Code: 1000, e.code() = 0, HTML Form Exception: Too many form fields

 at ClickHouse.Driver.ClickHouseClient.HandleError(HttpResponseMessage response, String query, Activity activity)
 at ClickHouse.Driver.ClickHouseClient.PostSqlQueryAsync(String sql, ClickHouseParameterCollection parameters, QueryOptions options, CancellationToken cancellationToken)
 at ClickHouse.Driver.ADO.ClickHouseCommand.PostSqlQueryAsync(String sqlQuery, CancellationToken token)
 at ClickHouse.Driver.ADO.ClickHouseCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
 at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
 at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
 at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
 at ClickHouse.EntityFrameworkCore.Storage.Internal.ClickHouseExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
 at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
 ClickHouse.Driver.ClickHouseServerException (0x000003E8): Poco::Exception. Code: 1000, e.code() = 0, HTML Form Exception: Too many form fields

 at ClickHouse.Driver.ClickHouseClient.HandleError(HttpResponseMessage response, String query, Activity activity)
 at ClickHouse.Driver.ClickHouseClient.PostSqlQueryAsync(String sql, ClickHouseParameterCollection parameters, QueryOptions options, CancellationToken cancellationToken)
 at ClickHouse.Driver.ADO.ClickHouseCommand.PostSqlQueryAsync(String sqlQuery, CancellationToken token)
 at ClickHouse.Driver.ADO.ClickHouseCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
 at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
 at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
 at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
 at ClickHouse.EntityFrameworkCore.Storage.Internal.ClickHouseExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
 at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()

and to get around this I had to use a different approach:

builder.Services.AddDbContext<ClickHouseContext>(options =>
{
 options.UseClickHouse(builder.Configuration.GetConnectionString("ClickHouse"), clickHouseOptions =>
 {
 clickHouseOptions.UseParameterizedCollectionMode(ParameterTranslationMode.Constant); // HERE
 });
});

Is it possible to make this behavior the default in the library?

[alex-clickhouse]

I think the better approach there would be to use a single array parameter for all the elements, opened an issue for it.

[SignalMQ]

Thank you! Now close this issue