I had a tip that using ToList or ToListAsync is bad for performance and we have to use IQueryable to get data from database.

I tested it and it was correct, I got at least 2 times more performance doing it. Except one problem, I can't filter the data.

q.Where(x => x.Categories != null && 
             x.Categories.Any(y => dto.Categories.Contains(y.Id)))

Here I want to filter from the list of Ids that user sends as category Ids but it throws error and explicitly says use ToList

Is there a way to filter IQueryable without using ToList?

public class ProductEntity : BaseEntity {
    public string? Title { get; set; }
    public string? Subtitle { get; set; }
    public string? Description { get; set; }
    public IEnumerable<CategoryEntity>? Categories { get; set; }
}


public class ProductFilterDto {
    public IEnumerable<Guid>? Categories { get; set; }
}  




System.InvalidOperationException: The LINQ expression 'DbSet<ProductEntity>()
    .Where(p => p.UseCase == __dto_UseCase_0)
    .Where(p => MaterializeCollectionNavigation(
        Navigation: ProductEntity.Categories,
        subquery: DbSet<Dictionary<string, object>>("CategoryEntityProductEntity")
            .Where(c => EF.Property<Guid?>(p, "Id") != null && object.Equals(
                objA: (object)EF.Property<Guid?>(p, "Id"), 
                objB: (object)EF.Property<Guid?>(c, "ProductsId")))
            .Join(
                inner: DbSet<CategoryEntity>(), 
                outerKeySelector: c => EF.Property<Guid?>(c, "CategoriesId"), 
                innerKeySelector: c0 => EF.Property<Guid?>(c0, "Id"), 
                resultSelector: (c, c0) => new TransparentIdentifier<Dictionary<string, object>, CategoryEntity>(
                    Outer = c, 
                    Inner = c0
                ))
            .Select(ti => ti.Inner)) != null && DbSet<Dictionary<string, object>>("CategoryEntityProductEntity")
        .Where(c1 => EF.Property<Guid?>(p, "Id") != null && object.Equals(
            objA: (object)EF.Property<Guid?>(p, "Id"), 
            objB: (object)EF.Property<Guid?>(c1, "ProductsId")))
        .Join(
            inner: DbSet<CategoryEntity>(), 
            outerKeySelector: c1 => EF.Property<Guid?>(c1, "CategoriesId"), 
            innerKeySelector: c2 => EF.Property<Guid?>(c2, "Id"), 
            resultSelector: (c1, c2) => new TransparentIdentifier<Dictionary<string, object>, CategoryEntity>(
                Outer = c1, 
                Inner = c2
            ))
        .Any(ti0 => __dto_Categories_1
            .Contains(ti0.Inner.Id)))' 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'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

CodePudding user response：

Plus 1 on the comments for null check inside of the expression. EF will automatically accommodate if a Product does not have any Categories. EF's treatment of Linq is to build SQL rather than operate against objects in memory. So checks that you would use for in-memory assertions can end up causing translation issues in EF queries.

The Null-ability of the collection inside of the DTO could also possibly result in issues. To be safe I would recommend trying something like:

if (dto.Categories != null)
{
    var categoryIds = dto.Categories.ToList();
    q.Where(x => x.Catgories.Any(y => categoryIds.Contains(y.Id)));
}