CODEWITHSUNDEEP
linqentity-framework
Aximili
Aximili

Reputation: 29474

LINQ: Selecting products under a category, including sub-categories

The code below searches for products matching a keyword s or being under a category matching the keyword s.

It is working but tedious, I am wondering if there is a shorter way to do this?

var products = context.Products.Where(x => 
                 x.Name.Contains(s) ||
                 x.Products_Categories.Any(pc => 
                     pc.Category.Name.Contains(s) || 
                     (pc.Category.Category1 != null && pc.Category.Category1.Name.Contains(s)) || 
                     (pc.Category.Category1 != null && pc.Category.Category1.Category1 != null && pc.Category.Category1.Category1.Name.Contains(s) || 
                     (pc.Category.Category1 != null && pc.Category.Category1.Category1 != null pc.Category.Category1.Category1.Category1 != null && &&pc.Category.Category1.Category1.Category1.Name.Contains(s))
               );

If it is not obvious:

Products_Categories is a many-to-many relationship, a Product can be in one or more Category.

Category1 is the parent category of the Category.

Note that it is linked to a database so I cannot use functions like IsUnderCategory().
I don't need Expression at the moment because this code is only used once.

Upvotes: 2

Views: 1823

Answers (2)

Fabio Milheiro
Fabio Milheiro

Reputation: 8474

An alternative approach using EF is to simply query iteratively all of the categories for subcategories using a while cycle.

When you're done, you have all of the IDs. The first time, the query plan is not yet done and it takes a little while but then it's very fast depending on how monstrous your database is.

Then you can just query for all products that have any of a list of category IDs using the Contains method that gets translated to WHERE FieldName IN (1,2,3)

The advantage of this approach is that it is not dependent on how many levels of subcategories you have. It always works while the other alternative fails silently.

Upvotes: 0

Gert Arnold
Gert Arnold

Reputation: 109119

There is no shortcut to recursive querying in linq, less so in linq that is backed by a SQL query provider. However, there is one upside of the statement being translated to SQL first before being executed: SQL has no notion of a null reference. So you remove all the null checks from your statement:

var products = context.Products.Where(x => 
                 x.Name.Contains(s) ||
                 x.Products_Categories.Any(pc => 
                     pc.Category.Name.Contains(s) || 
                     pc.Category.Category1.Name.Contains(s) || 
                     pc.Category.Category1.Category1.Name.Contains(s) || 
                     pc.Category.Category1.Category1.Category1.Name.Contains(s))
               );

It will be translated to a SQL statement with many outer joins. As you'll know SQL won't crash if you address a field of an outer joined table when it has no record.

The query will be... monstrous. And you have to assume a max depth of the hierarchy. The only way to improve that is creating a view in the database that returns all categories of a product by a recursive SQL query.

Upvotes: 1

Related Questions