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

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

About this issue

Original URL
State: closed
Created 4 years ago
Comments: 17 (17 by maintainers)

Most upvoted comments

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 on Sep 17, 2020

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

mikependon on Sep 17, 2020

@mikependon

Playground.zip

Instructions:

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

Swoorup on Sep 17, 2020

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

Swoorup on Sep 16, 2020

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.

mikependon on Sep 16, 2020