Refactoring my repository from using Ardalis to only use .NET 6 library, strugling with "Linq expression could not be translated"

I am refactoring a .NET 6/entity framework core 7 project which used to handle repository calls through Ardalis and Ardalis Specifications to add conditions to repository calls.

The idea of this refactoring is to only use Linq expressions and IQueryable instead of Ardalis specifications. I have a generic abstract repository class which all repositories can inherit of.

On paper, I was happy with the result and my code is compiling fine, unfortunatley when doing some tests on my API routes through Postman, I got the error : "Linq Expression could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'"

I will show you the code I had at first:

public abstract class GenericDbContextRepository<T, TKey> : IRepository<T, TKey> where T : class
{
    protected readonly DbContext GenericDbContext;
    protected readonly DbSet<T> DbSet;

    protected GenericDbContextRepository(DbContext DbContext)
    {
        GenericDbContext = DbContext;
        DbSet = GenericDbContext.Set<T>();
    }

    public async Task<IEnumerable<T>> FindAsync(Expression<Func<T, bool>> expression, IQueryable<T> query)
    {
        var originalQuery = DbSet.AsQueryable();
        var finalQuery = originalQuery.Concat(query);
        return await finalQuery.Where(expression).ToListAsync();
    }
}

For example, my repository "UserStoreRepository" would call FindAsync and it would pass the following IQueryable :

public static IQueryable<UserStore> GetUserStoreWithItemsQuery()
{
    IQueryable<UserStore> initialQuery = Enumerable.Empty<UserStore>().AsQueryable();
    return initialQuery
        .OrderByDescending(x => x.CreationDate)
        .Take(1)
        .Include(UserStore => UserStore.Items);
}

On first try, I got the following error:
> " The Linq Expression 'EnumerableQuery<UserStore>{}' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'"

One of my first idea to fix this issue was simply to follow the recommendation in the error message and use AsEnumerable() in my GetUserStoreWithItemsQuery() function, I ended up with the following code:

public static IQueryable<UserStore> GetUserStoreWithItemsQuery()
{
    IQueryable<UserStore> initialQuery = Enumerable.Empty<UserStore>().AsQueryable();
    
    return initialQuery
        .AsEnumerable()
        .OrderByDescending(x => x.CreationDate)
        .Take(1)
        .AsQueryable()
        //Include can only be used on IQueryable, not on IEnumerable
        .Include(UserStore => UserStore.Items);
}

The Linq expression's translation here worked and I ended up with a new error from my FindAsync() function :

> " The Linq Expression 'DbSet<UserStore>{}' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'"

I thought of applying the same fix with AsEnumerable():

public async Task<IEnumerable<T>> FindAsync(Expression<Func<T, bool>> expression, IQueryable<T> query)
{
    var originalQuery = DbSet.AsEnumerable();
    var finalQuery = originalQuery.Concat(query.AsEnumerable());
    return await finalQuery.AsQueryable().Where(expression).ToListAsync();
}

But now I have the following error :

> The provider for the source 'IQueryable' doesn't implement 'IAsyncQueryProvider'. Only providers that implement 'IAsyncQueryProvider' can be used for Entity Framework asynchronous operations.

I probably could "bypass" and implement 'IAsyncQueryProvider' but I feel like I should not have to, I am guessing there must be a better and cleaner way to achieve what I am trying to, and I am starting to think switching to Enumerable then back to IQueryable is probably not the good way of doing things, whether for code/database call performance and code quality. I welcome any advices and fixes.

Thank you all.

I think, in your particular case, the FindAsync method could be similar to this one, please take a look:

public virtual Task<IEnumerable<TEntity>> FindAsync(
    Expression<Func<TEntity, bool>>? predicate = null,
    CancellationToken token = default,
    params Expression<Func<TEntity, object>>[] navigationProperties)
{
    IQueryable<TEntity> dbQuery = _dbSet.AsNoTracking();

    if (predicate != null)
    {
        dbQuery = dbQuery.Where(predicate);
    }

    return await navigationProperties
        .Aggregate(dbQuery, (current, navigationProperty) => current.Include(navigationProperty))
        .ToListAsync(token);
}