Datatype compatibility between databases (SQLite & SqlServer)

[ElDuderinoBerlin]

Datatype compatibility between databases (SQLite & SqlServer)

[jzabroski]

This does not sound like a FluentMigrator issue. It sounds like a coding issue where you are trying to cast in C# from long to double?. You mentioned Dapper twice, so I wonder if its really a Dapper issue. I'm not sure what code you wrote to achieve this exception, so I am closing this issue as not reproducible.

[jzabroski]

If you want to customize data types per database, one workaround would be to use the IfDatabase clause to customize logic per database tag.

[ElDuderinoBerlin]

I do this:

using var trans = await dest.BeginTransactionAsync(tks.Token);
await dest.DeleteAllAsync<Asset>(trans);

var copied = await dest.InsertAsync(await source.GetAllAsync<Asset>(), trans);
await trans.CommitAsync(tks.Token);

Then i get:

DataException: Error parsing column 49 (TrailingAnnualDividendRate=0 - Int64)
InvalidCastException: Unable to cast object of type 'System.Int64' to type 'System.Nullable`1[System.Double]'.

The datatype must be a double (REAL), but used is NUMERIC, which is INT64 & REAL (double) at the same time depending on the value stored in the table.

I do not cast. Dapper evaluates the value, which is INT64 but want write a double and doesn't cast.
I just just changed all NUMERICs to REAL and it works. It's indeed an issue of FluentMigrator, because it generates the DDL!

Regards
Martin

[ElDuderinoBerlin]

Just looked into https://github.com/fluentmigrator/fluentmigrator/blob/a292999ca5b4e3a462e5db0340d3cfee4883b9a0/src/FluentMigrator.Runner.SQLite/Generators/SQLite/SQLiteTypeMap.cs

Used is indeed a REAL, but why is standing there a NUMERIC?

Hm...

[ElDuderinoBerlin]

Found the issue! M$!
Microsoft.Data.Sqlite
Holy shit!