dotnet data_error ai_generated true

System.InvalidOperationException: The LINQ expression 'DbSet<X>.Where(x => x.Property.Contains("value") || x.OtherProperty.Contains("other"))' 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-query-cannot-be-translated-into-a-sql-expression

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

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
Entity Framework Core 7.0 active — — —
Entity Framework Core 8.0 active — — —
Microsoft.EntityFrameworkCore.SqlServer 8.0.0 active — — —

Root Cause

Entity Framework Core cannot translate a LINQ expression into SQL when it uses unsupported constructs like certain string methods, custom functions, or complex logic that the database provider does not support.

generic

中文

当 LINQ 表达式使用数据库提供程序不支持的某些字符串方法、自定义函数或复杂逻辑等构造时，Entity Framework Core 无法将其翻译为 SQL。

Official Documentation

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

Workarounds

  1. 85% success Rewrite the LINQ expression using only database-supported constructs. For example, replace 'x.Property.Contains("value") || x.OtherProperty.Contains("other")' with 'EF.Functions.Like(x.Property, "%value%") || EF.Functions.Like(x.OtherProperty, "%other%")' which translates to SQL LIKE. 
    Rewrite the LINQ expression using only database-supported constructs. For example, replace 'x.Property.Contains("value") || x.OtherProperty.Contains("other")' with 'EF.Functions.Like(x.Property, "%value%") || EF.Functions.Like(x.OtherProperty, "%other%")' which translates to SQL LIKE.
  2. 80% success If the query cannot be rewritten, use client evaluation explicitly but filter as much as possible on the server side first. For example: 'var filtered = await context.Orders.Where(o => o.Status == "Active").ToListAsync(); var result = filtered.Where(o => o.Description.Contains("keyword")).ToList();' 
    If the query cannot be rewritten, use client evaluation explicitly but filter as much as possible on the server side first. For example: 'var filtered = await context.Orders.Where(o => o.Status == "Active").ToListAsync(); var result = filtered.Where(o => o.Description.Contains("keyword")).ToList();'

中文步骤

  1. 仅使用数据库支持的构造重写 LINQ 表达式。例如，将 'x.Property.Contains("value") || x.OtherProperty.Contains("other")' 替换为 'EF.Functions.Like(x.Property, "%value%") || EF.Functions.Like(x.OtherProperty, "%other%")'，这会翻译为 SQL LIKE。
  2. 如果无法重写查询，则显式使用客户端评估，但首先在服务器端尽可能过滤。例如：'var filtered = await context.Orders.Where(o => o.Status == "Active").ToListAsync(); var result = filtered.Where(o => o.Description.Contains("keyword")).ToList();'

Dead Ends

Common approaches that don't work:

  1. 75% fail

    Calling 'ToList()' before the filter forces client evaluation but loads all data into memory, causing performance issues and potential out-of-memory errors.

  2. 80% fail

    Adding 'AsEnumerable()' at the start of the query chain also forces full client evaluation, defeating the purpose of database filtering.

  3. 90% fail

    Ignoring the error and assuming the query works on a different database provider (e.g., SQLite vs SQL Server) leads to runtime failures when deploying.