decimal parameters formatted incorrectly

[bricelam]

From @Mats391 on April 18, 2017 12:16

When I insert a decimal value into a Sqlite database using EntityFramework Core, it will save it in a Text column. This is fine and prevents loss of data, but sometimes makes you unable to find a value you added before

Steps to reproduce

• Create Sqlite DbContext
• Insert Entity with a decimal value without decimal places
• SaveChanges
• Try to find by inserted value

using(var db = new MyDbContext()) {
    db.Database.EnsureCreated();
    db.Add( new MyEntity() { Value = 3m } );
    db.SaveChanges();

    // Creates error
    db.Entities.First( x => x.Value == 3m );
}

It adds the value as "3" into the database, but uses "3.0" in the query. It seems to not use the same ToString()

Further technical details

EF Core version: 1.1.1
Database Provider: Microsoft.EntityFrameworkCore.Sqlite
Operating system: Win7 x86
IDE: Visual Studio 2017

Edit:
This works for some reason:

using(var db = new MyDbContext()) {
    db.Database.EnsureCreated();
    decimal value = 3m;
    db.Add( new MyEntity() { Value = value } );
    db.SaveChanges();

    // Works as expected
    var y = db.Entities.First( x => x.Value == value );
}

Copied from original issue: dotnet/efcore#8205

[bricelam]

Looks like we need to add the following.

partial class SqliteSqlGenerationHelper
{
    protected overrride string DecimalFormat => "G";
}

BUT this is only the tip of the iceberg. Any arithmetic operation will coerce the values into doubles before performing the operation. This could yield lossy results. In general, using decimal values (and any non-SQLite-primitive type like DateTime, DateTimeOffset, and TimeSpan) in an expression that gets evaluated on the server should be carefully reviewed for potential errors due to coercion.

[bricelam]

Oh wait, the above code probably isn't sufficient. They need to be TEXT literals.

[bricelam]

We should review all literals too--we had anther issue with Guid in 1.0.0.

[bricelam]

It looks like double and float literals are also wrong for certain values: (#8270)

SQLite Error 1: 'unrecognized token: "1.79769313486232E+308E0"'.

[bricelam]

@smitpatel the logic used by Microsoft.Data.Sqlite is value.ToString(CultureInfo.InvariantCulture) quoted as a string literal.

[bricelam]

From @smitpatel on June 29, 2017 23:54

This should be fixed in SQLite to allow lossless arithmetic operations.

[bricelam]

lossless less lossy

[bricelam]

Fixed by #381