Remove EF warning logs 'The query uses a row limiting operator'

[olicooper]

Since .NET 5, Microsoft has un-obsoleted the CoreEventId.FirstWithoutOrderByAndFilterWarning and CoreEventId.RowLimitingOperationWithoutOrderByWarning

I get warning logs like this when using methods like the default IReadOnlyAppService.GetAsync method and queries with

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

Note: The log above appears for FirstOrDefault() calls as well as ToList() even though the CoreEventId.FirstWithoutOrderByAndFilterWarning warning should be written. I'm not sure why this happens but its probably a bug with EF.

Is there a way to apply something like the AbstractKeyReadOnlyAppService.ApplyDefaultSorting method to the EfCoreRepository for FindAsync calls (and default back to sorting by Entity.Id if CreationTime doesn't exist) or change it to SingleOrDefaultAsync?

abp/framework/src/Volo.Abp.EntityFrameworkCore/Volo/Abp/Domain/Repositories/EntityFrameworkCore/EfCoreRepository.cs

Line 412 in 158158e

? await (await WithDetailsAsync()).FirstOrDefaultAsync(e => e.Id.Equals(id), GetCancellationToken(cancellationToken))

[realLiangshiwei]

Thanks, I will check it out.

[olicooper]

I've just done some testing and SingleOrDefault doesn't change anything, but changing the query to
(await WithDetailsAsync()).OrderBy(x => x.Id).FirstOrDefaultAsync stops the warning appearing.
It would be nicer if we could order by CreationTIme when possible like .OrderBy(x => ((Volo.Abp.Auditing.IHasCreationTime)x).CreationTime) ... Actually if we are just searching for a single record by Id then it is probably pointless.

Thanks for looking 👍

[davidzwa]

I posted this issue with the same topic. It was closed, but it would be nice to see why.
#6981

[maliming]

hi @davidzwa

We plan to enhance GetAsync to eliminate EF warnings

[olicooper]

Sorry @davidzwa I missed that issue! This change should fix the issue when getting a single record from the database via the EfCoreRepository, but there will probably be edge cases when getting lists of items that can't be sorted by Id or CreationTime, so you would have to add the OrderBy statement yourself or override the ApplyDefaultSorting method shown below.

abp/framework/src/Volo.Abp.Ddd.Application/Volo/Abp/Application/Services/AbstractKeyReadOnlyAppService.cs

Lines 123 to 131 in 3789b0d

	protected virtual IQueryable<TEntity> ApplyDefaultSorting(IQueryable<TEntity> query)
	{
	if (typeof(TEntity).IsAssignableTo<IHasCreationTime>())
	{
	return query.OrderByDescending(e => ((IHasCreationTime)e).CreationTime);
	}
	throw new AbpException("No sorting specified but this query requires sorting. Override the ApplyDefaultSorting method for your application service derived from AbstractKeyReadOnlyAppService!");
	}

[davidzwa]

If I understand correctly the best advice is to not use IRepository<TEntity, TKey> directly and fix the OrderBy on application level? So far I was using the repo for seeding.

I can wait for the preview, np ;)

[olicooper]

You can override most functionality in the system simply by inheriting the class you want to override, then using [Dependency(ReplaceServices = true)] above the class and override the particular function (such as IRepository.GetAsync), but you shouldn't need to after this preview release which will probably be released later today. If you do need to though, take a look at this: https://docs.abp.io/en/abp/latest/Dependency-Injection#replace-a-service