InvalidOperationException: The LINQ expression could not be translated, even though I used ToListAsync() - EF Core 3.1

Question

I have this code in my repository:

public async Task<IndexViewModel> GetAllPosts(int pageNumber, string category)
{
    Func<Post, bool> InCategory = (post) => post.Category.Equals(category, StringComparison.OrdinalIgnoreCase);

    int pageSize = 5;
    int skipAmount = pageSize * (pageNumber - 1);

    var query = dbContext.Posts.AsQueryable();

    if (!String.IsNullOrEmpty(category))
        query = query.Where(x => InCategory(x));

    return new IndexViewModel()
    {
        Posts = await query
            .Skip(skipAmount)
            .Take(pageSize)
            .ToListAsync()
    }; 
}

I get this error:

An unhandled exception occurred while processing the request.

InvalidOperationException: The LINQ expression 'DbSet .Where(p => Invoke(__InCategory_0, p[Post]) )' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/linkid=2101038 for more information.

Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.g__CheckTranslated|8_0(ShapedQueryExpression translated, ref <>c__DisplayClass8_0 )

Now this is despite the fact that I used .ToListAsync(), which it tells me to use in the error. How do I fix this problem?

Answer 1

You have to use Expression if you expect execution this on the server side. Also, usually, database has case insensitiive collation and you don't have to worry about case.

public async Task<IndexViewModel> GetAllPosts(int pageNumber, string category)
{
    Expression<Func<Post, bool>> InCategory = (post) => post.Category == category;

    int pageSize = 5;
    int skipAmount = pageSize * (pageNumber - 1);

    var query = dbContext.Posts.AsQueryable();

    if (!String.IsNullOrEmpty(category))
        query = query.Where(InCategory);

    return new IndexViewModel()
    {
        Posts = await query
            .Skip(skipAmount)
            .Take(pageSize)
            .ToListAsync()
    }; 
}