Advanced Search using Lambda Expression

Question

I want to implement Advanced Search in my ASP.NET MVC Application, where user can select 1 or more criteria for the product search.

Let's say I have these criterias: Color, Size, Price Range.

This is as far as I get till now

ProductSizeList = db.ProductSizes.Where(ProductSize =>
    (string.IsNullOrEmpty(ProductColorId) || ProductSize.Product.ProductColors.Where(a => a.ColorID == IntColorId).Any())
    ).GroupBy(x => x.ProductID).Select(Grouped => Grouped.FirstOrDefault()).ToList();

I have a many-to-many relationship between Product and Color tables, and ProductColor is the one linking them. Same thing with Product, ProductSize, Size tables.

The code is working perfectly with the Size, and Price Range. The problem with the Color, because .Any() returns when it finds the first Product. If There is more than one Product, it only returns the first one.

So, I want to know if there is another method, or another way to be able to get all products with the specified color.

I searched a lot, and know that I can build Where Clause Dynamically, but I think it would be too much work for my requirements. If there is a simple fix for this I would be more than happy.

Edit

I removed the working-as-wanted code as suggested by @Jeroen, and left the one I want to fix.

Solved

I fixed it, thanks to combining @jason and @Marlon answers. I'll put the solution in a separate answer. I just want to understand 2 points:

Why it only worked Correctly when I based my query on Product?
Why `.Distinct()` didn't do any thing, and I got duplicated products.

You probably should look into Predicates (https://stackoverflow.com/a/1710395/3951051) and maybe chain them. (https://stackoverflow.com/questions/1248232/combine-multiple-predicates) — WhoIsJohnDoe, Aug 25 '15 at 12:03
It would help if you trimmed down the code to just the actual lambda (part) you're having problems with, and include some minimal bit of set-up code to repro the issue. Currently I feel there's a lot of code bits distracting from the actual problem you're having. — Jeroen, Aug 25 '15 at 12:03
I guess you filter your results not by calling Any(), but here: Grouped.FirstOrDefault(). And by the way, you can call Any directly with predicate and without Where clause: Any(a => a.ColorID == IntColorId) — FireAlkazar, Aug 25 '15 at 12:12
@FireAlkazar This code discard duplicated results `GroupBy(x => x.ProductID).Select(Grouped => Grouped.FirstOrDefault())`, with`Size` and `Price Range`, it returns all results without duplication. Thanks for the `.Any()` part. — Ahmed, Aug 25 '15 at 12:18

score 4 · Accepted Answer · answered Aug 25 '15 at 12:25

4

Please try this. It uses IQueryable to let you more easily construct your conditions before executing against the database when ToList is called.

var query = db.ProductSizes.AsQueryable();
if (string.IsNullOrEmpty(ProductColorId) == false)
    query = query.Where(ProductSize => ProductSize.Product.ProductColors.Any(a => a.ColorID == IntColorId))
if (string.IsNullOrEmpty(SizeId) == false)
    query = query.Where(ProductSize => ProductSize.Size.Id == IntSizeId);
if (string.IsNullOrEmpty(From) == false)
    query = query.Where(ProductSize => ProductSize.Price >= DecimalFrom);
if (string.IsNullOrEmpty(To) == false)
    query = query.Where(ProductSize => ProductSize.Price <= DecimalTo);
var ProductSizeList = query
    .Select(ProductSize => ProductSize.ProductID)
    .Distinct()
    .ToList();

answered Aug 25 '15 at 12:25

Jason W

13,026
3
31
62

Sorry, but I don't get how this would differ from my solution. `.Any()` would return once the condition is fulfilled, and would return the first product only. – Ahmed Aug 25 '15 at 12:42
2

The `.Any()` here returns true only if colors linked to a product that is also linked to the ProductSize in your query. It respects the products that match your other conditions. Perhaps the non-desirable results are due to the `FirstOrDefault` in the `GroupBy` or the From/To that wasn't really separated in your original query or because you have other product sizes linked to products you didn't expect with matching colors? Without seeing your data, it's difficult to guess what's wrong here, but this approach should be fine. I've used it many times. – Jason W Aug 25 '15 at 12:53

score 1 · Answer 2 · answered Aug 25 '15 at 12:40

Try to base your query on Product, instead of ProductSize: (You didn't say , so i'm supposing Size and Price are in ProductSize objects).

var query = db.Product.AsQueryable();
if (string.IsNullOrEmpty(ProductColorId) == false)
    query = query.Where(product => product.ProductColors.Where(a => a.ColorID == IntColorId).Any());
if (string.IsNullOrEmpty(SizeId) == false)
    query = query.Where(product => product.Sizes.Where(s => s.Size_Id == IntSizeId));

(...)

var ProductSizeList = query
    .Select(product => product.ProductID)
    .Distinct()
    .ToList();

I prefere Jason W's approach instead of putting everything in one query, because in the last the 'or' in the middle of the query would ignore any index in the database.

Sorry, but I don't get how this would differ from my solution. `.Any()` would return once the condition is fulfilled, and would return the first product only. — Ahmed, Aug 25 '15 at 12:52
Any is inside Product.Where, so it would be called for each product.And basing your query on Product entity, you wouldn't need to have a Group by. — Marlon, Aug 25 '15 at 18:51

Ahmed · Answer 3 · 2015-08-26T08:02:45.643

This is what worked for me

var query = db.Products.AsQueryable();
if (string.IsNullOrEmpty(ProductColorId) == false)
    query = query.Where(Product => Product.ProductColors.Any(a => a.ColorID == IntColorId));
if (string.IsNullOrEmpty(SizeId) == false)
    query = query.Where(Product => Product.ProductSizes.Any(a => a.SizeID == IntSizeId));
if (string.IsNullOrEmpty(From) == false)
    query = query.Where(Product => Product.ProductSizes.Any(a => a.Price >= DecimalFrom));
if (string.IsNullOrEmpty(To) == false)
    query = query.Where(Product => Product.ProductSizes.Any(a => a.Price <= DecimalTo));
var ProductsList = query
    .Select(ProductSize => ProductSize)
    .GroupBy(x => x.id).Select(Grouped => Grouped.FirstOrDefault())
    .ToList();

I based my query on Product, and used GroupBy to remove duplicate results instead of Distinct.

Advanced Search using Lambda Expression

3 Answers3