LINQ 根据另一个列表之间的值过滤结果[英] LINQ Filter results based on values between another list

问题描述

I have a peculiar problem where I need to filter my search results based on price ranges. So I have 2 lists:

List A containing all items with their price.

List B containing all price ranges like price b/w 20 and 30 and price between 50-60.

Now I need to filter results in A where price lies between the price ranges specified in list B.

My linq queries are:

A - Product Data

var list = (from a in db.MItems
                        join b in db.DCategoryProducts on a.ProductCode equals b.ProductCode
                        join c in db.DItemTargetAreas on a.ProductCode equals c.ProductCode
                        join d in db.DItemQuantities on a.ProductCode equals d.ProductCode
                        where a.IsActive && b.ItemCategoryCode == itemCategoryID
                        && !string.IsNullOrEmpty(_targetarea) ? _targetarea.Contains(c.TargetArea.ToString()) : c.TargetArea == c.TargetArea
                        && !string.IsNullOrEmpty(_compression) ? _compression.Contains(a.CompressionRating.ToString()) : a.CompressionRating == a.CompressionRating
                        && !string.IsNullOrEmpty(_color) ? _color.Contains(d.ColourCode.ToString()) : d.ColourCode == d.ColourCode
                        && !string.IsNullOrEmpty(_style) ? _style.Contains(d.StyleCode.ToString()) : d.StyleCode == d.StyleCode
                        && !string.IsNullOrEmpty(_size) ? _size.Contains(d.SizeCode.ToString()) : d.SizeCode == d.SizeCode


                        && a.Price between my price_ranges


                        orderby a.ProductName
                        select new Models.ViewModels.Product
                        {
                            ItemCategoryCode = a.ItemCategoryCode,
                            ProductCode = a.ProductCode,
                            ProductName = a.ProductName,
                            SmallPic = a.SmallPic,
                            Price = a.Price,
                            PriceWas = a.PriceWas,
                            IsNew = a.IsNew
                        }).Distinct().ToList();

B - Price Ranges

var priceRanges = (from p in db.DPriceSlabs
                               where !string.IsNullOrEmpty(_priceSlab) ? _priceSlab.Contains(p.PRICE_ID.ToString()) : p.PRICE_ID == p.PRICE_ID
                               select new { p.PRICE_FROM, p.PRICE_TO }).ToList();

How can I get the desired results out of List A by applying filters from List B ? Any improvements to the current queries will also be much appreciated.

TIA

推荐答案

You want something like this ?

list.Where(item => priceRanges.Any(price => item.Price >= price.PRICE_FROM && item.Price <= p.PRICE_TO ));