I'm having a problem with writing a query for filtering products with a filter. I would like to be able to filter by a ProductFilter but I'm getting

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

    public class ProductFilter
    {
        public DateTime? AvailableFrom { get; set; }
        public DateTime? AvailableTo { get; set; }
        public Size? Size { get; set; }
        public int? RangeFrom { get; set; }
        public int? RangeTo { get; set; }
        public List<Facet>? Facets { get; set; }
    }

    public class Facet
    {
        public Guid Id { get; set; }
        public List<FacetValue> FacetValues { get; set; } = new List<FacetValue>();
    }

    public class FacetValue
    {
        public Guid Id { get; set; }
    }

public class Product : EntityBase
{
    public virtual ICollection<ProductVariant>? Variants { get; set; }
    public virtual ICollection<FacetValue> FacetValues { get; set; } = new List<FacetValue>();
}

public class ProductVariant : EntityBase
{
    public Guid ProductId { get; set; }
    public virtual Product Product { get; set; } = null!;

    public Size Size { get; set; }
    public virtual ICollection<Order> Orders { get; set; } = new List<Order>();
    public virtual ICollection<FacetValue> FacetValues { get; set; } = new List<FacetValue>();
}

public class FacetValue : EntityBase
{
    public string Name { get; set; } = null!;

    public Guid FacetId { get; set; }
    public virtual Facet Facet { get; set; } = null!;
    
    public virtual ICollection<ProductVariant> ProductVariants { get; set; } = new List<ProductVariant>();
    public virtual ICollection<Product> Products { get; set; } = new List<Product>();
}

public class Facet : EntityBase
{
    public string Name { get; set; } = null!;
    public virtual ICollection<FacetValue> Values { get; set; } = new List<FacetValue>();
}

      return productsQuery
                        .Where(p =>
                            p.Variants != null && p.Variants!.Any(pv =>
                                !pv.Orders.Any(o =>
                                    o.StartDate <= requestFilter.AvailableTo ||
                                    o.EndDate <= requestFilter.AvailableTo
                                )
                                &&
                                (requestFilter.Size == null || pv.Size == requestFilter.Size)
                                && pv.State == ProductVariantState.Active
                                &&
                                (
                                    requestFilter.Facets.IsNullOrEmpty() ||
                                    requestFilter.Facets!.All(
                                        filterFacet =>
                                            filterFacet.FacetValues
                                                .Any(filterFacetValue =>
                                                    p.FacetValues.Any(fv => fv.Id == filterFacetValue.Id))
                                            ||
                                            filterFacet.FacetValues
                                                .Any(filterFacetValue =>
                                                    pv.FacetValues.Any(fv => fv.Id == filterFacetValue.Id))
                                    )
                                )
                            )
                        );

CodePudding user response：

The answer was actually pretty straightforward.

        if (!requestFilter.Facets.IsNullOrEmpty())
        {
            foreach (var filterFacet in requestFilter.Facets!)
            {
                productsQuery = productsQuery
                    .Where(p =>
                        p.Variants != null && p.Variants!.Any(pv =>
                            (requestFilter.Size == null || pv.Size == requestFilter.Size) &&
                            pv.State == ProductVariantState.Active &&
                            (
                                p.FacetValues.Any(
                                    pFv => filterFacet.FacetValues.Select(fv => fv.Id).Contains(pFv.Id)
                                )
                                ||
                                pv.FacetValues.Any(
                                    pvFv => filterFacet.FacetValues.Select(fv => fv.Id).Contains(pvFv.Id)
                                )
                            )
                        )
                    );
            }
        }

But I'm wondering how I could refactor the code to make it more usable and extract for instance the bit below:

!pv.Orders.Any(o =>
    o.StartDate <= requestFilter.AvailableTo ||
    o.EndDate <= requestFilter.AvailableTo)