Comparing decimals within a LINQ query using a SQLite-database fails with operators but works with .CompareTo

Question

In my SQLite database, I have a table foo that contains a column bar that is of data type decimal. I want to retreive all rows, where the value of bar is > 100.

If I try to execute the query like this:

var result = from r in db.Foo
             where r.Bar > 100
             select Bar;

I get the following error:

The LINQ expression 'DbSet<Foo>.Where(r => r.Bar > 100)' could not be translated. 

Either rewrite the query in a form that can be translated, 
or switch to client evaluation explicitly by inserting a call to either 
AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). 
See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

However, if I rewrite the query to use .CompareTo() instead of >, it works without a problem:

var result = from r in db.Foo
             where r.Bar.CompareTo(100) > 0
             select r;

I don't quite get why the first query works but the second one does not since they basically do the same things. Does .CompareTo() trigger client-side evaluation?

Answer 1

SQLite doesn't natively support decimal, however, EF Core can read and write values of these types, and querying for equality. [Source] This is why Bar > 100 does not work and is throwing an error.

The reason why Bar.CompareTo(100) > 0 works, is, because entity framework translates .CompareTo into the following query:

SELECT 
    "p"."Bar"
FROM 
    "Foo" AS "p"
WHERE
    CASE
        WHEN "p"."Bar" = '100.0' THEN 0
        WHEN "p"."Bar" > '100.0' THEN 1
        WHEN "p"."Bar" < '100.0' THEN -1
    END > 0

Here, entity framework converts the 100 to a TEXT and does the comparison that way.

This is probably not a very efficient way of handling the comparison. A better way would be to convert Bar to a double like so:

var result = from r in db.Foo
             where (double)r.Bar > 100
             select Bar;

The resulting SQL-query is:

SELECT 
    "p"."Bar" 
FROM 
    "Foo" AS "p" 
WHERE 
    CAST("p"."Bar" AS REAL) > 100.0