efcore: False positive "Skip/Take without OrderBy" when using SplitQuery
When using .UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery)
we get a the following warning
The query uses a row limiting operator ('Skip'/'Take') without an 'OrderBy' operator. This may lead to unpredictable results.
even without really applying and row limiting operation.
Repro: Configuration:
p_serviceCollection.AddDbContext<T>(
options =>
{
options.ConfigureWarnings(w =>
w.Throw(CoreEventId.RowLimitingOperationWithoutOrderByWarning)); // Configured to throw instead of warn
options.UseSqlServer(Configuration[$"Database:{p_dbName}:ConnectionString"],
b =>
{
b.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery); // TOGGLE THIS BY COMMENTING
b.MigrationsAssembly("Application");
});
});
Query:
var user = await m_context.AccountUsers
.Include(u => u.ProfilePicture)
.Include(u => u.EmailDetails)
.Include(u => u.UserGroups)
.ThenInclude(ug => ug.AccountGroup)
.SingleOrDefaultAsync(u => u.Id == p_userId);
As far as I recall, SingleOrDefault{Async}
is not a row limiting (FirstOrDefault{Async}
is), and as you can see, there is no Skip/Take
here.
EF Core version 5.0.1 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 5.0.1 Operating system: Win 10 x64
About this issue
- Original URL
- State: closed
- Created 3 years ago
- Reactions: 6
- Comments: 15 (7 by maintainers)
Commits related to this issue
- Relational: Implement split query for non-include collections Resolves #21234 Resolves #22283 Resolves #23803 — committed to dotnet/efcore by smitpatel 3 years ago
- Relational: Implement split query for non-include collections Resolves #21234 Resolves #22283 Resolves #23803 — committed to dotnet/efcore by smitpatel 3 years ago
- Relational: Implement split query for non-include collections Resolves #21234 Resolves #22283 Resolves #23803 — committed to dotnet/efcore by smitpatel 3 years ago
- Relational: Implement split query for non-include collections (#24706) Resolves #21234 Resolves #22283 Resolves #23803 — committed to dotnet/efcore by smitpatel 3 years ago
The crucial point is that for SingleOrDefault, you get an exception for any set that contains more than one element (that’s just how the operator works). So an ORDER BY in SQL isn’t required: it’s OK for the database to return rows in any order, since if we get more than one we throw anyway. In contrast, FirstOrDefault without ORDER BY is completely non-deterministic: if you don’t specify ordering, you have no idea which row you get back out of the matching set.
It’s true that if you want only one row, FirstOrDefault allows you to fetch only rather than all of them, which is good for performance. However, you must still specify which row you want back, i.e. according to which ordering.