EF Core 3.0 Linq decimal compare (Greater or Less than) not working - could not be translated

user_61525 0
Viewed 637

This simple statement which used to work in 2.2 DOES NOT WORK anymore in 3.1. I get the error:

var qry = from p in ctx.Shifts where p.StartTime < 1
          select p;
var list = qry.ToList(); //This fails

NOTE THAT both ">" and "<" do not work but "==" works.

The object is as shown below

public class Shift
{
   public decimal StartTime {get;set;}
   public decimal EndTime {get;set;}
}

I get the error:

System.InvalidOperationException : The LINQ expression 'DbSet .Where(p => p.StartTime < 1)' 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()

ef-core-3.1 entity-framework-core linq-to-entities
1 Answers

First, "working" in EF Core 1.x / 2.x and not working in EF Core 3.x+ simply means the expression was silently evaluated on client, and now is failing because EF Core 3.0 removed implicit client evaluation and requires you to explicitly resolve them using the suggested options by the exception message.

Second, the actual issue here is that you seem to be using SQLite and hitting one of its EF Core Query limitations:

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.

  • DateTimeOffset
  • Decimal
  • TimeSpan
  • UInt64

For decimal you can use the recommendation from the same link (the official EF Core documentation) to map it as double by changing the property type or using value converter, e.g.

if (Database.IsSqlite())
{
    modelBuilder.Entity<Shift().Property(e => e.StartTime).HasConversion<double>();
    modelBuilder.Entity<Shift().Property(e => e.EndTime).HasConversion<double>();
}
user_52025630
Related