dotnet data_error ai_generated true

System.InvalidOperationException: Cannot use split query for a query that uses Include on a collection navigation that is also used in a Select. Either call AsSplitQuery() before the Include or remove the Select.

ID: dotnet/ef-core-query-split-query-exception

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

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
Entity Framework Core 7.0 active — — —
Entity Framework Core 8.0 active — — —

Root Cause

Entity Framework Core throws this when a LINQ query combines 'Include' on a navigation property with a 'Select' that projects the same navigation, conflicting with split query execution mode.

generic

中文

当 LINQ 查询在导航属性上组合使用 'Include' 和投影同一导航的 'Select' 时，与拆分查询执行模式冲突，Entity Framework Core 会抛出此异常。

Official Documentation

https://learn.microsoft.com/en-us/ef/core/querying/single-split-queries#split-queries

Workarounds

  1. 90% success Place 'AsSplitQuery()' before the first 'Include' in the query chain: 'var result = context.Orders.AsSplitQuery().Include(o => o.Items).Where(o => o.Id == 1).ToList();' 
    Place 'AsSplitQuery()' before the first 'Include' in the query chain: 'var result = context.Orders.AsSplitQuery().Include(o => o.Items).Where(o => o.Id == 1).ToList();'
  2. 85% success Replace 'Include' with a 'Select' that explicitly projects the related data: 'var result = context.Orders.Where(o => o.Id == 1).Select(o => new { o.Id, Items = o.Items.Select(i => i.Name) }).ToList();' 
    Replace 'Include' with a 'Select' that explicitly projects the related data: 'var result = context.Orders.Where(o => o.Id == 1).Select(o => new { o.Id, Items = o.Items.Select(i => i.Name) }).ToList();'

中文步骤

  1. 在查询链中第一个 'Include' 之前放置 'AsSplitQuery()'：'var result = context.Orders.AsSplitQuery().Include(o => o.Items).Where(o => o.Id == 1).ToList();'
  2. 用显式投影相关数据的 'Select' 替换 'Include'：'var result = context.Orders.Where(o => o.Id == 1).Select(o => new { o.Id, Items = o.Items.Select(i => i.Name) }).ToList();'

Dead Ends

Common approaches that don't work:

  1. 80% fail

    Adding 'AsSplitQuery()' after 'Include' does not fix the issue; it must be placed before the Include chain.

  2. 65% fail

    Removing 'Include' and relying solely on lazy loading may cause N+1 query problems and performance degradation.

  3. 70% fail

    Switching to 'AsSingleQuery()' globally avoids the error but may cause cartesian explosion and slow queries.