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

Most upvoted comments

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.

Also, what I have learned long time ago is that FirstOrDefault is more efficient if we only need one result without worrying the rest. How come in EFCore5, it gives warning?

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.