CODEWITHSUNDEEP
linqasp.net-coreentity-framework-coreasp.net-core-3.1ef-core-3.1
SpicyCatGames
SpicyCatGames

Reputation: 1696

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

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?

Upvotes: 0

Views: 2829

Answers (2)

SpicyCatGames
SpicyCatGames

Reputation: 1696

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 EFCore.

We can still do it explicitly if we must by calling AsEnumerable() or ToList() or their async counterparts 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

Upvotes: -1

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27461

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

Upvotes: 3

Related Questions