Linq filtering by class array properties

user_14384748 0
Viewed 353

My schema is as enter image description here

I am currently getting the products with valiants like so

    products = context.Products
       .Include(x => x.Skus)
       .Include(x => x.ProductVariants)
           .ThenInclude(pv => pv.Option)
       .Include(x => x.ProductVariants)
           .ThenInclude(pv => pv.Value);

Now i am trying to add a filter functionality by OptionId and ValueId

The following list holds both the OptionId and the ValueId for every option selected at the UI

   List<Filter> filters;

where Filter is

public class Filter
{
    public int Oid { get; set; } //OptionId
    public int Vid { get; set; } //ValueId
}

How could i add filter functionality on this one?

After using

var v = context.Products.Include(x => x.ProductVariants)
    .Where(prod => prod.ProductVariants
    .Any(v => filters.Any(f => f.Oid == v.OptionId && f.Vid == v.ValueId)));

i got the error

The LINQ expression 'DbSet<ProductVariant>()
    .Where(p0 => EF.Property<Nullable<int>>(EntityShaperExpression: 
        EntityType: Product
        ValueBufferExpression: 
            ProjectionBindingExpression: EmptyProjectionMember
        IsNullable: False
    , "Id") != null && object.Equals(
        objA: (object)EF.Property<Nullable<int>>(EntityShaperExpression: 
            EntityType: Product
            ValueBufferExpression: 
                ProjectionBindingExpression: EmptyProjectionMember
            IsNullable: False
        , "Id"), 
        objB: (object)EF.Property<Nullable<int>>(p0, "ProductId")))
    .Any(p0 => __filters_0
        .Any(f => f.o == p0.OptionId && f.v == p0.ValueId))' 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.

Update While using

    var vv = context.ProductVariants
        .Where(v => filters.Any(f => f.Oid == v.OptionId && f.Vid == v.ValueId)).AsEnumerable();

The error is now

The LINQ expression 'DbSet<ProductVariant>()
    .Where(p => __filters_0
        .Any(f => f.Oid == p.OptionId && f.Vid == p.ValueId))' could not be translated.

Update The error persists even with only filter by option

    var vv = context.ProductVariants
        .Where(v => filters.Any(f => f.Oid == v.OptionId)).AsEnumerable();

Update The classes used are

    public class Product
        {
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int Id { get; set; }
    
            public IEnumerable<ProductVariant> ProductVariants { get; set; }
            public IEnumerable<Sku> Skus { get; set; }
        }

public enum FilterType
    {
        CheckBox,
        Radio,
        Button,
        List
    }

    public class Option
    {
        public int OptionId { get; set; }
        public string OptionName { get; set; }

        public FilterType FilterType { get; set; }
    }

    public class Value
    {
        public int ValueId { get; set; }
        public string OptionValue { get; set; }
    }

    public class Sku
    {
        public int SkuId { get; set; }

        public int ProductId { get; set; }

        public decimal Price { get; set; }

        [ForeignKey("ProductId")]
        public Product Product { get; set; }
    }

    public class ProductVariant
    {
        public int Id { get; set; }

        public int ProductId { get; set; }

        public int OptionId { get; set; }

        public int ValueId { get; set; }

        public int SkuId { get; set; }

        [ForeignKey("ProductId")]
        public Product Product { get; set; }

        [ForeignKey("OptionId")]
        public Option Option { get; set; }

        [ForeignKey("ValueId")]
        public Value Value { get; set; }

        [ForeignKey("SkuId")]
        public Sku Sku { get; set; }
    }

Update I have narrowed the error to be related with the Filter class

By using

List<int> ints = new List<int>();
ints.Add(1);

and

    var vv = context.ProductVariants
        .Where(v => ints.Any(f => f == v.OptionId));

it just works. Should i use an expression or something else?

static Expression<Func<...
.net-5 entity-framework-core linq c#
3 Answers

It'll likely end up looking like:

.Where(prod => prod.Variants.Any(v => filters.Any(f => f.Oid == v.OptionId && f.Vid == v.VariantId)))

If your ProductVariants entity has no Id (not sure how you set up your entities), you might need f.Oid == v.Option.Id && f.Vid == v.Variant.Id

Unless you actually need to select fields out of Sku, Option and Value they could be dropped from the query; realistically only product and product variant are needed for the filtering

Edit:

Looks like the ORM is struggling to turn the linq into SQL. Starting from product variant might help:

context.ProductVariants
  .Where(v => filters.Any(f => f.Oid == v.OptionId && f.Vid == v.VariantId))

If that works out, add your product in

user_14106640

I wasn't able to replicate the problem with this code in a .net core winforms app and ef5:

using Microsoft.EntityFrameworkCore;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Linq;
using System.Windows.Forms;

namespace WFNetCoreCSWithEF5
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

            var f = new List<Filter>() { new Filter { Oid = 1, Vid = 2 } };
            var x = new ProductDbContext().ProductVariant.Where(pc => f.Any(f => f.Oid == pc.OptionId && f.Vid == pc.ValueId));
        }

    }


    public class Filter
    {
        public int Oid { get; set; } //OptionId
        public int Vid { get; set; } //ValueId
    }

    public class Product
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        public IEnumerable<ProductVariant> ProductVariants { get; set; }
        //public IEnumerable<Sku> Skus { get; set; }
    }

    public enum FilterType
    {
        CheckBox,
        Radio,
        Button,
        List
    }

    public class Option
    {
        public int OptionId { get; set; }
        public string OptionName { get; set; }

        public FilterType FilterType { get; set; }
    }

    public class Value
    {
        public int ValueId { get; set; }
        public string OptionValue { get; set; }
    }

    public class Sku
    {
        public int SkuId { get; set; }

        //public int ProductId { get; set; }

        public decimal Price { get; set; }

        //[ForeignKey("ProductId")]
        //public Product Product { get; set; }
    }

    public class ProductVariant
    {
        public int Id { get; set; }

        public int ProductId { get; set; }

        public int OptionId { get; set; }

        public int ValueId { get; set; }

        public int SkuId { get; set; }

        [ForeignKey("ProductId")]
        public Product Product { get; set; }

        [ForeignKey("OptionId")]
        public Option Option { get; set; }

        [ForeignKey("ValueId")]
        public Value Value { get; set; }

        [ForeignKey("SkuId")]
        public Sku Sku { get; set; }
    }

    public class ProductDbContext : DbContext
    {
        public DbSet<Product> Products { get; set; }
        public DbSet<ProductVariant> ProductVariant { get; set; }
        public DbSet<Sku> Skus { get; set; }

        public DbSet<Option> Options { get; set; }

        public DbSet<Value> Values { get; set; }


        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
        }

        protected override void OnConfiguring(DbContextOptionsBuilder options)
            => options.UseSqlServer(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=c:\temp\new3.mdf;Integrated Security=True;Connect Timeout=30");
    }
}

And these DOS commands:

dotnet ef migrations add X
dotnet ef database update

To create the db

user_14106640

Okay, ended up with PredicateBuilder

  if (filters.Any())
  {
    var predicate = PredicateBuilder.False<Product>();

    foreach (var filter in filters)
        predicate = predicate.Or(p => p.ProductVariants.Any(x => x.OptionId == filter.o & x.ValueId == filter.v));

    products = products.AsQueryable().Where(predicate);
  }

  products = products.AsQueryable()
    .Include(x => x.ProductVariants)
      .ThenInclude(pv => pv.Option)
    .Include(x => x.ProductVariants)
      .ThenInclude(x => x.Value);
user_143847480
Related