Multiple dynamic query in Linq

Question

First of all, I will explain the table structure

First table Attribute          Second table Category

Id       Name            Id     Name    AttributeId(Foreign Key)
------------------      -----------------------------------------
1      Material          1      Cotton   1
2      Size              2      Black    3
3      Color             3      White    3
                         4      Linen    1

Third Table ProductCatLink

Id     CategoryId(Fk Category table)   ProductId(FK of Product Table)
-----------------------------------------------------------------------------
1         1                                     5
2         2                                     6
3         2                                     5
4         4                                     6
5         4                                     7
6         3                                     8

Consider user selects material cotton and color black and white then result must be

 Id       CategoryId     ProductId(ForeignKey of Product Table)
 ---------------------------------------------------------------
    1         1               5
    2         2               5

Consider user selects material cotton and linen and color black and white then result must be

Id       CategoryId     ProductId(ForeignKey of Product Table)
-------------------------------------------------------------------
    1         1               5
    2         4               6
    3         2               5
    4         1               6

user passes categoryid array from UI like {1,2,3}(cotton material with black or white color) or {1,4,3} (cotton or linen material with black color) must group with attribute id to achieve this.

I tried different way using contains but not working properly, if checking each product with all category using custom loops works fine but comparing each products with all condition causes performance issue.

Is there any simple solution for this problem? Is there any solution using Predicatebuilder in linq?

any code snippets on how to achieve this using linq will be helpful

I done it like this is there any performance issue with it?

 ExpressionStarter<Products> ProductPredicater(params int[] categories)
          {
                var predicate = PredicateBuilder.New<Products>();
                var catwithAttributes = from cat in categories.AsEnumerable()
                                        join pSubCat in 

                 _productSubCat.GetAll().AsEnumerable()
                                    on cat equals pSubCat.Id                                    
                                    select pSubCat;

              var attributeids = catwithAttributes.GroupBy(m => 
              m.AttributeId).Select(m => m.Key);
          

            foreach (int keyword in attributeids)
            {
                var subcatlist = catwithAttributes.Where(m => m.AttributeId == keyword).Select(m => m.Id).ToList();
                predicate = predicate.And(p =>  p.ProductCategoryLinkDetails.Any( l=> subcatlist.Contains(Convert.ToInt32(l.ProductSubCategory_Id))));                
            }

            return predicate;
        }


var result =   from p in products.Get(ProductPredicater(input.ProductCategoryId), 
               null, m => m.ProductCategoryLinkDetails) select p;

Answer 1

Very hard to understand your question, but I think I get idea. Looks not performant but I'll update answer when find better solution.

var materials = new[] { 1 };
var colors    = new[] { 2, 3 };

var query = from p in db.Products
   where 
     p.ProductCatLinks.Any(l => materials.Contains(l.CategoryId)) &&
     p.ProductCatLinks.Any(l => colors.Contains(l.CategoryId))
   select p;