dotnet data_error ai_generated true

System.InvalidOperationException: The LINQ expression 'x => x.SomeMethod()' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

ID: dotnet/ef-core-translation-failure

Also available as: JSON · Markdown · 中文
85%Fix Rate
85%Confidence
1Evidence
2023-05-10First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
net6.0 active — — —
net7.0 active — — —
net8.0 active — — —
net9.0 active — — —

Root Cause

EF Core cannot translate a LINQ method call (e.g., custom methods, ToString on non-mapped types) into SQL, because it lacks a corresponding SQL function or mapping.

generic

中文

EF Core 无法将 LINQ 方法调用（例如自定义方法、未映射类型上的 ToString）转换为 SQL，因为它没有对应的 SQL 函数或映射。

Official Documentation

https://learn.microsoft.com/en-us/ef/core/querying/client-eval

Workarounds

  1. 90% success Rewrite the query to use translatable constructs. For example, replace '.Where(x => x.CustomMethod() == value)' with '.Where(x => x.Property == value)' if CustomMethod just accesses a property. If using ToString, use 'SqlFunctions.StringConvert' for numeric types or 'EF.Functions.DateDiff' for dates. 
    Rewrite the query to use translatable constructs. For example, replace '.Where(x => x.CustomMethod() == value)' with '.Where(x => x.Property == value)' if CustomMethod just accesses a property. If using ToString, use 'SqlFunctions.StringConvert' for numeric types or 'EF.Functions.DateDiff' for dates.
  2. 85% success Use client evaluation explicitly by calling .AsEnumerable() or .ToList() before the non-translatable part. Example: 'var results = context.Entities.Where(e => e.IsActive).AsEnumerable().Where(e => e.CustomMethod() == true).ToList();' 
    Use client evaluation explicitly by calling .AsEnumerable() or .ToList() before the non-translatable part. Example: 'var results = context.Entities.Where(e => e.IsActive).AsEnumerable().Where(e => e.CustomMethod() == true).ToList();'
  3. 80% success Register a custom queryable function using 'HasDbFunction' in OnModelCreating. Example: 'modelBuilder.HasDbFunction(() => MyDbContext.ConvertToUpper(default))' and map it to a SQL function like 'UPPER'. 
    Register a custom queryable function using 'HasDbFunction' in OnModelCreating. Example: 'modelBuilder.HasDbFunction(() => MyDbContext.ConvertToUpper(default))' and map it to a SQL function like 'UPPER'.

中文步骤

  1. Rewrite the query to use translatable constructs. For example, replace '.Where(x => x.CustomMethod() == value)' with '.Where(x => x.Property == value)' if CustomMethod just accesses a property. If using ToString, use 'SqlFunctions.StringConvert' for numeric types or 'EF.Functions.DateDiff' for dates.
  2. Use client evaluation explicitly by calling .AsEnumerable() or .ToList() before the non-translatable part. Example: 'var results = context.Entities.Where(e => e.IsActive).AsEnumerable().Where(e => e.CustomMethod() == true).ToList();'
  3. Register a custom queryable function using 'HasDbFunction' in OnModelCreating. Example: 'modelBuilder.HasDbFunction(() => MyDbContext.ConvertToUpper(default))' and map it to a SQL function like 'UPPER'.

Dead Ends

Common approaches that don't work:

  1. Adding .AsEnumerable() before the problematic method call without understanding which part causes the issue 75% fail

    AsEnumerable() forces client evaluation for the entire query, which may work but can cause performance issues and does not fix the root cause if the method is used multiple times.

  2. Using .Select() with a lambda that calls ToString() on a non-string property expecting EF Core to translate it 85% fail

    ToString() is not translatable for many types (e.g., decimal, DateTime) in older EF Core versions; it requires a specific SQL function like CAST or FORMAT.

  3. Changing the method to be static and assuming EF Core will recognize it 90% fail

    EF Core does not automatically translate custom static methods; they must be registered as queryable functions or converted to translatable expressions.