Help needed with many-to-many filter

[Daniel Plomp]

Hi all,

I need some help with the following case:

Tables
- Product
- Keyword
- ProductKeyword (only ID's)

Each product can have one or more keywords and vice versa.

So I have a many-to-many solution. What I want is the following:
On my webpage I want to select one or more keywords. When I click 'Search', I need a result of product items which all match the selected keywords.

This is the code based on one keyword:

/// <summary>
        /// Search for products
        /// </summary>
        /// <param name="itemsPerPage"></param>
        /// <returns></returns>
        public List<Product> SearchProducts(int from, int max, List<Keyword> filter, out int totalitems)
        {
            try
            {
                // FILTER ON KEYWORDS

                var items = context.Products.Where(x => x.Visible == true);
                totalitems = items.Count();

                return items.Where(x => x.Visible == true).Skip(from).Take(max).ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

How can I do this?

Regards,
Daniel 

[Daniel Plomp]

Well, I've come with the following solution:

// Filter on keywords
var products = from p in context.Products
              from k in p.Keywords
              where filter.Contains(k)
              select p;

totalitems = products.Distinct().Count();
return products.Where(x => x.Visible == true).Distinct().Skip(from).Take(max).ToList();

This seems to work. If anyone has a better solution?

Regards,
Daniel

[Serge]

Hello Daniel Plomp,

 First of all I am glad you have found a solution to your problem.  Another solution would be to just use a collection of the identities of the desired keywords and query on that, however your solution looks fine in the current situation. 

If you need further help or face further trouble do not hesitate to let us know.

Greetings,
Serge
the Telerik team