none
Linq Select Query with filters for online stores RRS feed

  • Question

  • Hello,

    I have some problems with performance in an online store that offers the posibility to select multiple filters for products.

    For example : I need a query to filters all products with :

    - RAM  Memory : 4Gb or 6 Gb

    and

    - HDD: 150 Gb or 250 GB .

    I have attached an database diagram with tables .

    Filters - the table where I store general filters like : Ram Memory , HDD, Processor Types, etc

    FilterElements- the table where I store the filter Values : for Ram Memory the values are : 2Gb, 4 Gb

    Products- the table where I store the products

    ProductFilters - is the table where I store the asociation of filterElements to Products.

    I tried the next query:

    ( parsedFilters is an dictionary where the key is the filterID and the values are the selected filterElementID: Example:

    public List<Product> GetProductsByFilters(int vCategoryId, string filters, int maximumRows, int startRowIndex)
    {
                     int PageIndex = GetPageIndex(startRowIndex, maximumRows);
                     int PageSize = maximumRows;


                     Dictionary<int, List<int>> parsedFilters = ExtractFilters(filters);

                     var temp = (from lproduct in Shoppingctx.Products.Include("ProductFilters")
                                where
                                     lproduct.ProductCategoryID == vProductcategoryID                                
                                select lproduct);

                        foreach (KeyValuePair<int, List<int>> pair in parsedFilters)
                        {
                            int filterID = pair.Key;
                            List<int> values = pair.Value;

                            temp= temp.Where(p => p.ProductFilters.Any(y => y.FilterID.Value ==  filterID && y.FilterElementID.HasValue && values.Contains(y.FilterElementID.Value)));
                        }


                 List<Product> products = temp.Skip(PageSize * PageIndex).Take(PageSize).ToList();


                     return products;


      }


    But there are problems with performance .

    In my opinion, there is a problem because I split the query in 3 steps

    - Select by category

    - Select fy filters

    - Select rows for paging

    Is there a solution to do this in one step ?

    Any sugestions ?


                                                         
    • Edited by Lucian P. _ Saturday, August 10, 2013 6:00 AM
    Saturday, August 10, 2013 5:51 AM

Answers

  • Hello Lucian P. _,

    Thank you for posting in MSDN Forums.

    From your description, I notice the issue you are experiencing is that you want to know how to combine the query.

    As far as I know that you can use Dynamic LINQ.

    Here is the link that will show you how to use the Dynamic LINQ and the sample code:

    http://msdn.microsoft.com/en-us/library/vstudio/bb882637.aspx.

    And I found it would have a problem in the loop in your code. The temp will be covered by the result set of the last query option.

    I would recommend you define a template set to store the result in the loop each time; rather than using the “temp”.

    I look forward to hearing from you.

    Best Regards.


    Fred Bao
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, August 12, 2013 5:49 AM
    Moderator