EF-SQLite: Translation of method 'System.Math.Abs' failed.

[springy76]

I have an entity Document with a decimal property:

	public decimal? TotalNet { get; set; }

	public decimal? TotalGross { get; set; }

and ModelBuilder applies conversion to double (as suggested by docs about limits of SQLite): prop.HasConversion<double>()

Executing this query:

dbcontext.Documents.Where(d => Math.Abs(d.TotalNet ?? 0) > Math.Abs(d.TotalGross ?? 0) ).ToList();

fails with InvalidOperationException:

The LINQ expression 'DbSet()
.Where(d => Math.Abs(d.TotalNet ?? 0) > Math.Abs(d.TotalGross ?? 0))' could not be translated. Additional information: Translation of method 'System.Math.Abs' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information.
Translation of method 'System.Math.Abs' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

before any SQL got created. (BTW: The link with number 2132413 is totally unhelpful and total nonsense).

ExceptionCallstack: HResult -2146233079

   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

---

In comparison the same using SQL-Server and prop.HasColumnType("money") produces+executes SQL:

WHERE ABS(COALESCE([d].[TotalNet], 0.0)) > ABS(COALESCE([d].[TotalGross], 0.0))

and works.

---

In comparison the same using PostgreSQL (npgsql) and prop.HasColumnType("money") produces+executes SQL:

WHERE abs(COALESCE(d."TotalNet", 0.0::money)) > abs(COALESCE(d."TotalGross", 0.0::money))

and fails with PostgresException:

42883: function abs(money) does not exist

---

Include provider and version information

EF Core version: 6.0.7
Database provider: Microsoft.EntityFrameworkCore.Sqlite
Target framework: .NET 6.0
Operating system: Win10
IDE: VS 2022 / LINQPad

[roji]

@springy76 the SQLite provider doesn't translate the overload of Math.Abs which accepts a decimal; in general, using a value converter doesn't automatically allow you to use functions defined on the destination type. This is what is meant by this note on the limitations of SQLite:

SQLite doesn't natively support the following data types. EF Core can read and write values of these types, and querying for equality (where e.Property == value) is also supported. Other operations, however, like comparison and ordering will require evaluation on the client.

Consider using double directly in your entity type instead of having a value converter from decimal to double.

[springy76]

@roji Thats why the conversion to float is in place: It allows sorting:

both .OrderByDescending(d => d.TotalGross) and .OrderByDescending(d => d.TotalGross ?? 0) work

and comparison, too:

.Where(d => (d.TotalNet ?? 0m) > (d.TotalGross ?? 0m) )

executes

WHERE COALESCE("d"."TotalNet", 0.0) > COALESCE("d"."TotalGross", 0.0)
ORDER BY COALESCE("d"."TotalGross", 0.0) DESC

Those also work using a custom converter which stores integer cents by applying factor 100 and derives from ValueConverter<decimal, long> and additionally adds a suffix to the column name, SQL then becomes:

WHERE COALESCE("d"."TotalNetCents", 0) > COALESCE("d"."TotalGrossCents", 0)
ORDER BY COALESCE("d"."TotalGrossCents", 0) DESC

and it executes without errors and the results are valid.

EFcore is more capable as you try to tell me ;)

[roji]

@springy76 yes, ordering is supported, since the SQL simply places the column name in the ORDER BY clause. Function translation is different: EF needs to recognize the Math.Abs CLR function, which it doesn't currently do.

[ajcvickers]

Covered by #10434