Returning related elements in a single query (Is my query bad?)

Question

Hey, I have a table called products that has a relationship with itself. This table stores products, their sub-products, and then sub-sub-products. When a user searches for the top-most product, this query is supposed to return the parent, its children, and its children's children. Now, my query works, but I am new to the entity framework and SQL in general, so I am not sure if this is the way it should be done. To make things a bit more hairy, I am searching against multiple fields in the table.

else
            {
                productQuery = 
                    from b in solutionContext.Version
                    where 
                        (
                            b.Product.Name == search || b.Product.Description == search ||
                            b.Product.Product2.Name == search || b.Product.Product2.Description == search ||
                            b.Product.Product2.Product2.Name == search || b.Product.Product2.Product2.Description == search
                        )
                    orderby b.Product.LastNumber ascending
                    select b;
            }

For clarification, Product2 is the relationship that goes from the child to the parent.

Subquestion: In the future I want to a search for a child, to return its parent, and parent's parent. The way I would currently go about doing that is to add some lambda expressions and do what I did, but going up the relationship. Is that smart?

Answer 1

There's nothing really wrong with this query. Certainly, accessing related members is expected in L2E.

Consider using syntax like:

b.Product.Name.Equals(search, StringComparison.OrdinalIgnoreCase)

Searches should generally be case-insensitive.