-2

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;
Nighil
  • 4,099
  • 7
  • 30
  • 56
  • Pull you table data in `cache` memory then you have better performance. These are called [EAV](https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model) tables that have performance issues when have heavy query. – Hassan Monjezi Oct 13 '20 at 08:06
  • If there are any query based solution, I will really appreciate that – Nighil Oct 13 '20 at 08:07
  • *"I tried different way using contains but not working properly"* And what were those attempts, you've not show any of them in your question.Why didn't those attempt work? What is your specific question here? *"Is there a simple solution"* is just a yes or no question; what is the programming question you have here? – Thom A Oct 13 '20 at 08:19
  • @Larnu I am not talking about the full code solution, if I get any snippets and some logic I can work out, thanks.. – Nighil Oct 13 '20 at 09:33
  • @Larnu I tried, check my edit – Nighil Oct 14 '20 at 07:38

1 Answers1

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;
Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32