LINQ expression can't be translated in unit test only

[stevendarby]

I build up a couple of LINQ expressions that should be translatable into SQL. In 2.2 this worked in the app and unit tests. In 3.1, it continues to work in the app - and I can see the SQL is executed as expected, with no client evaluation - however, in a unit test I get "The LINQ expression ... could not be translated."

I use SqlServer in the app and Sqlite in the unit test. Same context and configurations for both.

Steps to reproduce

Code adapted for an example:

public class Entity
{
    public string Property1 { get; set; }
    public int Property2 { get; set; }
}

Expression<Func<Entity, string>> filterMember = x => x.Property1;
Expression<Func<Entity, object>> selectMember = x => x.Property2;
const string filterVal = "testValue";

var parameter = filterMember.Parameters.First();
var filterMemberInvocation = Expression.Invoke(filterMember, parameter);
var equal = Expression.Equal(filterMemberInvocation, Expression.Constant(filterVal));
var where = Expression.Lambda<Func<Entity, bool>>(equal, parameter);
var max = context.Set<Entity>().Where(where).Max(selectMember);

Further technical details

EF Core version: 3.1
Database provider: Sqlite in unit tests
Target framework: 3.1
Operating system: Windows 10 1903
IDE: Visual Studio 2019 16.4.5

[smitpatel]

Can you post whole exception message?

[stevendarby]

The LINQ expression 'DbSet<Entity>
    .Where(s => s.Property1 == "testValue")
    .Max(s => s.Property2)' 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.

In trying to separate out a small reproducible solution I think I've discovered the crux of the issue. The 'selectMember' I'm using in the Max function is an int? property with a decimal? conversion for the DB. I believe Sqlite doesn't like filtering on decimals in 3.0/1+. For the test, I have added a double conversion for that property in a test DbContext that derives from my regular app DbContext, and this seems to have fixed it. This wasn't very clear from the exception.

[ajcvickers]

@Snappyfoo Support for decimals on SQLite is being improved for 5.0: See #19635.

Duplicate of #11925