Repodb: Bug: Unable to share POCOs between different database providers (Sqlite and Postgres)

[Swoorup]

When using specific attributes for example:

  [<CLIMutable>]
  type EventWriteEntity =
    { id: Guid
      position: Nullable<int64>
      correlation_id: Guid
      causation_id: Guid
      stream_key: string
      version: int
      name: string
      [<NpgsqlTypeMap(NpgsqlDbType.Json)>]
      payload: string
      [<NpgsqlTypeMap(NpgsqlDbType.Json)>]
      metadata: string
      schema_revision: int }

In tests scenario, it might be ideal to use sqlite.
However upon inserting it throws an exception:

  Error Message:
   Unable to cast object of type 'Microsoft.Data.Sqlite.SqliteParameter' to type 'Npgsql.NpgsqlParameter'.
  Stack Trace:
     at lambda_method(Closure , DbCommand , IList`1 )
   at RepoDb.DbConnectionExtension.InsertAllInternalBase[TEntity](IDbConnection connection, String tableName, IEnumerable`1 entities, Int32 batchSize, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.InsertAllInternal[TEntity](IDbConnection connection, String tableName, IEnumerable`1 entities, Int32 batchSize, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.InsertAll[TEntity](IDbConnection connection, IEnumerable`1 entities, Int32 batchSize, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)

Haven't tested with all operations.

[Swoorup]

• InsertAll - Throws
• Query - Works
• ExecuteQuery - Works

I can't seem to test others without running into type inference problems with F#.

[mikependon]

This is a pre-discussed problem and it is intentionally not a part of the recent release v1.12.0. In addition, please also create a separate user story in relation to your use-case of using the Primary and Identity as separate columns on the same model.

[Swoorup]

Did a exhaustive testing. Here are the results, when the postgres attribute is used for sqlite.

// None of the push operation works
Operation: Insert
Operation: InsertAll
Operation: Merge
Operation: MergeAll
Operation: Update
Operation: UpdateAll

// Works
Operation: ExecuteQuery
Operation: Query
Operation: QueryAll
Operation: BatchQuery
Operation: QueryMultiple
Operation: ExecuteQueryMultiple

More Detailed Exception log

Operation: Insert
Exception: Unable to cast object of type 'Microsoft.Data.Sqlite.SqliteParameter' to type 'Npgsql.NpgsqlParameter'. 
   at lambda_method(Closure , DbCommand , EventWriteEntityWithNpgsqlAttribute )
   at RepoDb.DbConnectionExtension.InsertInternalBase[TEntity,TResult](IDbConnection connection, String tableName, TEntity entity, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.InsertInternal[TEntity,TResult](IDbConnection connection, String tableName, TEntity entity, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.Insert[TEntity](IDbConnection connection, TEntity entity, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDbTests.Program.<>c__DisplayClass0_0.<Main>b__1() in /Users/swoorup.joshi/RiderProjects/RepoDbTest/Program.cs:line 167
   at RepoDbTests.Helpers.TryExecute[T](String operationName, Func`1 m) in /Users/swoorup.joshi/RiderProjects/RepoDbTest/Program.cs:line 84


Operation: InsertAll
Exception: Unable to cast object of type 'Microsoft.Data.Sqlite.SqliteParameter' to type 'Npgsql.NpgsqlParameter'. 
   at lambda_method(Closure , DbCommand , EventWriteEntityWithNpgsqlAttribute )
   at RepoDb.DbConnectionExtension.InsertAllInternalBase[TEntity](IDbConnection connection, String tableName, IEnumerable`1 entities, Int32 batchSize, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.InsertAllInternal[TEntity](IDbConnection connection, String tableName, IEnumerable`1 entities, Int32 batchSize, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.InsertAll[TEntity](IDbConnection connection, IEnumerable`1 entities, Int32 batchSize, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDbTests.Program.<>c__DisplayClass0_0.<Main>b__2() in /Users/swoorup.joshi/RiderProjects/RepoDbTest/Program.cs:line 175
   at RepoDbTests.Helpers.TryExecute[T](String operationName, Func`1 m) in /Users/swoorup.joshi/RiderProjects/RepoDbTest/Program.cs:line 84


Operation: Merge
Exception: Unable to cast object of type 'Microsoft.Data.Sqlite.SqliteParameter' to type 'Npgsql.NpgsqlParameter'. 
   at lambda_method(Closure , DbCommand , EventWriteEntityWithNpgsqlAttribute )
   at RepoDb.DbConnectionExtension.UpdateInternalBase[TEntity](IDbConnection connection, String tableName, TEntity entity, QueryGroup where, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.UpdateInternal[TEntity](IDbConnection connection, String tableName, TEntity entity, QueryGroup where, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.Update[TEntity](IDbConnection connection, String tableName, TEntity entity, QueryGroup where, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.UpsertInternalBase[TEntity,TResult](IDbConnection connection, String tableName, TEntity entity, IEnumerable`1 qualifiers, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.MergeInternal[TEntity,TResult](IDbConnection connection, String tableName, TEntity entity, IEnumerable`1 qualifiers, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.Merge[TEntity](IDbConnection connection, TEntity entity, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDbTests.Program.<>c__DisplayClass0_0.<Main>b__3() in /Users/swoorup.joshi/RiderProjects/RepoDbTest/Program.cs:line 186
   at RepoDbTests.Helpers.TryExecute[T](String operationName, Func`1 m) in /Users/swoorup.joshi/RiderProjects/RepoDbTest/Program.cs:line 84


Operation: MergeAll
Exception: Unable to cast object of type 'Microsoft.Data.Sqlite.SqliteParameter' to type 'Npgsql.NpgsqlParameter'. 
   at lambda_method(Closure , DbCommand , EventWriteEntityWithNpgsqlAttribute )
   at RepoDb.DbConnectionExtension.UpdateInternalBase[TEntity](IDbConnection connection, String tableName, TEntity entity, QueryGroup where, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.UpdateInternal[TEntity](IDbConnection connection, String tableName, TEntity entity, QueryGroup where, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.Update[TEntity](IDbConnection connection, String tableName, TEntity entity, QueryGroup where, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.UpsertInternalBase[TEntity,TResult](IDbConnection connection, String tableName, TEntity entity, IEnumerable`1 qualifiers, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.UpsertAllInternalBase[TEntity](IDbConnection connection, String tableName, IEnumerable`1 entities, IEnumerable`1 qualifiers, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.MergeAllInternal[TEntity](IDbConnection connection, String tableName, IEnumerable`1 entities, IEnumerable`1 qualifiers, Int32 batchSize, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.MergeAll[TEntity](IDbConnection connection, IEnumerable`1 entities, Int32 batchSize, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDbTests.Program.<>c__DisplayClass0_0.<Main>b__4() in /Users/swoorup.joshi/RiderProjects/RepoDbTest/Program.cs:line 193
   at RepoDbTests.Helpers.TryExecute[T](String operationName, Func`1 m) in /Users/swoorup.joshi/RiderProjects/RepoDbTest/Program.cs:line 84


Operation: Update
Exception: Unable to cast object of type 'Microsoft.Data.Sqlite.SqliteParameter' to type 'Npgsql.NpgsqlParameter'. 
   at lambda_method(Closure , DbCommand , EventWriteEntityWithNpgsqlAttribute )
   at RepoDb.DbConnectionExtension.UpdateInternalBase[TEntity](IDbConnection connection, String tableName, TEntity entity, QueryGroup where, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.UpdateInternal[TEntity](IDbConnection connection, String tableName, TEntity entity, QueryGroup where, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.Update[TEntity](IDbConnection connection, TEntity entity, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDbTests.Program.<>c__DisplayClass0_0.<Main>b__5() in /Users/swoorup.joshi/RiderProjects/RepoDbTest/Program.cs:line 203
   at RepoDbTests.Helpers.TryExecute[T](String operationName, Func`1 m) in /Users/swoorup.joshi/RiderProjects/RepoDbTest/Program.cs:line 84


Operation: UpdateAll
Exception: Unable to cast object of type 'Microsoft.Data.Sqlite.SqliteParameter' to type 'Npgsql.NpgsqlParameter'. 
   at lambda_method(Closure , DbCommand , EventWriteEntityWithNpgsqlAttribute )
   at RepoDb.DbConnectionExtension.UpdateAllInternalBase[TEntity](IDbConnection connection, String tableName, IEnumerable`1 entities, IEnumerable`1 qualifiers, Int32 batchSize, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.UpdateAllInternal[TEntity](IDbConnection connection, String tableName, IEnumerable`1 entities, IEnumerable`1 qualifiers, Int32 batchSize, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDb.DbConnectionExtension.UpdateAll[TEntity](IDbConnection connection, IEnumerable`1 entities, Int32 batchSize, IEnumerable`1 fields, String hints, Nullable`1 commandTimeout, IDbTransaction transaction, ITrace trace, IStatementBuilder statementBuilder)
   at RepoDbTests.Program.<>c__DisplayClass0_0.<Main>b__6() in /Users/swoorup.joshi/RiderProjects/RepoDbTest/Program.cs:line 212
   at RepoDbTests.Helpers.TryExecute[T](String operationName, Func`1 m) in /Users/swoorup.joshi/RiderProjects/RepoDbTest/Program.cs:line 84


Operation: ExecuteQuery
Result: 
[
  {
    "id": "97130f58-3e7c-49b7-8006-03544031d436",
    "position": 1,
    "name": "Record1",
    "payload": "{\"name\": \"swoorup\" }",
    "metadata": null
  },
  {
    "id": "6ac0e73b-8a07-4450-8d3e-55e4cac5fab2",
    "position": 2,
    "name": "Record2",
    "payload": "{\"name\": \"michael\" }",
    "metadata": null
  }
]


Operation: Query
Result: 
[
  {
    "id": "c850268c-464e-409d-8e09-71301bfa9d34",
    "position": 1,
    "name": "Record1",
    "payload": "{\"name\": \"swoorup\" }",
    "metadata": null
  }
]


Operation: QueryAll
Result: 
[
  {
    "id": "c8173e09-a73a-4cdd-b86c-82e87e5fa150",
    "position": 1,
    "name": "Record1",
    "payload": "{\"name\": \"swoorup\" }",
    "metadata": null
  },
  {
    "id": "997626f5-d6c7-43ec-bf0b-1efe3e071799",
    "position": 2,
    "name": "Record2",
    "payload": "{\"name\": \"michael\" }",
    "metadata": null
  }
]


Operation: BatchQuery
Result: 
[
  {
    "id": "eb59ec92-cb14-4689-85d6-0ff7a9c5c23c",
    "position": 2,
    "name": "Record2",
    "payload": "{\"name\": \"michael\" }",
    "metadata": null
  }
]


Operation: QueryMultiple
Result: 
{
  "Item1": [
    {
      "id": "7db1ea38-4d85-418f-91c5-eb08c598b2c9",
      "position": 1,
      "name": "Record1",
      "payload": "{\"name\": \"swoorup\" }",
      "metadata": null
    }
  ],
  "Item2": [
    {
      "id": "a"
    }
  ]
}


Operation: ExecuteQueryMultiple
Result: 
{
  "Item1": [
    {
      "id": "4821febb-5442-49d8-b98f-9a57d8c6d3dd",
      "position": 1,
      "name": "Record1",
      "payload": "{\"name\": \"swoorup\" }",
      "metadata": null
    },
    {
      "id": "37d1e085-9555-439c-ae55-39e2b1a415aa",
      "position": 2,
      "name": "Record2",
      "payload": "{\"name\": \"michael\" }",
      "metadata": null
    }
  ],
  "Item2": [
    {
      "id": "a"
    }
  ]
}

[Swoorup]

Script used to test:

Packages: Newtonsoft.Json, RepoDb, RepoDb.PostgreSQL, RepoDb.SqLite

Program.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using Microsoft.Data.Sqlite;
using Newtonsoft.Json;
using RepoDb;
using RepoDb.Enumerations;
using NpgsqlTypes;
using RepoDb.Attributes;

namespace RepoDbTests
{
    #region EntityModels

    public class EventReadEntity
    {
        public Guid id { get; set; }
        public Int64 position { get; set; }
        public string name { get; set; }
        public string payload { get; set; }
        public string? metadata { get; set; }
    }

    public class EventReadEntityWithNpgsqlAttribute
    {
        public Guid id { get; set; }
        public Int64 position { get; set; }
        public string name { get; set; }
        [NpgsqlTypeMap(NpgsqlDbType.Jsonb)] public string payload { get; set; }
        [NpgsqlTypeMap(NpgsqlDbType.Jsonb)] public string? metadata { get; set; }
    }

    public class EventWriteEntityWithNpgsqlAttribute
    {
        public Guid id { get; set; }
        public Int64? position { get; set; }
        public string name { get; set; }
        [NpgsqlTypeMap(NpgsqlDbType.Jsonb)] public string payload { get; set; }
        [NpgsqlTypeMap(NpgsqlDbType.Jsonb)] public string metadata { get; set; }
    }

    public class EventWriteEntity
    {
        public Guid id { get; set; }
        public Int64? position { get; set; }
        public string name { get; set; }
        public string payload { get; set; }
        public string? metadata { get; set; }
    }

    public class Table2
    {
        public string id { get; set; }
    }

    #endregion

    public static class Helpers
    {
        public static string PrettyPrint<T>(this T obb) => JsonConvert.SerializeObject(obb, Formatting.Indented);
        public static string CreateDummyJson(this string v) => $"{{\"name\": \"{v}\"}}";

        private const string CreateTablesSql = @"
            create table if not exists [es_events]
            (
              id              text                        not null,
              position        integer primary key AUTOINCREMENT,
              name            varchar(40)                 not null,
              payload         text                       not null,
              metadata        text
            );

            create table if not exists [another]
            (
              id              text                        not null
            );";

        public static void TryExecute<T>(string operationName, Func<T> m)
        {
            Console.WriteLine($"Operation: {operationName}", Color.Yellow);
            try
            {
                var result = m().PrettyPrint();
                Console.WriteLine($"Result: ", Color.Green);
                Console.WriteLine($"{result}");
            }
            catch (Exception e)
            {
                Console.WriteLine($"Exception: {e.Message} \n{e.StackTrace}", Color.Red);
            }

            Console.WriteLine();
            Console.WriteLine();
        }

        public static IDbConnection CreateTablesWithDummyData()
        {
            try
            {
                SqLiteBootstrap.Initialize();
                FluentMapper
                    .Type<DateTime>()
                    .DbType(DbType.DateTime2);
                FluentMapper
                    .Entity<EventWriteEntity>()
                    .Table("es_events");
                FluentMapper
                    .Entity<EventReadEntity>()
                    .Table("es_events");
                FluentMapper
                    .Entity<EventWriteEntityWithNpgsqlAttribute>()
                    .Table("es_events");
                FluentMapper
                    .Entity<EventReadEntityWithNpgsqlAttribute>()
                    .Table("es_events");
                FluentMapper
                    .Entity<Table2>()
                    .Table("another");

                Converter.ConversionType = ConversionType.Automatic;
            }
            catch (Exception _)
            {
                // ignored
            }

            // var connString = $"DataSource=file:memdb1?mode=memory&cache=shared";
            var conn = new SqliteConnection("Filename=:memory:");
            conn.ExecuteQuery(CreateTablesSql);

            // insert some data

            conn.InsertAll(new List<EventWriteEntity>()
            {
                new EventWriteEntity()
                {
                    id = Guid.NewGuid(),
                    name = "Record1",
                    metadata = null,
                    payload = "{\"name\": \"swoorup\" }"
                },
                new EventWriteEntity()
                {
                    id = Guid.NewGuid(),
                    position = null,
                    name = "Record2",
                    metadata = null,
                    payload = "{\"name\": \"michael\" }"
                }
            });
            conn.InsertAll(new List<Table2>
            {
                new Table2 {id = "a"}
            });

            return conn;
        }
    }

    static class Program
    {
        static void Main(string[] _)
        {
            var connBuilder = new Func<IDbConnection>(() => Helpers.CreateTablesWithDummyData());
            // Test push function
            Helpers.TryExecute("Insert", () => connBuilder().Insert(new EventWriteEntityWithNpgsqlAttribute
            {
                id = Guid.NewGuid(),
                position = null,
                name = "Record3",
                metadata = null,
                payload = "bla".CreateDummyJson(),
            }));
            Helpers.TryExecute("InsertAll", () => connBuilder().InsertAll(new[]
            {
                new EventWriteEntityWithNpgsqlAttribute
                {
                    id = Guid.NewGuid(),
                    position = null,
                    name = "Record3",
                    metadata = null,
                    payload = "bla".CreateDummyJson(),
                }
            }));
            Helpers.TryExecute("Merge", () => connBuilder().Merge(new EventWriteEntityWithNpgsqlAttribute()
            {
                id = Guid.NewGuid(),
                position = 1,
                name = "Record1",
                payload = "JOSH".CreateDummyJson()
            }));
            Helpers.TryExecute("MergeAll", () => connBuilder().MergeAll(new[]
            {
                new EventWriteEntityWithNpgsqlAttribute()
                {
                    id = Guid.NewGuid(),
                    position = 1,
                    name = "Record1",
                    payload = "JOSH".CreateDummyJson()
                }
            }));
            Helpers.TryExecute("Update", () => connBuilder().Update(
                new EventWriteEntityWithNpgsqlAttribute()
                {
                    id = Guid.NewGuid(),
                    position = 1,
                    name = "Record1",
                    payload = "JOSH".CreateDummyJson()
                }
            ));
            Helpers.TryExecute("UpdateAll", () => connBuilder().UpdateAll(new[]
            {
                new EventWriteEntityWithNpgsqlAttribute()
                {
                    id = Guid.NewGuid(),
                    position = 1,
                    name = "Record1",
                    payload = "JOSH".CreateDummyJson()
                }
            }));

            // Test pull operations
            Helpers.TryExecute("ExecuteQuery",
                () => connBuilder().ExecuteQuery<EventReadEntityWithNpgsqlAttribute>("SELECT * FROM [es_events]"));
            Helpers.TryExecute("Query",
                () => connBuilder().Query<EventReadEntityWithNpgsqlAttribute>(where: e => e.name == "Record1"));
            Helpers.TryExecute("QueryAll", () => connBuilder().QueryAll<EventReadEntityWithNpgsqlAttribute>());
            Helpers.TryExecute("BatchQuery", () =>
                connBuilder().BatchQuery<EventReadEntityWithNpgsqlAttribute>(
                    rowsPerBatch: 100,
                    page: 0,
                    orderBy: OrderField.Parse(new {name = Order.Descending}),
                    where: e => e.name == "Record2"));
            Helpers.TryExecute("QueryMultiple",
                () => connBuilder()
                    .QueryMultiple<EventReadEntityWithNpgsqlAttribute, Table2>(where1: e => e.name == "Record1",
                        where2: t2 => t2.id == "a"));
            Helpers.TryExecute("ExecuteQueryMultiple", () =>
            {
                var result = connBuilder().ExecuteQueryMultiple("SELECT * FROM [es_events]; SELECT * FROM [another]");
                var person = result.Extract<EventReadEntityWithNpgsqlAttribute>();
                var addressCount = result.Extract<Table2>();
                return (person, addressCount);
            });
        }
    }
}

[mikependon]

Cool. Would you like a beta (or a hotfix) release for this?

[Swoorup]

Cool. Would you like a beta (or a hotfix) release for this?

That would be much appreciated 😄

[mikependon]

The fix for this is now available at RepoDb v1.12.1.

[Swoorup]

Can confirm it works now

[mikependon]

Thanks for confirming.