none
Join clause in LINQ RRS feed

  • Question

  • Hi

    I have 2 tables in my database. one of them is Tbl_Products and the other one is Tbl_ProductCategories. The first one stores the information about products and the second one stores categories that each product in Tbl_Products belongs to. Tbl_Categories and Tbl_Products have a 1 to many relationship.

    here is the list of the fields of each table:

    Tbl_Products : 

    • ID
    • ProductTypeID
    • ProductName
    • ProductDescription
    • ProductAmount
    • ProductPrice

    Tbl_Categories:

    • ID
    • CategoryName

    I also have created the Entity Data Model file for my tables. Now in one of pages I need to show the list of my products with their categories retrieved from Tbl_Categories. I'm new to the LINQ and Entity Framework.I can do it with TSQL using Join clause but I want to write a LINQ query to approach this.

    A result I'm looking for is something like this : 

    ID           Category              Name              Amount              Price

    -----------------------------------------------------------------------------

    11135     Perfumes              Dior                    25                   60$

     

    Thanks in advance.


    Milad
    Monday, July 25, 2011 11:51 PM

Answers

  • A possible LINQ could be:

    var query =
      from p in context.ProductEntitySet.Include("Categories")
      select p;
    

    Where:

    • "context" is the ObjectContext you are working with.
    • "ProductEntitySet" is the ObjectSet<T> that represents the Table "Tbl_Product".
    • "Categories" is the Navigation Property of Product to ProductCategories.

     

    Include("Categories") 
    
    This indicates ObjectContext to load related Categories of every result Product.

     

    Regards,

    Fabian Fernandez

     

    Wednesday, July 27, 2011 4:55 AM
  • using (NorthwindEntities context = new NorthwindEntities())
    {
    	var query = from p in context.Tbl_Products
    				join q in context.Tbl_Categories
    				on p.ID equals q.ID
    				select new
    				{
    					ID = p.ProductTypeID,
    					Category = q.Categoryname,
    					Name = p.ProductName,
    					Amount = p.ProductAmount,
    					Price = p.ProductPrice
    				};
    	foreach (var item in query)
    	{
    		Console.WriteLine(string.Format("ID={0},Category={1},Name={2}, Amount={3},Price={4}"
    			, item.ID, item.Category, item.Name, item.Amount, item.Price));
    	}
    
    }
    


    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/
    Wednesday, July 27, 2011 6:50 AM

All replies

  • A possible LINQ could be:

    var query =
      from p in context.ProductEntitySet.Include("Categories")
      select p;
    

    Where:

    • "context" is the ObjectContext you are working with.
    • "ProductEntitySet" is the ObjectSet<T> that represents the Table "Tbl_Product".
    • "Categories" is the Navigation Property of Product to ProductCategories.

     

    Include("Categories") 
    
    This indicates ObjectContext to load related Categories of every result Product.

     

    Regards,

    Fabian Fernandez

     

    Wednesday, July 27, 2011 4:55 AM
  • using (NorthwindEntities context = new NorthwindEntities())
    {
    	var query = from p in context.Tbl_Products
    				join q in context.Tbl_Categories
    				on p.ID equals q.ID
    				select new
    				{
    					ID = p.ProductTypeID,
    					Category = q.Categoryname,
    					Name = p.ProductName,
    					Amount = p.ProductAmount,
    					Price = p.ProductPrice
    				};
    	foreach (var item in query)
    	{
    		Console.WriteLine(string.Format("ID={0},Category={1},Name={2}, Amount={3},Price={4}"
    			, item.ID, item.Category, item.Name, item.Amount, item.Price));
    	}
    
    }
    


    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/
    Wednesday, July 27, 2011 6:50 AM