Linq To Sql Sub Query Complex

Question

tblItem

• Name
• ProductID

tblProduct

• Name
• ProductID
• CategoryID

tblCategory

• Name

• CategoryID

  var itms = from item in CMP.tblItems
      let t2s = (from  g in CMP.tblProducts
      where g.CategoryID==CatID
      select g.ProductID)
      where item.Name.Contains(Model) && item.ProductID.ToString() == t2s.ToString()
      select new { item.Name };
  

My problem is more than one product is return to t2s (Sub-Query). if i add FirstOrDefault() to Sub-Query then it will match it with only one product id! i need to match will all productid(s) it returns.

Answer 1

Use the navigation properties that LINQ-to-SQL creates for you. Item should have property Product. So you can simply do this:

var itms = from item in CMP.tblItems
    where item.Name.Contains(Model) && item.Product.CategoryID = CatId
    select new { item.Name };

Answer 2

Try This One:

var itms=from item in CMP.tblItems
         from g in CMP.tblProducts
         where item.Name.Contains(Model) && item.ProductID == g.ProductID && g.CategoryID == CatID
         select new {item.Name};