Repodb: Question: OrderBy not working, sqlite? (F#)

[Swoorup]

[<CLIMutable>]
type CommandEntity =
  { Id: Nullable<int>
    AggregateId: Guid
    CommandId: Guid
    CausationId: Guid
    CorrelationId: Guid
    CommandPayload: string
    PayloadType: string
    QueueName: string
    ExpectedVersion: Nullable<int64>
    ProcessId: obj
    ``Timestamp Utc``: Nullable<DateTime> }

SqLiteBootstrap.Initialize()
RepoDb.Converter.ConversionType <- ConversionType.Automatic

FluentMapper.Entity<CommandEntity>().Table("CommandEntity")
|> ignore

let getAll () =
    let orderBy = [| OrderField("ExpectedVersion", Order.Descending) |]

    let commands = ctx.Query<CommandEntity>(fun e -> e.Id = Nullable(19), orderBy=orderBy )

yields the following:

Unhandled exception. System.InvalidOperationException: No mapping exists from object type Db+Fn+commands@80 to a known managed provider native type.
   at Microsoft.Data.Sqlite.SqliteValueBinder.Bind()
   at Microsoft.Data.Sqlite.SqliteParameter.Bind(sqlite3_stmt stmt)
   at Microsoft.Data.Sqlite.SqliteParameterCollection.Bind(sqlite3_stmt stmt)
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at RepoDb.DbConnectionExtension.ExecuteQueryInternal[TEntity](IDbConnection connection, String commandText, Object param, Nullable`1 commandType, Nullable`1 commandTimeout, IDbTransaction transaction, Boolean skipCommandArrayParametersCheck)
   at RepoDb.DbConnectionExtension.QueryInternalBase[TEntity](IDbConnection connection, IEnumerable`1 fields, QueryGroup where, IEnumerable`1 orderBy, Nullable`1 top, String hints, String cacheKey, Int32 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.QueryInternal[TEntity](IDbConnection connection, QueryGroup where, IEnumerable`1 orderBy, Nullable`1 top, String hints, String cacheKey, Int32 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.Query[TEntity](IDbConnection connection, QueryGroup where, IEnumerable`1 orderBy, Nullable`1 top, String hints, String cacheKey, Int32 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.Query[TEntity](IDbConnection connection, Object whereOrPrimaryKey, IEnumerable`1 orderBy, Nullable`1 top, String hints, String cacheKey, Int32 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, ITrace trace, IStatementBuilder statementBuilder)
   at Db.Fn.getAll() in /Users/swoorup.joshi/RiderProjects/Playground/Playground/Db.fs:line 79

RepoDb (1.11.6)
RepoDb.SqLite (1.0.16)

[Swoorup]

The table is defined as

CREATE TABLE IF NOT EXISTS [CommandEntity](
  Id INTEGER PRIMARY KEY AUTOINCREMENT,
  AggregateId varchar(500) NOT NULL,
  CommandId varchar(500) NOT NULL,
  CausationId varchar(500) NOT NULL,
  CorrelationId VARCHAR(500) not NULL,
  CommandPayload TEXT,
  PayloadType TEXT,
  QueueName TEXT,
  ExpectedVersion INTEGER,
  ProcessId VARCHAR(500),
  "Timestamp Utc" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

[mikependon]

It is supported, here is the builder. Though, there is no integration test pointed specifically for OrderBy in the SqLite solutions, but it is fully covered by the Core.

Give me time to investigate this.

[Swoorup]

Same issue with postgres:

nhandled exception. System.NotSupportedException: The CLR type Db+Fn+commands@71 isn't natively supported by Npgsql or your PostgreSQL. To use it with a PostgreSQL composite you need to specify DataTypeName or to map it, please refer to the documentation.
   at Npgsql.TypeMapping.ConnectorTypeMapper.GetByClrType(Type type)
   at Npgsql.NpgsqlParameter.ResolveHandler(ConnectorTypeMapper typeMapper)
   at Npgsql.NpgsqlCommand.ValidateParameters(ConnectorTypeMapper typeMapper)
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at RepoDb.DbConnectionExtension.ExecuteQueryInternal[TResult](IDbConnection connection, String commandText, Object param, Nullable`1 commandType, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, Boolean skipCommandArrayParametersCheck)
   at RepoDb.DbConnectionExtension.QueryInternalBase[TEntity](IDbConnection connection, String tableName, QueryGroup where, IEnumerable`1 fields, IEnumerable`1 orderBy, Nullable`1 top, String hints, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.QueryInternal[TEntity](IDbConnection connection, String tableName, QueryGroup where, IEnumerable`1 fields, IEnumerable`1 orderBy, Nullable`1 top, String hints, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.Query[TEntity](IDbConnection connection, Object what, IEnumerable`1 fields, IEnumerable`1 orderBy, Nullable`1 top, String hints, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, ITrace trace, IStatementBuilder statementBuilder)
   at Db.Fn.getAll() in /Users/swoorup.joshi/RiderProjects/Playground/Playground/Db.fs:line 70
   at Db.Fn.writeEvents() in /Users/swoorup.joshi/RiderProjects/Playground/Playground/Db.fs:line 97

[mikependon]

I cannot replicate this problem as it is working on my side to both DB Providers. I used the existing Integration Tests for testing. Your issue is seems to be referring to the Db.Fn.commands object. Has that ring the bell on your side?

[Swoorup]

This might be F# specific its erroring on

let commands = ctx.Query<CommandEntity>(fun e -> e.Id = Nullable(19), orderBy=orderBy )

I also tried the following with the same issue.

let commands = ctx.Query<commandentity>(fun e -> e.id = Nullable(19L), orderBy=OrderField.Parse ({| expectedversion = Order.Descending |}))

I'll try to create a small repo of the issue.

[mikependon]

Yes please, that would be a good idea. Can you create a very small application in F# that replicates this?

[Swoorup]

@mikependon

Playground.zip

Instructions:

1. dotnet tool restore
2. dotnet paket install
3. dotnet migrondi up // test database must exists
4. dotnet watch run inside the playground project.

[mikependon]

Can you give me your CREATE TABLE script?

[Swoorup]

Forgot to mention the 3rd command must also be run inside playground project folder. Migrations are in /playground/playgroun/migrations

In any case

-- ---------- MIGRONDI:UP:1600272935798 --------------
-- Write your Up migrations here

CREATE TABLE IF NOT EXISTS CommandEntity(
  -- Id bigserial primary key,
  id int GENERATED ALWAYS AS IDENTITY,
  eventid UUID ,
  aggregateid varchar(500) NOT NULL,
  commandid varchar(500) NOT NULL,
  causationid varchar(500) NOT NULL,
  correlationid VARCHAR(500) not NULL,
  commandpayload TEXT,
  payloadtype TEXT,
  queuename TEXT,
  expectedversion INTEGER,
  processid VARCHAR(500),
  "Timestamp Utc" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

[mikependon]

It is actually working in QueryAll but not Query. I will look at this one then.

[mikependon]

First, as per my investigation, the issue is not because of OrderBy, it is because of the fun keyword. To support my claims, see the code below.

Interesting part is, the code with fun keyword is inferred on this method.

public static IEnumerable<TEntity> Query<TEntity>(this IDbConnection connection,
    object what,
    IEnumerable<Field> fields = null,
    IEnumerable<OrderField> orderBy = null,
    int? top = 0,
    string hints = null,
    string cacheKey = null,
    int? cacheItemExpiration = Constant.DefaultCacheItemExpirationInMinutes,
    int? commandTimeout = null,
    IDbTransaction transaction = null,
    ICache cache = null,
    ITrace trace = null,
    IStatementBuilder statementBuilder = null)
    where TEntity : class
{
    return QueryInternal<TEntity>(connection: connection,
        tableName: ClassMappedNameCache.Get<TEntity>(),
        where: WhatToQueryGroup<TEntity>(connection, what, transaction),
        fields: fields,
        orderBy: orderBy,
        top: top,
        hints: hints,
        cacheKey: cacheKey,
        cacheItemExpiration: cacheItemExpiration,
        commandTimeout: commandTimeout,
        transaction: transaction,
        cache: cache,
        trace: trace,
        statementBuilder: statementBuilder);
}

And not on this method.

public static IEnumerable<TEntity> Query<TEntity>(this IDbConnection connection,
    Expression<Func<TEntity, bool>> where, // <-- It should be here
    IEnumerable<Field> fields = null,
    IEnumerable<OrderField> orderBy = null,
    int? top = 0,
    string hints = null,
    string cacheKey = null,
    int? cacheItemExpiration = Constant.DefaultCacheItemExpirationInMinutes,
    int? commandTimeout = null,
    IDbTransaction transaction = null,
    ICache cache = null,
    ITrace trace = null,
    IStatementBuilder statementBuilder = null) where TEntity : class
{
    return QueryInternal<TEntity>(connection: connection,
        tableName: ClassMappedNameCache.Get<TEntity>(),
        where: ToQueryGroup(where),
        fields: fields,
        orderBy: orderBy,
        top: top,
        hints: hints,
        cacheKey: cacheKey,
        cacheItemExpiration: cacheItemExpiration,
        commandTimeout: commandTimeout,
        transaction: transaction,
        cache: cache,
        trace: trace,
        statementBuilder: statementBuilder);
}

And the reason to that is really the type is not equals.

The type that has inferred to what is the argument you passed in the orderBy argument, which is a bit strange. There is something wrong on this functional call.

[Swoorup]

Wow it appears to work, if you braces around fun it works.
Very subtle.

let commands = ctx.Query<commandentity>((fun e -> e.id = Nullable(19L)), orderBy=OrderField.Parse ({| expectedversion = Order.Descending |}))

[Swoorup]

So the where clauses is returning a tuple of bool and order. 😆
I wonder if it is possible to add type safety

[mikependon]

I am about to ask this to the experts of F#. Atleast, it is not inferred properly on this desired overloaded method.

But thanks that you fixed it.

[mikependon]

So the where clauses is returning a tuple of element and order.

There is proper overloaded method that is type-safety, and if in any case that your call cannot be inferred, it goes to a default object what or object where.

[Swoorup]

I found it can be bit tricky. Maybe something to add in the docs.

Specifying type for the element invokes the Expression version.

If forgetting to specify the type, you would get default to object, which fails with similar message.

[mikependon]

I think that is a programming language barrier then. At-least in C#, you do need to specify the type when defining an expression, but is seems required if you're doing it in F#.

CSharp:

connection.Query<EventEntity>(e => ids.Contains(e.Id));

FSharp

connection.Query<EventEntity>((fun e : EventEntity) => ids.Contains(e.Id));

Anyway, in my side, it is inferred properly.

It is also important to take note the the way Func<Expression<TEntity, bool>> has been implemented is being filtered by of type class (where T : class). See the docs here. This could be the reason why it forces you to do it like that in F#.