Row limiting warning Skip/Take before OrderBy - GetAsync of IRepository<T>

[davidzwa]

• 4.0.2 with EF Core

Volo.Abp.EntityFrameworkCore.MySQL 4.0.2
Volo.Abp.Identity.EntityFrameworkCore 4.0.2

• Exception message and stack trace if available (check the logs).

WRN] The query uses a row limiting operator ('Skip'/'Take') without an 'OrderBy' operator. This may lead to unpredictable results.

Looks similar to #6179 but then in IRepository when calling GetAsync (possibly others as well). In this case my entity extends FullAuditedEntity<Guid> and therefore IsDeleted query filter among others is present. Or this has more to do with paging/ordering. Not sure.

Can you check if this is as easy to fix as the issue mentioned above?

[maliming]

Hi

• Steps needed to reproduce the problem.

[davidzwa]

I call my UserRepository GetAsync (DI injected by IUserRepository)

await UserRepository.GetAsync(u => u.IdentityId == CurrentUser.Id, true);

This repository extends by IRepository

public class UserRepository : EfCoreRepository<TeamsiteDbContext, User, Guid>, IUserRepository
{
        public UserRepository(IDbContextProvider<TeamsiteDbContext> dbContextProvider) : base(dbContextProvider)
        {
        }
        
        public override IQueryable<User> WithDetails()
        {
            // IncludeDetails contains only .Include() and .ThenInclude() calls (nothing else like f.e. skip/take/orderby)
            return GetQueryable().IncludeDetails();
        }

}

given that IUserRepository is defined as follows:

public interface IUserRepository : IRepository<User, Guid> {
// Methods not relevant to problem and GetAsync
}

And of course within the AbpModule

            services.AddAbpDbContext<TeamsiteDbContext>(options =>
            {
                // Do note we are moving to custom Repositories to use AggregateRoot
                options.AddDefaultRepositories(includeAllEntities: true);
            });

The queried entity looks as follows:

    public class User : FullAuditedEntity<Guid>
    {
        public User() : base()
        {
        }
    }

The (adjusted serilog template) log then shows the warning twice, but never again unless I restart the API:

[11:46:40 WRN] (Microsoft.EntityFrameworkCore.Query.) The query uses a row limiting operator ('Skip'/'Take') without an 'OrderBy' operator. This may lead to unpredictable results.
[11:46:40 WRN] (Microsoft.EntityFrameworkCore.Query.) The query uses a row limiting operator ('Skip'/'Take') without an 'OrderBy' operator. This may lead to unpredictable results.

I've also tested calling ListAsync which does not show this double warning. Update/Delete I have not tested. Let me know if this helps!
I will test my other Repository<> extending classes right now, because I need to be sure that this is not my doing.

[davidzwa]

I confirm that my other IRepository<> repository shows the same warning, but then 3 times in a row. This made me to believe that the amount of warnings is related to the multiple query Include().ThenInclude() calls I do compared to HasQueryFilter additions. I used the following statement in OnModelCreating() on OneToMany children to prevent the inconsistent IsDeleted/Tenant filtering (this suppressed a warning by EF Core at startup):

            entity.HasQueryFilter(e => e.Event.IsDeleted == false);

This I did for 3 ManyToOne includes, which matches the amount of warnings.
Concerning the example I gave in the beginning of this issue, the 2 warnings also correspond with the amount of Include().ThenInclude() I did there.

The reason I did the HasQueryFilter is related to this exception, which I thought I had fixed:
https://docs.microsoft.com/en-us/ef/core/querying/filters#accessing-entity-with-query-filter-using-required-navigation
Specifically I quote the EF Core docs on that topic:

Using required navigation to access entity which has global query filter defined may lead to unexpected results.

I think this is related.

[maliming]

I think ABP framework can do nothing with this problem.

[davidzwa]

@maliming could you provide a tip on how to implement AuditedEntity for child entities properly? (SoftDelete on root, but no cascading delete on children because of that)
Or maybe a small hint as what is happening here?

[olicooper]

@davidzwa Are you still facing the "Using required navigation" issue? I have been working on a generic fix today - thanks for the link to the Microsoft filtering page, it helped me figure out a solution!

The idea is to 'copy' the configured GlobalQueryFilter on the main entity to the nested 'target' property.
It works like this: InheritQueryFilter<MyEntity, MyOtherEntity>(builder, x => x.MyOtherEntity);

Just create a CustomAbpDbContext.cs and inherit your project database context from it, then place the InheritQueryFilter calls at the bottom of OnModelCreating().

public abstract class CustomAbpDbContext<TDbContext> : AbpDbContext<TDbContext> where TDbContext : DbContext
{
    protected CustomAbpDbContext(DbContextOptions<TDbContext> options) : base(options) { }

    protected virtual void InheritQueryFilter<TEntity, TTarget>(
        ModelBuilder builder,
        Expression<Func<TEntity, TTarget>> target
    )
        where TEntity : class, Volo.Abp.Domain.Entities.IEntity
        where TTarget : class, Volo.Abp.Domain.Entities.IEntity
    {
        var entity = builder.Entity<TEntity>();

        var parentExpression = builder.Entity<TTarget>().Metadata
            ?.GetQueryFilter().As<Expression<Func<TTarget, bool>>>();

        if (parentExpression == null)
        {
            if (entity.Metadata.BaseType != null || !ShouldFilterEntity<TTarget>(entity.Metadata)) return;
            parentExpression = CreateFilterExpression<TTarget>();
            if (parentExpression == null) return;
        }

        var filter = Expression.Lambda<Func<TEntity, bool>>(
            new ReplaceExpressionVisitor(parentExpression.Parameters[0], target.Body)
                .Visit(parentExpression.Body),
            target.Parameters[0]);

        // Alternatively use this more robust method if you are using EntityFrameworkCore
        //  var filter = Expression.Lambda<Func<TEntity, bool>>(
        //      Microsoft.EntityFrameworkCore.Query.ReplacingExpressionVisitor
        //          .Replace(parentExpression.Parameters[0], target.Body, parentExpression.Body),
        //      target.Parameters[0]);

        entity.HasQueryFilter(filter);
    }

    private class ReplaceExpressionVisitor : ExpressionVisitor
    {
        private readonly Expression _oldValue, _newValue;

        public ReplaceExpressionVisitor(Expression oldValue, Expression newValue)
        {
            _oldValue = oldValue;
            _newValue = newValue;
        }

        public override Expression Visit(Expression node)
        {
            if (node == _oldValue) return _newValue;
                
            return base.Visit(node);
        }
    }
}

[davidzwa]

@olicooper I did it by simply adding a HasQueryFilter on the child entity. Will definitely share that and look at your solution as well. I also tried to answer a related issue #7482 based on my knowledge (which seems limited compared to what you have made).