CODEWITHSUNDEEP
c#sqlasp.netsql-serverlinq
Simon
Simon

Reputation: 577

Filtering products in web app

I have a table of products, and products can have any number of filter specifications assigned to them - i.e. colour, type, etc. The filter values assigned to a product are stored in a lookup table. The tables are a bit more complex but a simple example I can work from is below:

Products
-----------------
ProdID | Code | Price
1      | A1   | 2.99
2      | B1   | 10.99
10     | F4   | 20.00


Filters
-----------------
FilterID | Name
1        | Black
2        | Blue
4        | Round
10       | Waterproof


ProductFilterLookup
-------------------
ProdID | FilterID
1      | 1
1      | 4
1      | 10
2      | 1
2      | 10
10     | 1

So if a user selects products that are 'Black' and 'Waterproof' (FilterID's 1 and 10) the expected results would be ProdID's 1 and 2 as only those products have both of those filters.

A product can have any filters assigned to it. I want to get all products which macth a set of chosen filters (meaning it has all of the filter values).

I can do this in either LINQ or SQL and need it to be as efficient as possible (perfomance wise).

Upvotes: 0

Views: 228

Answers (2)

Radinator
Radinator

Reputation: 1088

@Simon

To answer your question, this SQL statements will help you:

SELECT pfl.id, p.productid, p.code, p.price, f.filterid, f.name 
FROM 
    productfilterlookup pfl
INNER JOIN
    products p ON (pfl.productid = p.productid)
INNER JOIN
    filters f ON (pfl.filterid = f.filterid)
WHERE pfl.filterid = 1 OR pfl.filterid = 10;

This will put some output like this:

+----+-----------+------+-------+----------+--------------+
| id | productid | code | price | filterid | name         |
+----+-----------+------+-------+----------+--------------+
|  1 |         1 | A1   |  2.99 |        1 | Black        |
|  3 |         1 | A1   |  2.99 |       10 | Waterproofed |
|  4 |         2 | B1   | 10.99 |        1 | Black        |
|  5 |         2 | B1   | 10.99 |       10 | Waterproofed |
|  6 |        10 | F4   | 20.00 |        1 | Black        |
+----+-----------+------+-------+----------+--------------+

Upvotes: 0

Gilad Green
Gilad Green

Reputation: 37299

For performance - You can get the linq to probably be as fast as the sql. Just have an sql and a linq and compare the explain plans of the two. See what sql it generates for the linq.

You can test:

var neededFilters = new List<string> { "Black", "Waterproof" };

var result1 = (from p in products
               join pf in productFilterLookup on p.ProdID equals pf.ProdID
               join f in filters on pf.FilterID equals f.FilterID
               group f.Name by p into grouping
               where neededFilters.All(filter => grouping.Contains(filter))
               select grouping.Key).ToList();

var result = (from p in products
              join f in (from pf in productFilterLookup
                         join f in filters on pf.FilterID equals f.FilterID
                         select new { pf.ProdID, f.Name })
                         on p.ProdID equals f.ProdID into grouping
              where neededFilters.All(filter => grouping.Any(item => item.Name == filter))
              select p).ToList();

Upvotes: 1

Related Questions