how to write with LinQ to SQL?

Question

SELECT P.ProductId, P.ProductCategoryId, P.ParentProductCategoryId, 
       P.ProductName, PC.Name AS Category, P.Price, P.ProductYear
FROM   dbo.ProductCategory AS PC 
INNER JOIN
  (SELECT dbo.ProductCategory.ParentProductCategoryId, 
          dbo.ProductCategory.ProductCategoryId, 
          dbo.ProductCategory.Name AS CategoryName, 
          dbo.Product.ProductId, 
          dbo.Product.Price, 
          dbo.Product.Name AS ProductName, 
          dbo.Product.ProductYear
   FROM dbo.Product 
   INNER JOIN dbo.ProductCategory 
   ON dbo.ProductCategory.ProductCategoryId = dbo.Product.ProductCategoryId
  ) AS P 
ON PC.ProductCategoryId = P.ParentProductCategoryId

Answer 1

I'm not quite sure about your database layout but the LINQ statement would look something like this...

YourDataContext db = new YourDataContext();

var query =
    from p in db.Products
    join pc in db.ProductCategories on p.ProductCategoryId equals pc.ProductCategoryId 
    select new 
    {
        p.ProductId,
        p.ProductCategoryId,
        p.ParentProductCategoryId,
        p.ProductName,
        Category = pc.Name,
        p.Price,
        p.ProductYear 
    }

Answer 2

thanks ur answer.. but I don't get all for result. I retrieve the result using your answer, the result is below:

ProductId ProductName CategoryName price ProductYear

1 Comprehensive 2008 Horoscope... Tiger 19.99 2008 2 Comprehensive 2007 Horoscope... Tiger 0.00 2007 3 Test Lion 20.00 2008 4 Burmese Elephant Horoscope Elephant 20.00 2008 5 Chinese Snake Horoscope Snake 20.00 2008 6 Chinese Pig Horoscope Pig 20.00 2008 7 Burmese Mouse Horoscope Mouse 20.00 2008

Really,I want to get the result listed below:

ProductId ProductName CategoryName price ProductYear 1 Comprehensive 2008 Horoscope... Burmese Horoscope 19.99 2008 2 Comprehensive 2007 Horoscope... Burmese Horoscope 0.00 2007 3 Test Burmese Horoscope 20.00 2008 4 Burmese Elephant Horoscope Burmese Horoscope 20.00 2008 5 Chinese Snake Horoscope Chinese Horoscope 20.00 2008 6 Chinese Pig Horoscope Chinese Horoscope 20.00 2008 7 Burmese Mouse Horoscope Burmese Horoscope 20.00 2008

there, ProductCategory table has ParentProductCategoryId, I want to disply CategoryName in resuls should be Name of ProductCategory table where ParentProductCategoryId= ProductCategoryId. I used above your answer , CategoryName is not right that I want.. it shows Category Name but equal to childCategoryID .

please can you slove my problem , I don't know how to write joining same table ?