Linq Query Suggestion

I have query as below. A product can be assigned to multiple products so开发者_开发问答 after this linq query I am getting multiple products in result. How do I change it so that I can see one product only once .

var list = (from p in Products from cat in p.Product_SubCategory_Mappings
       let trend = CustomFunction(p.ID) where cat.SubCategory.CategoryId == 19 && 
       trend > 100 select new { Product = p, p.Vendor, trend}).ToList();

Here is table structure if it helps

1. Products --> ProductID | Name | Price | VendorID
2. Category --> CategoryID | Label
3. SubCategory--> SubCategoryID | Label | ParentID
4. Product_SubCategory_Mapping--> ProductID | SubCategoryID

When you do a sub-select on Product.Product_SubCategory_Mappings, you are multiplying the number of rows in your result set by the number of sub-category mappings you have for each product. This means products will be duplicated.

To avoid this, you can use the Any extension method instead of a sub-select.

Something like this:

var list = (from p in Products
            let trend = CustomFunction(p.ID)
            let cats = p.Product_SubCategory_Mappings
            where trend > 100
                && cats.Any(cat => cat.SubCategory.CategoryId == 19)
            select new { Product = p, p.Vendor, trend }
            )
            .ToList();

this may be work

 public IQueryable<Product> GetProduct()
        {

            var list = (from p in Products
                        from cat in p.Product_SubCategory_Mappings
                        let trend = CustomFunction(p.ID)
                        where cat.SubCategory.CategoryId == 19 &&
                              trend > 100
                        select new {Product = p, p.Vendor, trend}).Distinct();
            return list;
        }