InvalidOperationException: The LINQ expression could not be translated, even though I used ToListAsy-CodePudding

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?

CodePudding user response：

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()
    }; 
}

CodePudding user response：

It seems that LINQ queries that would have poor performance because a lot of data will have to be loaded into memory are blocked by EF Core.

We can still do it explicitly if we must by calling AsEnumerable() before filtering the data like:

var blogs = context.Blogs
    .AsEnumerable()
    .Where(blog => StandardizeUrl(blog.Url).Contains("dotnet"))
    .ToList();

This is not recommended unless the data is small, because all of Blogs in this example is being loaded into memory.

Learn more